我访问了如何从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,"名称":"餐厅Sinaia"
但是实际输出是 " properties":{" f1":6323," f2":"餐厅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也将几何字段作为属性。
我的意思是,在结果中,我可以看到以geojson格式形成的几何形状,并以gis 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此选择。以您的价值观,这将提供以下示例:
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
您可以在控制台中使用OGR2OGR执行此代码
cd C:\OSGeo4W64\bin && ogr2ogr -f "GeoJSON" C:UsersDocumentsnameFile.json "PG:dbname=nameBD schemas=NameSchema host='localhost' port='5432' user='postgres' password='**'" -sql "select * from public.tableName"