在 PL/pgSQL 函数中添加 ST

一尘不染

在 PL/pgSQL 函数中添加 ST_Transform

sql

我使用Paul Ramsey 博客中的函数从postGIS 数据库中查询 geoJSON 数据。我稍微调整了这个功能，到目前为止有效：

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;

现在，我要集成一个 ST_Transform(geom_column, 4326) 来返回传单应用程序的 lat/lng 数据：

我试着调整线
json_geom := ST_AsGeoJSON(((rowjsonb ->> ST_Transform(geom_column, 4326))::geometry)::jsonb;
这是行不通的，因为 ST_Transform需要在几何图形而不是文本或 json 上执行；

我的另一个想法，声明一个新变量 geom_c 并在块中首先执行转换
geom_c := ST_Transform(geom_column, 4326)::geometry;

这也不起作用。

我还尝试了以下方法：

json_geom := ST_AsGeoJSON(rowjsonb ->> ST_Transform((geom_column->>'geom')::geometry, 4326))::jsonb; 返回错误：运算符不存在：文本 ->> 未知

json_geom := ST_AsGeoJSON(rowjsonb ->> ST_Transform(ST_GeomFromGeoJSON(geom_column), 4326))::jsonb; 这给出了错误意外的字符（在偏移量 0 处）

这是我正在查询的标准表中的两个样本点：

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

我使用的查询是：

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

我什至可以将 ST_Transform 函数集成到块段中吗？还是我需要在逻辑上重写块？

阅读 318

2021-05-30

共1个答案

一尘不染

欢迎来到 SO。参数必须是几何体，所以你需要在参数本身中转换字符串，而不是函数的结果，例如

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

例子：

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

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

话虽如此，您的函数可以这样修改：

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;

使用您的样本数据进行测试

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
      }
    }
  ]
}

使用 ST_AsGeoJSON 的注意事项：ST_Transforms需要几何图形并ST_AsGeoJSON返回包含几何图形表示的文本，而不是几何图形本身。所以你首先需要转换几何，然后你可以将它序列化为 GeoJSON。

演示： db<>fiddle

2021-05-30