从postgis中获取geojson数据

要从PostGIS中获取GeoJSON数据，可以使用以下SQL查询语句：

SELECT 'FeatureCollection' AS type, array_to_json(array_agg(f)) AS features
FROM (
SELECT 'feature' AS type, ST_AsGeoJSON(geom)::json AS geometry,
(SELECT row_to_json(t) FROM (
SELECT cb.city_code, cb.city_name, cd.data_new, ce.color
FROM citys_boundary cb
LEFT JOIN citys_data cd ON cb.city_code = cd.city_code
LEFT JOIN color_enum ce ON cd.data_level = ce.level AND ce.data_code = cd.data_type
WHERE cb.level = 'district' AND cb.city_code LIKE '51%' AND cd.data_type = '100000'
) AS t) AS properties
FROM citys_boundary cb
LEFT JOIN citys_data cd ON cb.city_code = cd.city_code
LEFT JOIN color_enum ce ON cd.data_level = ce.level AND ce.data_code = cd.data_type
WHERE cb.level = 'district' AND cb.city_code LIKE '51%' AND cd.data_type = '100000'
) AS f;

这个查询语句将返回一个包含GeoJSON数据的FeatureCollection对象，其中包含了城市边界的几何信息和属性信息。\[1\]\[2\]\[3\]
#### 引用[.reference_title]
- *1* *2* *3* [pgsql ,获取postgis地理信息,返回geojson对象](https://blog.csdn.net/weixin_45032766/article/details/127407351)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item]
[ .reference_list ]