I discovered that somewhere in the upgrades from PostgreSQL 9.5 and PostGIS 2.2 to PostgreSQL 12.1 and PostGIS 3.0 the behavior for transforming a row into JSON changed.
I am using the following example command:
SELECT row_to_json(tmp) FROM (SELECT '0101000020E61000006666666666665F403333333333332440'::geometry) tmp;
In 9.5/2.2 it produces the output:
row_to_json
-------------------------------------------------------------------
{"geometry":"0101000020E61000006666666666665F403333333333332440"}
(1 row)
But 12.1/3.0 produces the output:
row_to_json
----------------------------------------------------------
{"geometry":{"type":"Point","coordinates":[125.6,10.1]}}
(1 row)
Is there a setting to retain the functionality of 9.5/2.2 and not automatically convert my WKB to GeoJSON?
Edit:
The following command produces the error cannot cast type geometry to json
in 9.5/2.2 and the expected GeoJSON value in 12.1/3.0:
SELECT '0101000020E61000006666666666665F403333333333332440'::geometry::json;
I am guessing it is related to how PostGIS now knows how to convert between the types, I wonder if there's a way to force it to interpret everything as text.
{ "text": "0101000020E61000006666666666665F403333333333332440" }
and after all, isn't it text that you want and not geometry? – user30184 Nov 28 '19 at 7:35to_json
function have the same behavior ? – obchardon Nov 28 '19 at 15:15