SQL query to have a complete geojson feature from PostGIS?

Question

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.

Answer 1

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;

Answer 2

This answer could be used with PostgreSQL version anterior to 9.4. Use dbaston's answer for PostgreSQL 9.4+

The query is the following: (where 'GEOM' is the geometry field, id the field to include in json properties, shapefile_feature the table name, and 489445 is the id of the feature wanted)

SELECT row_to_json(f) As feature \
     FROM (SELECT 'Feature' As type \
     , ST_AsGeoJSON('GEOM')::json As geometry \
     , row_to_json((SELECT l FROM (SELECT id AS feat_id) As l)) As properties \
     FROM shapefile_feature As l WHERE l.id = 489445) As f;

output:

{
   "geometry":{
      "type":"MultiPolygon",
      "coordinates":[
         [
            [
               [
                  -309443.24253826,
                  388111.579584133
               ],
               [
                  -134666.391073443,
                  239616.414560895
               ],
               [
                  -308616.222736376,
                  238788.813082666
               ],
               [
                  -309443.24253826,
                  388111.579584133
               ]
            ]
         ]
      ]
   },
   "type":"Feature",
   "properties":{
      "feat_id":489445
   }
}

Answer 3

@dbaston's answer has been modified lately by @John Powell aka Barça, and it produces invalid geojsons on my end. As modified, the aggregation on features returns each feature nested inside a json object, which is invalid.

I don't have the reputation to comment directly on the answer, but the final jsonb_agg should be on the column "feature" and not on the "features" subquery. Aggregating on the column name (or "features.feature" if you find it neater) puts every element straight in the "features" array after the aggregation, which is the right way to go.

So the following, which is pretty similar to @dbaston's answer as it was up until a few weeks ago (plus @Jonh Powell's correction to subquery naming) does work:

SELECT jsonb_build_object(
  'type',     'FeatureCollection',
  'features', jsonb_agg(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;

Answer 4

Just a slight correction to dbaston's answer (I would comment but I don't have points) You need to cast ST_AsGeoJSON's output as json (the ::json thingie):

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;

Otherwise the geometry member is going to be a string. That's not valid GeoJSON

Answer 5

For me the solutions didn't worked with PostGIS 3.2.1 and PostgreSQL 14.5. But in the current docs of PostGIS for ST_AsGeoJSON, there is an even simpler solution with json_agg for this problem. Adapted to this situation, this should be:

SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(ST_AsGeoJSON(lg.*)::json)
)
FROM locations as lg

This retrieves a valid GeoJSON FeatureCollection, including properties.

Answer 6

The json_agg solution is wonderfully simple! Since others coming to this are likely to be as new to SQL as I am, a couple of things I had to muddle through:

1. I needed to build a sub-table to apply ST_Transform first, and
2. PostGIS seems to prefer column headings in quotes even if they're only 1 word.

So I ended up with:

SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', json_agg(
    ST_AsGeoJSON(transformed)::json 
  )
)
FROM (
 SELECT "Name", "Description", "Date", ST_Transform(geom, 4326) AS geom 
 FROM My_PostGIS_Table
) AS transformed