Converting PostGIS table (postgresql) to GeoJSON

Question

I visited the question showed in How to convert from PostgreSQL to GeoJSON format?

This PostGIS SQL transforms the whole table into a GeoJSON result:

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;

I've found there that in the results, we don't get the name of the fields.

I expect the output as "properties":{"id":6323,"name":"Restaurant Sinaia"

but the actual output is "properties":{"f1":6323,"f2":"Restaurant Sinaia"

I read the specification of the row_to_json instruction, so I decided to change the last row_to_json instruction

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;

But now the geojson retrieves also the geometry field as a property.

I mean, into the results I can see the geometry formated in geojson format and again in PostGIS format (this second geometry is not necessary, I could waste it) so if the first result is 1200Kb, the second it will be around 2300Kb.

What can I do? any alternatives to

row_to_json((id, name)) As properties

or

row_to_json((lg)) As properties

I also tried things like

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

and any others, but with no results (only SQL errors)

Thank you very much

Answer 1

What you need to do, is first select your columns then row_to_json this select. With your values, this will give following example :

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

For passers by, you don't need subqueries and jsonb has slightly better performance. That being said, this shaved off ~150ms in dumping 11k records on a wide-ish table. To make this scale you'll definitely want to LIMIT + OFFSET or toss in a WHERE to filter out things you don't need

WITH features AS (
  SELECT jsonb_build_object(
    'type', 'Feature',
    'geometry', ST_AsGeoJSON(geom)::jsonb,
    'properties', jsonb_build_object(
      'id', id,
      'name', name
    )
  ) AS feature
  FROM imposm3_restaurants
)
SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(feature)
)::json
FROM features;