Geometry output different in Postgres + Postgis: 11.7 + 2.5

[hoetmaaiers]

Environment

• PostgreSQL version: 11.7
• PostGIS version: 2.5
• PostgREST version: 7.0.1
• Operating system: Both Debian or Docker container (kartoza/postgis)

Description of issue

I am using the PostGIS extension to enable 'geometry' as data type. Via PostgREST I can query the attribute and receive the value as GeoJSON:

In PostgrSQL version 12.2 + PostGIS 3.0 I receive the geometry as proper GeoJSON:

{ 
  geom: {"type":"Polygon","coordinates":[[[3.62957693852653,50.9862520206627],[3.63153652950536,50.9845275090493],[3.63236366117811,50.9850384930322],[3.63244672784498,50.9849554363762],[3.63275368352831,50.9847385131512],[3.6329944508651,50.9845399997232], ...]]}
}

In PostgrSQL version 11.7 + PostGIS 2.5 when I query the same record, I receive the geometry as (what I think it resembles) a WKB (Well Known Binary) representation of the geometry:

{
  geom: "01030000000100000072020000A8695BAF938914407B3D444B62A84940F93C713398891440F10FA0325EA84940F5C9B2E5998914402AD067715DA84940AC5F582C9A891440617AF5515DA8494062031F569B891440BC0D73CD5CA8494065091264A0891440E794BF8D5AA849405A3CFABFD08914400C6E6C0945A84940C5EBA7F1078A1440A644FE3A2CA84940C1CCC43E0D8A14404FC009D929A84940D1B3752A3B8A1440A7F66D3515A84940906E04573B8A1440235D7B1F15A849405299373B438A1440B98B4D3D11A849407BACB8944B8A1440B5BE5A210DA849409BA477AD4B8A14407A3F4A140DA84940C5DA46338E8A144062DF3FFEE9A749409B1848568E8A1440D719C9EBE9A7494092BC15AFC18A144062DC88D6CEA74940CBE887B0C38A14405417B3C7CDA74940170167A2138B1440DC596D9CA3A7494029C90D3A198B1440C2E342A9A0A74940C21FC2161B8B144025CDCBAD9FA749405D8A59441D8B14405617A4879EA74940192AAC21478B1440D3C03A7288A749408E3FED924D8B144035853B1F85A749409CD73C10538B1440D448D9D484A7494081FD0953838B1440C8484AD868A7494036664D64088C1440D5187D4137A74940E1EE20CBFB8B1440497A22E736A749404A2A634CE98B14403FAE7B6236A74940EF8152F0F28B14404EAA248331A7494041E16BDD0D8C1440ECC8A15425A749406B6D84C9278C1440858E4D9A19A74940617F66133E8C1440C78DBC840FA74940050170E03E8C14406312FC270FA74940DBFA80F53E8C14402BAF731E0FA7494033439B64568C14406AF7398404A7494027FA621B708C14403EA5DEE1F8A64940211FBDC1868C1440735868A2EEA649401DB9590AA38C1440B68957D6E1A6494029DAB774B48C144055FD15F5D9A64940E933BF83C68C144074C951C9D1A64940D430DEFCC68C1440B9908092D1A64940BF77502EC78C144039B0BA6ED1A649404866082ED28C1440047F747AC9A649404FC53E68DE8C144005FAEDCDC2A64940FBCF5ECD008D1440A7FCBA07B0A649406D5C0CD8008D14409EBAE201B0A64940EF34D2D7208D1440F561668A9EA64940D114515B298D14403FB18FFB97A649400F29134E2A8D144035077F4097A6494019825724308D14405BCAA7"
}

The easiest way to solve this, would be simply upgrade my PostgreSQL and PostGIS versions But since these service are installed on Debian I am tied to the Debian Buster version.

[steve-chavez]

In PostgrSQL version 12.2 + PostGIS 3.0 I receive the geometry as proper GeoJSON:

Yes, that's true. In PostGIS 3.0 there's a cast for geometry to json which is done automatically by json_agg(which we use in our wrapper query).
We have an implementation of a geojson in #1564 that relies on that.

In theory we could provide geojson for < 3.0, but not sure it would be worth the effort.
I see there's https://packages.debian.org/unstable/postgresql-12-postgis-3, perhaps it's possible to upgrade to postgis 3 on debian?

[hoetmaaiers]

Thanks for sharing this insight and thinking along.

Using the unstable release is not in favour of the sysadmin's strategy for our internal services.

Another solution might be to visualise the WKB variant and never need the GeoJSON version? But with this possible solution I want to be able in future projects to also configure the geometry output to be in WKB. Is it configurable somewhere?

[wolfgangwalther]

I have never worked with PostGIS, so I might be completely off here - but maybe you can just try to recreate the cast for geometry to json from postgis 3.0 that Steve mentioned, in your pg11/postgis2.5 schema manually? Maybe this will already yield the same result as in your pg12/postgis3.0 setup.

[hoetmaaiers]

Would this be easy to do @steve-chavez ?

[wolfgangwalther]

The casts are defined here: https://github.com/postgis/postgis/blob/f50dcaaaa7a0c77e84076d0543c3c48b3a4d9ad4/postgis/postgis.sql.in#L4731-L4748

Not sure whether you can compile the c function independently for this. Or whether you might be able to recreate it in plpgsql or something.

Probably easier to convince your sysadmin to use the unstable release for now ;)

[hoetmaaiers]

Ok thank you for the insights. I'll close this for now...

[wolfgangwalther]

Actually, looking at the code you could try something roughly like this:

CREATE OR REPLACE FUNCTION geometry_to_json(geometry)
RETURNS json
LANGUAGE SQL AS $$
SELECT ST_AsGeoJson($1)::json
$$ IMMUTABLE STRICT PARALLEL SAFE;

CREATE CAST (geometry AS json) WITH FUNCTION geometry_to_json(geometry);

[hoetmaaiers]

Thank you @wolfgangwalther, this exact snippet solved my problem!
🙏