gpt4 book ai didi

postgresql - 将 PostGIS 表 (postgresql) 转换为 GeoJSON

转载 作者:行者123 更新时间:2023-11-29 14:27:36 24 4
gpt4 key购买 nike

我访问了How to convert from PostgreSQL to GeoJSON format?中显示的问题

此 PostGIS SQL 将整个表转换为 GeoJSON 结果:

SELECT row_to_json(fc) AS geojson FROM 
(SELECT 'FeatureCollection' As type, array_to_json(array_agg(f))
As features FROM
(SELECT
'Feature' As type,
ST_AsGeoJSON((lg.geometry),15,0)::json As geometry,
row_to_json((id, name)) As properties
FROM imposm3_restaurants As lg) As f ) As fc;

我发现在结果中,我们没有得到字段的名称。

我希望输出为 "properties":{"id":6323,"name":"锡纳亚餐厅"

但实际输出是 "properties":{"f1":6323,"f2":"锡纳亚餐厅"

看了row_to_json指令的规范,决定把最后一条row_to_json指令改掉

SELECT row_to_json(fc) AS geojson FROM 
(SELECT 'FeatureCollection' As type, array_to_json(array_agg(f))
As features FROM
(SELECT
'Feature' As type,
ST_AsGeoJSON((lg.geometry),15,0)::json As geometry,
row_to_json((lg)) As properties
FROM imposm3_restaurants As lg) As f ) As fc;

但现在 geojson 还检索几何字段作为属性。

我的意思是,在结果中我可以看到以 geojson 格式和 PostGIS 格式格式化的几何图形(这第二个几何图形不是必需的,我可以浪费它)所以如果第一个结果是 1200Kb,第二个将是2300Kb。

我能做什么?任何替代品

row_to_json((id, name)) As properties

row_to_json((lg)) As properties

我也尝试过类似的东西

row_to_json(('id',lg.id ,'masa',lg.masa ,'parcela',lg.parcela)) As properties

和任何其他,但没有结果(只有 SQL 错误)

非常感谢

最佳答案

您需要做的是,首先选择您的列,然后选择 row_to_json。根据您的值(value)观,这将给出以下示例:

SELECT
row_to_json(fc)
FROM (
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
id
, name
) AS t
) AS properties
FROM imposm3_restaurants
) AS f
) AS fc

关于postgresql - 将 PostGIS 表 (postgresql) 转换为 GeoJSON,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56163349/

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