PostgREST adds wrappers and quotations to GeoJSON output

[tvogt]

Environment

• Postgres/PostGIS: latest official docker image: postgis/postgis
• PostgREST: latest official docker image: postgrest/postgrest
• Operating system: MacOS 12.3.1

Description of issue

Trying to build a GeoJSON endpoint, I noticed that queries that work fine via psql have additional wrapper entities wrapped around them when getting them via PostgREST, which breaks the GeoJSON parser in Mapbox:

Table and view:

CREATE TABLE IF NOT EXISTS mapevents (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    geom public.geometry,
);
CREATE VIEW mapevents_geojson AS
    SELECT json_build_object('type', 'FeatureCollection', 'features', json_agg(ST_AsGeoJSON(x))) from mapevents x;

The above view approach is taken from #1894

output from psql:

# select * from mapevents_geojson;
                                                                                                   
     json_build_object                                                                             
                           
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---------------------------
 {"type" : "FeatureCollection", "features" : ["{\"type\": \"Feature\", \"geometry\": {\"type\":\"Po
int\",\"coordinates\":[13.359615,52.046112]}, \"properties\": {\"id\": 1, \"created_at\": \"2022-06
-17T15:15:12.524347\"}}"]}
(1 row)

but via curl I get:

$ curl "http://localhost:3000/mapevents_geojson"
[{"json_build_object":{"type" : "FeatureCollection", "features" : ["{\"type\": \"Feature\", \"geometry\": {\"type\":\"Point\",\"coordinates\":[13.359615,52.046112]}, \"properties\": {\"id\": 1, \"created_at\": \"2022-06-17T15:15:12.524347\"}}"]}}]

Note the "json_build_object" wrapper.

[tvogt]

Defining the view like this appears to work:

CREATE VIEW mapevents_geojson AS SELECT 'FeatureCollection' AS type, array_to_json(array_agg(ST_AsGeoJSON(x)::json)) as features from mapevents x;

[tvogt]

It does seem that for proper GeoJSON endpoints, defining one view per desired endpoint is the approach to take. I'm ok with that and once you've got it puzzled out it's not even complicated. It might be useful to add it to the docs as a working example - when you Google, you'll find a dozen different examples, most of which don't yield the desired result because they're for older versions of Postgres, PostGIS or PostgREST.

[steve-chavez]

It might be useful to add it to the docs as a working example

We have this on latest: https://postgrest.org/en/latest/how-tos/working-with-postgresql-data-types.html#postgis

[steve-chavez]

@tvogt Regarding your issue on #2339. I think the following could work.

Try using a FUNCTION instead of a VIEW, that RETURNS mapevents - without SETOF - that should give you a json object IIRC.

Actually, just try with RETURNS json.

[tvogt]

@steve-chavez Using a function works. I've copied and adapted the example and it produces proper GeoJSON. It's not a perfect solution, though, because I lose all the magic of filters, embeds etc. that make PostgREST so amazing.

[steve-chavez]

Btw, we just merged the geojson feature, you can try that by downloading the binaries(at the bottom) here https://github.com/PostgREST/postgrest/actions/runs/2546488478.

For your use case I guess the problem is still having to specify the application/geo+json header.

[laurenceisla]

Try using a FUNCTION instead of a VIEW

As Steve suggested, I created a PR in the docs that modifies the example from a VIEW to a FUNCTION in order to return a JSON object and not an Array. Here is the example if you need it: https://postgrest.org/en/latest/how-tos/working-with-postgresql-data-types.html#postgis