Can't open postgrest service in QGIS

[sigeal]

Environment

• PostgreSQL version: 10.19
• PostgREST version: 9.0.0
• Operating system: Ubuntu 16.04

Description of issue

I have a PostgreSql stored procedure which returns a geojson feature collection created with json_build_object function. This web service opens fine in OpenLayers, however, when I try to open it in QGIS, with data source manager -> vector -> protocol -> geojson :

I get the following error :

Invalid data source: [myurl]/rpc/wod_geojson is not a valid or recognized data source.

This is the query wrapped in a plpgsql function called my_geojson :

SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(
        json_build_object(
            'type', 'Feature',
            'id', id,
            'geometry', ST_AsGeoJSON(ST_Transform(geometry, 4326))::json,
            'properties', json_build_object(
                'lib', lib
            )
        )
    )
)::json my_geojson FROM (SELECT ROW_NUMBER() OVER() id, * FROM mytable) tmp

This is the url I use to call the service:
http://mydomain:3000/rpc/my_geojson

Tests realised with curl and ogrinfo for failing postgrest service and working pg_featureserv services :

PostGrest

curl answer :

StatusCode        : 200
StatusDescription : OK
Content           : {123, 34, 116, 121...}
RawContent        : HTTP/1.1 200 OK
                    Transfer-Encoding: chunked
                    Content-Range: 0-0/*
                    Vary: Accept-Encoding
                    Content-Type: application/geo+json
                    Date: Sun, 13 Feb 2022 07:35:55 GMT
                    Server: postgrest/9.0.0
                    
                    {"type" : ...
Headers           : {[Transfer-Encoding, chunked], [Content-Range, 0-0/*], [Vary, Accept-Encoding], [Content-Type, 
                    application/geo+json]...}
RawContentLength  : 430979

ogrinfo answer :

ERROR 1: HTTP error code : 404
ERROR 1: HTTP error code : 404
FAILURE:
Unable to open datasource `http://mydomain:3000/rpc/wod_geojson.json' with the following drivers...

pg_featureserv

curl answer :

StatusCode        : 200
StatusDescription : OK
Content           : {123, 34, 116, 121...}
RawContent        : HTTP/1.1 200 OK
                    Transfer-Encoding: chunked
                    Content-Type: application/geo+json
                    Date: Sun, 13 Feb 2022 07:33:51 GMT
                    
                    {"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Po...
Headers           : {[Transfer-Encoding, chunked], [Content-Type, application/geo+json], [Date, Sun, 13 Feb 2022 
                    07:33:51 GMT]}
RawContentLength  : 4474

ogrinfo answer :

INFO: Open of `http://mydomain:9000/functions/webmapod_mdarret/items.json'
      using driver `GeoJSON' successful.
1: items (Point)

I tried different headers combinations with no success...

[steve-chavez]

in QGIS, with data source manager -> vector -> protocol -> geojson, I get the following error :
Invalid data source: [myurl]/rpc/wod_geojson is not a valid or recognized data source.

Don't know much about QGIS, but are you sure you can use "protocol -> geojson" for an http response like that? Isn't it expecting a geojson file instead?

This blog post says it should be "Data Source Manager button >> Vector Data >> Protocol HTTP(S), cloud, etc. >> Select GeoJSON type >> URI path >> Add".

[sigeal]

Here is what I got from gdal-ogr mailing list :

I wonder if there could be something in the Accept headers that orginfo is using. Ogrinfo seems to set headers “Accept: text/plain, application/json” but GeoJSON is now officially “application/geo+json”. I do not know the hierarchy of the MIME types and if “json” should include also the subtype “geo+json”.
I made a test with ogrinfo and --config GDAL_HTTP_HEADER_FILE my_headers.txt. After this I could see from the logs this

Accept: application/geo+json
Accept: text/plain, application/json

By https://datatracker.ietf.org/doc/html/rfc2616/ I suppose this is correct and http server should concatenate Accept headers. However, the test was not successful so maybe there is something else going wrong.
-Jukka Rahkonen-

[sigeal]

And I finally discovered that I can load my geojson webservice in QGIS using Python :

myLayer= QgsVectorLayer('http://mydomain:3000/rpc/wod_geojson, 'myLayer', 'ogr')
QgsProject.instance().addMapLayers([myLayer])

[steve-chavez]

Glad you made it work! Do you have a link to the gdal-org mailing thread? Would still like to know what's missing for us to work with ogrinfo.

[sigeal]

@Steve : Thank you for helping, and for this great software, I really enjoy using it and look forward to seeing PostGis functionnalities integration.
Here is the link to gdal thread :
https://lists.osgeo.org/pipermail/gdal-dev/2022-February/055425.html

[steve-chavez]

So as mentioned in https://lists.osgeo.org/pipermail/gdal-dev/2022-February/055429.html, ogrinfo sends an Accept: text/plain, application/json - weird that it prints ERROR 1: HTTP error code : 404, because I've tested it and postgrest actually rejects that with a 406 Not Acceptable(postgrest assumes text/plain is preferred). Maybe we can assume it's printing the wrong status somehow.

That being said, there might be something wrong with our content negotiation.. I think we should prefer application/json and succeed in that case.. will open an issue for this.