gpt4 book ai didi

sql - 在 PL/pgSQL 函数中添加 ST_Transform

转载 作者:行者123 更新时间:2023-12-04 15:02:16 26 4
gpt4 key购买 nike

我使用了 Paul Ramsey´s blog 中的函数从 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) 来返回传单应用程序的经纬度数据:

  • 我试过调整线路
json_geom := ST_AsGeoJSON(((rowjsonb ->> ST_Transform(geom_column, 4326))::geometry)::jsonb;

这不起作用,因为 ST_Transform 需要在几何图形上执行,而不是文本或 json;

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

这也不起作用。

我还尝试了以下方法:

json_geom := ST_AsGeoJSON(rowjsonb ->> ST_Transform((geom_column->>'geom')::geometry, 4326))::jsonb; 返回错误:operator does not存在:文本 ->> 未知

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 函数集成到 block 段中吗?或者我需要从逻辑上重写 block 吗?

最佳答案

欢迎来到 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

关于sql - 在 PL/pgSQL 函数中添加 ST_Transform,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/66777343/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com