Insert/update PostGIS geometry column with PostgREST

Question

I use the combination of PostgreSQL 12.1, PostGIS 3.0.0 and PostgREST 7.0.1 and I am extremely happy with this setup. There is an open enhancement for a wider PostGIS support in PostgREST and I am looking forward to it.

I have a table with a geometry column:

CREATE TABLE places (
  id serial primary key,
  geometry geometry(GEOMETRY,4326)
);

With SQL I can insert the point for latitude 57.20 and longitude 17.45 like this:

insert into places(geometry) values (ST_GeomFromText('POINT(17.45 57.20)', 4326));

When querying it with PostgREST it is represented as GeoJSON:

GET /places?id=eq.6317&select=geometry

[{
  "geometry": {
    "type": "Point",
    "coordinates": [17.45,57.2]
  }
}]

But how do I need to represent the geometry so that I can insert/update using PostgREST?

Using GeoJSON results in an 500 Internal Server Error:

POST /places

{
    "geometry": {
        "type": "Point",
        "coordinates": [17.45,57.2]
    }
}

500 Internal Server Error

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

Same with using the ST_GeomFromText function:

POST /places

{
    "geometry": "ST_GeomFromText('POINT(17.45 57.20)', 4326)"
}

500 Internal Server Error

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

What does work is using the internal PostGIS string representation, but my API client only has the values 17.45 57.20 at hand:

POST /places

{
    "geometry": "0101000020E610000033333333337331409A99999999994C40"
}

201 Created

Is there any other text representation that would work or is there a general possibility to pass functions like ST_GeomFromText to PostgREST? Any hints are highly appreciated!

Answer 1

The straightforward way would be to use the geometry literal representation:

POST /places

{
  "geometry": "SRID=4326;POINT(17.45 57.2)"
}

(you can omit the SRID)

This works because this also works:

select 'SRID=4326;POINT(54.729142 25.268204)'::geometry
-- result: 0101000020E6100000ED116A86545D4B4009A87004A9443940  

Another way would be to have before insert trigger processing the json array.