Supabase-js insert geometry objects

user_4435313 0
Viewed 176

I'm trying to insert geometry objects into my supabase postgres table.

I have the following geometry columns:

Column name Data type
point POINT
line_string LINESTRING
polygon POLYGON

I have tried multiple ways to insert POINT objects (I haven't gotten to the linestrings or polygons, seeing that they are more complicated).


const geometry = { x: -123, y: -123 }

const point = `(${geometry.x}, ${geometry.y})`; // Try number 1
const point = `point(${geometry.x},${geometry.y})`; // Try number 2
const point = `ST_SetSRID(ST_MakePoint(${geometry.y}, ${geometry.x}), 4326)`; // Try number 3

const { data, error } = await supabase.from('geometries').upsert({ point });

All three gave the same error:

{
  message: 'parse error - invalid geometry',
  code: 'XX000',
  details: null,
  hint: '"(0" <-- parse error at position 2 within geometry'
}

{
  message: 'parse error - invalid geometry',
  code: 'XX000',
  details: null,
  hint: '"point(0, 0" <-- parse error at position 10 within geometry'
}

{
  message: 'parse error - invalid geometry',
  code: 'XX000',
  details: null,
  hint: '"ST" <-- parse error at position 2 within geometry'
}

How do I insert/upsert point objects? Linestrings? Polygons?

supabase-database supabase gis
2 Answers

For point, you have to remove the comma between the coordinates values.

POINT(${geometry.x} ${geometry.y})

Let me know how it goes with this!

user_54589130

As written here:

Well-Known Binary (WKB) provides a portable, full-precision representation of spatial data as binary data (arrays of bytes)

Concerning precision we can read here:

WKB format is useful to read geometry data from the database and maintaining full numeric precision. This avoids the precision rounding that can happen with text formats such as WKT.

Geometries can be transformed in WKB with OpenLayers. This way we don't need to use PostGIS functions in our query. Based on the documentation I managed to update/insert geometries as WKB.

import WKB from 'ol/format/WKB';
import Feature from 'ol/Feature';
import Point from 'ol/geom/Point';

const pointCoordinates = [...,...];

const feature = new Feature({
    geometry: new Point(pointCoordinates),
});

const format = new WKB();

const geomWKB = format.writeFeature(feature);
user_20006540
Related