如何从geojson对象查询 [英] how to query from geojson object

问题描述

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

in the below posted query i want to query the following information

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

featuresCollection 是一个 geojson 对象.

The featuresCollection is a geojson object.

请告诉我如何从 geojson 对象中查询

Please let me know how to query from a geojson object

    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)

推荐答案

只需在子查询中解压您的特征集合，提取几何图形并应用您想要的转换，例如来自名为 t 的表，其中包含一个包含 GeoJSON 字符串的列 geojson:

Just unpack your feature collection in a subquery, extract the geometries and apply the transformation you want, e.g. from a table called t with a column geojson containing GeoJSON strings:

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

这篇关于如何从geojson对象查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！