如何从geojson对象进行查询

Question

在下面发布的查询中，我想查询以下信息

ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(feature->>'geometry'),4326),25832) AS LIDARDataPolygonsAsGeometry

featuresCollection是一个geojson对象。

如何从geojson对象进行查询？

query="""   
WITH data AS (
SELECT '{featuresCollection}'::json AS featuresCollection
)
SELECT 
LIDARDataPolygonsAsGeometry,
FROM (
SELECT 
ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(feature->>'geometry'),4326),25832) AS LIDARDataPolygonsAsGeometry

FROM (SELECT json_array_elements(featuresCollection->'features') AS feature 
FROM data) AS f) j
GROUP BY LIDARDataPolygonsAsGeometry
""".format(table=config['PostgreDB']['table_name_test'], width=config['Grid']['cell_width'], height=config['Grid']['cell_height'],bufferRadius=config['Grid']['buffer_radius'],featuresCollection=featuresCollection)

Answer 1

只需在子查询中解压缩特征集合，提取几何图形并应用所需的转换，例如从名为t的表中提取包含GeoJSON字符串的列geojson：

SELECT 
ST_Transform(
ST_SetSRID(
ST_GeomFromGeoJSON(feature->>'geometry'),4326),25832)  
FROM (SELECT json_array_elements(geojson->'features') AS feature FROM t) data;