欢迎来到 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
602392714
清零编程群