PostGIS doesn't automatically convert WKB to GeoJSON

Question

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.

Answer 1

Yes, in 3.0 we added a cast from geometry to json, and that cast uses geojson as the output json encoding. If you want to still get hex you'll have to wrap up your geometry into a bytea in whatever query you are using to generate your row.

WITH r AS (
   SELECT ST_AsBinary(geom) AS geom, name, address
   FROM mytable
)
SELECT row_to_json(r.*) FROM r

Answer 2

Given Paul's answer, I created a function that replaces all geometry columns with their WKB values in a json object. It did take a couple steps, though:

First I made a function that gets all geometry columns for a given table as column_name, geometry_type records. it looks like so:

CREATE OR REPLACE FUNCTION get_geometry_columns(schema_name text, table_name text)
RETURNS TABLE (column_name text, geometry_type text) AS $$
begin
  RETURN QUERY
  SELECT f_geometry_column::text as column_name, type::text as geometry_type
  FROM public.geometry_columns
  WHERE f_table_schema = schema_name
  AND f_table_name = table_name;
end
$$
language plpgsql;

Then I created a function that took a json value and converted each geometry column into text, replacing the original GeoJSON value with the WKB representation. It allows the user to pass in an array of strings to know which columns to convert, but it has a default of just [geometry]:

CREATE OR REPLACE FUNCTION cast_geojson_to_text(json_data jsonb, geometry_columns TEXT[] DEFAULT ARRAY['geometry']) RETURNS jsonb as $$
DECLARE 
    geometry_column text;
    geometry_json jsonb := '{}'::jsonb;
begin
    FOREACH geometry_column IN ARRAY geometry_columns
    LOOP
        IF json_data->>geometry_column IS NOT NULL THEN
            geometry_json = geometry_json || jsonb_build_object(
                geometry_column, 
                CAST(ST_GeomFromGeoJSON(json_data->>geometry_column) AS TEXT)
            );
        END IF;
    END LOOP;
    return json_data || geometry_json;
end
$$
language plpgsql;

In the end, you can use it by passing the json that results from row_to_json()::jsonb to the function:

WITH json_data AS (
  SELECT row_to_json(tmp)::jsonb AS json FROM (
    SELECT '0101000020E61000006666666666665F403333333333332440'::geometry
  ) tmp
)
SELECT cast_geojson_to_text(
    json, 
    (SELECT ARRAY_AGG(column_name) FROM get_geometry_columns('schema', 'table'))
)
FROM json_data;

With a table that has a geometry geometry column, the above query would return the result:

                       cast_geojson_to_text                        
--------------------------------------------------------------------
 {"geometry": "0101000020E61000006666666666665F403333333333332440"}
(1 row)

Since I was intending to use it with the community-created postgres auditing trigger, I put the code in a fork available here.

Edit: There was some funky business with SRIDs, the linked file above has been updated to use the ST_SetSRID column to correct those issues.