我浏览了这个问题:如何将PostgreSQL转换成GeoJSON格式?
这个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":"Restaurant Sinaia"
但实际输出结果是: "properties":{"f1":6323,"f2":"Restaurant Sinaia"
我阅读了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也作为属性检索geometry字段。
我的意思是,在结果中,我可以看到以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错误)
非常感谢