postgis 返回 geojson

article/2025/9/29 0:07:49

GeoJSON 特征集合：

{"type": "FeatureCollection","features": [{"type": "Feature","geometry": {"type": "Point","coordinates": [102.0, 0.5]},"properties": {"prop0": "value0"}},{"type": "Feature","geometry": {"type": "LineString","coordinates": [[102.0, 0.0],[103.0, 1.0],[104.0, 0.0],[105.0, 1.0]]},"properties": {"prop0": "value0","prop1": 0.0}},{"type": "Feature","geometry": {"type": "Polygon","coordinates": [[[100.0, 0.0],[101.0, 0.0],[101.0, 1.0],[100.0, 1.0],[100.0, 0.0]]]},"properties": {"prop0": "value0","prop1": {"this": "that"}}}]
}

ST_AsGeoJSON 函数:

SELECTST_AsGeoJSON(ST_Transform(geom,4326))
FROMtb_base_hazard

在这里插入图片描述

可以看出 ST_AsGeoJSON 返回一个 Geometry

此为 postgis 3.x 以下方法

WITH feature AS (SELECT'Feature' AS "type",st_asgeojson ( geom ) :: json AS "geometry",(SELECTjson_strip_nulls ( row_to_json ( fields ) )FROM(SELECTid, hazard_name) as fields) AS "properties" FROMtb_base_hazard AS h WHERE1=1),features AS ( SELECT 'FeatureCollection' AS "type", array_to_json ( ARRAY_AGG ( feature.* ) ) AS "features" FROM feature ) SELECT row_to_json ( features.* ) FROM features;

此为 postgis 3.x 方法更快更简洁

官方文档
在这里插入图片描述
可见直接返回单个 feature

WITH features AS (SELECT( SELECT st_asgeojson ( fields.* ) :: json FROM ( SELECT c.ID,c.the_geom ) AS fields ) AS feature FROMcities AS c WHERE1 = 1 ) 
SELECTjson_build_object ( 'type', 'FeatureCollection', 'features', json_agg ( features.feature ) ) 
FROMfeatures;