Ajout de ST_Transform dans une fonction PL/pgSQL

Question

J'utilise une fonction de Le blog de Paul Ramsey pour interroger les données geoJSON d'une base de données postGIS. J'ai ajusté un peu la fonction, qui a fonctionné jusqu'à présent :

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;

J'en suis maintenant au point où je veux intégrer une ST_Transform(geom_column, 4326) pour me rendre les données lat/lng pour une application de brochure :

• J'ai essayé d'ajuster la ligne

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

ce qui ne fonctionne pas, car ST_Transform doit être effectué sur une géométrie et non sur un texte ou un json ;

• Mon autre idée, déclarer une nouvelle variable geom_c et effectuer la transformation comme premièrement dans le bloc

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

qui ne fonctionne pas non plus.

J'ai également essayé ce qui suit :

json_geom := ST_AsGeoJSON(rowjsonb ->> ST_Transform((geom_column->>'geom')::geometry, 4326))::jsonb; ce qui renvoie l'erreur suivante : l'opérateur n'existe pas : texte ->> inconnu

json_geom := ST_AsGeoJSON(rowjsonb ->> ST_Transform(ST_GeomFromGeoJSON(geom_column), 4326))::jsonb; qui donne l'erreur caractère inattendu (à l'offset 0)

Voici deux exemples de points de la table Standorts, que j'interroge :

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

La requête que j'utilise est la suivante :

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";

Puis-je même intégrer la fonction ST_Transform dans le segment de bloc ? Ou dois-je réécrire le bloc de manière logique ?

Answer 1

Bienvenue sur SO. Le paramètre doit être une géométrie, donc vous devez couler la chaîne de caractères dans le paramètre lui-même, et non dans le résultat de la fonction, par ex.

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

Exemple :

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

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

Cela dit, votre fonction pourrait être modifiée de la sorte :

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;

Testez avec votre échantillon de données

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 à l'utilisation de ST_AsGeoJSON : ST_Transforms s'attend à une géométrie et ST_AsGeoJSON renvoie un texte contenant une représentation de la géométrie, et non la géométrie elle-même. Vous devez donc d'abord transformer la géométrie, puis la sérialiser sous forme de GeoJSON.

Démonstration : db<>fiddle