将PostGIS表(postgresql)转换为GeoJSON

Question

我访问了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":"Restaurant Sinaia“

但是实际的输出是“属性”：{“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还将几何字段作为属性进行检索。

我的意思是，在结果中，我可以看到以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错误)

非常感谢

Answer 1

你需要做的是，首先选择你的列，然后row_to_json这个select。使用您的值，这将给出以下示例：

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

Answer 2

您可以使用ogr2ogr在控制台中执行此代码

cd C:\\OSGeo4W64\\bin && ogr2ogr -f "GeoJSON" C:\Users\Documents\nameFile.json "PG:dbname=nameBD schemas=NameSchema host='localhost' port='5432' user='postgres' password='**'" -sql "select * from public.tableName"