Adding ST_Transform in a PL/pgSQL function

Question

I use a function from Paul Ramsey´s blog to query geoJSON data from a postGIS database. I adjusted the function a little, which worked so far:

CREATE OR REPLACE FUNCTION rowjsonb_to_geojson(
  rowjsonb JSONB, 
  geom_column TEXT DEFAULT 'geom')
RETURNS json AS 
$$
DECLARE 
 json_props jsonb;
 json_geom jsonb;
 json_type jsonb;
BEGIN
 IF NOT rowjsonb ? geom_column THEN
   RAISE EXCEPTION 'geometry column ''%'' is missing', geom_column;
 END IF;
 json_geom := ST_AsGeoJSON((rowjsonb ->> geom_column)::geometry)::jsonb;
 json_geom := jsonb_build_object('geometry', json_geom);
 json_props := jsonb_build_object('properties', rowjsonb - geom_column);
 json_type := jsonb_build_object('type', 'Feature');
 return (json_type || json_geom || json_props)::text;
END; 
$$ 
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

Now I´m on the point, where I want to integrate a ST_Transform(geom_column, 4326) to give me back lat/lng data for a leaflet application:

• I tried adjusting the line

json_geom := ST_AsGeoJSON(((rowjsonb ->> ST_Transform(geom_column, 4326))::geometry)::jsonb;

which doesn´t work, because ST_Transform needs to be performed on a geometry and not a text, or json;

• My other idea, to declare a new variable geom_c and perform the transformation as first in the block

geom_c := ST_Transform(geom_column, 4326)::geometry;

which also doesn´t work either.

I also tried the following:

json_geom := ST_AsGeoJSON(rowjsonb ->> ST_Transform((geom_column->>'geom')::geometry, 4326))::jsonb; which gives back the error: operator does not exist: text ->> unknown

json_geom := ST_AsGeoJSON(rowjsonb ->> ST_Transform(ST_GeomFromGeoJSON(geom_column), 4326))::jsonb; which gives the error unexpected character (at offset 0)

Here are a two sample points from the standorts table, that I´m querying:

"id": "0", "geom": "0101000020787F0000000000001DDF2541000000800B285441"
"id": "1", "geom": "0101000020787F000000000000EFE42541000000A074275441"
     

The query I use is:

SELECT 'FeatureCollection' AS type, 
   'standorts' AS name, 
   json_build_object('type', 'name', 'properties', 
   json_build_object('name', 'urn:ogc:def:crs:OGC:1.3:CRS84')) AS CRS,
   array_to_json(array_agg(rowjsonb_to_geojson(to_jsonb(standort.*)))) AS FEATURES FROM standort";

Can I even integrate the ST_Transform function into the block segment? Or do I need to rewrite the block logically?

Answer 1

Welcome to SO. The parameter must be a geometry, so you need to cast the string in the parameter itself, not the result of function, e.g.

json_geom := ST_AsGeoJSON(((rowjsonb ->> ST_Transform(geom_column::geometry, 4326)))::jsonb;

Example:

SELECT 
  ST_AsGeoJSON(
    ST_Transform('SRID=32636;POINT(1 2)'::GEOMETRY,4326));

                       st_asgeojson                        
-----------------------------------------------------------
 {"type":"Point","coordinates":[28.511265075,0.000018039]}

That being said, your function could be modified like that:

CREATE OR REPLACE FUNCTION rowjsonb_to_geojson(
  rowjsonb JSONB, 
  geom_column TEXT DEFAULT 'geom')
RETURNS json AS 
$$
DECLARE 
 json_props jsonb;
 json_geom jsonb;
 json_type jsonb;
BEGIN
 IF NOT rowjsonb ? geom_column THEN
   RAISE EXCEPTION 'geometry column ''%'' is missing', geom_column;
 END IF;
 json_geom := ST_AsGeoJSON(ST_Transform((rowjsonb ->> geom_column)::geometry,4326))::jsonb;
 json_geom := jsonb_build_object('geometry', json_geom);
 json_props := jsonb_build_object('properties', rowjsonb - geom_column);
 json_type := jsonb_build_object('type', 'Feature');
 return (json_type || json_geom || json_props)::text;
END; 
$$ 
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

Test with your sample data

WITH standort (id,geom) AS (
  VALUES
    (0,'0101000020787F0000000000001DDF2541000000800B285441'),
    (1,'0101000020787F000000000000EFE42541000000A074275441')
) 
SELECT row_to_json(q) AS my_collection FROM (
SELECT 'FeatureCollection' AS type, 
   'standorts' AS name, 
   json_build_object('type', 'name', 'properties', 
   json_build_object('name', 'urn:ogc:def:crs:OGC:1.3:CRS84')) AS CRS,
   array_to_json(array_agg(rowjsonb_to_geojson(to_jsonb(standort.*)))) AS features 
FROM standort) q;

                      my_collection
-----------------------------------------------

{
  "type": "FeatureCollection",
  "name": "standorts",
  "crs": {
    "type": "name",
    "properties": {
      "name": "urn:ogc:def:crs:OGC:1.3:CRS84"
    }
  },
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [
          11.886684554,
          47.672030583
        ]
      },
      "properties": {
        "id": 0
      }
    },
    {
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [
          11.896296029,
          47.666357408
        ]
      },
      "properties": {
        "id": 1
      }
    }
  ]
}

Note to the usage of ST_AsGeoJSON: ST_Transforms expects a geometry and ST_AsGeoJSON returns a text containing a representation of the geometry, not the geometry itself. So you first need to transform the geometry and then you can serialise it as GeoJSON.

Demo: db<>fiddle