PostGIS to GeoJSON – SQL Query to Get Complete GeoJSON Feature from PostGIS

geojsonpostgispostgresqlsql

I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature.

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(lg.geog)::json As geometry
    , row_to_json(lp) As properties
   FROM locations As lg 
         INNER JOIN (SELECT loc_id, loc_name FROM locations) As lp 
       ON lg.loc_id = lp.loc_id  ) As f )  As fc;

so far I tryed to modify the feature collection query of the example. but the output is not valid.

Best Answer

This can be done a bit more simply with json_build_object in PostgreSQL 9.4+, which lets you build up a JSON by supplying alternating key/value arguments. For example:

SELECT json_build_object(
    'type',       'Feature',
    'id',         gid,
    'geometry',   ST_AsGeoJSON(geom)::json,
    'properties', json_build_object(
        'feat_type', feat_type,
        'feat_area', ST_Area(geom)::geography
     )
 )
 FROM input_table;

Things get even better in PostgreSQL 9.5+, where some new operators are added for the jsonb data type (docs). This makes it easy to set up a "properties" object that contains everything but the id and geometry.

SELECT jsonb_build_object(
    'type',       'Feature',
    'id',         gid,
    'geometry',   ST_AsGeoJSON(geom)::jsonb,
    'properties', to_jsonb(row) - 'gid' - 'geom'
) FROM (SELECT * FROM input_table) row;

Want to make a FeatureCollection? Just wrap it all up with jsonb_agg:

SELECT jsonb_build_object(
    'type',     'FeatureCollection',
    'features', jsonb_agg(features.feature)
)
FROM (
  SELECT jsonb_build_object(
    'type',       'Feature',
    'id',         gid,
    'geometry',   ST_AsGeoJSON(geom)::jsonb,
    'properties', to_jsonb(inputs) - 'gid' - 'geom'
  ) AS feature
  FROM (SELECT * FROM input_table) inputs) features;
Related Question