52

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.

1
  • I had to do a proof of concept for another app so put together this repo which, in part uses the answers from here. Hopefully helps getting started with this stuff - find it here: pg-us-census-poc
    – zak
    Oct 10, 2019 at 9:02

4 Answers 4

89

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;
9
  • 1
    This functionality alone has me scrambling to upgrade from 9.3.5 to 9.5.3 this morning. If only it was as simple as regexp_replace(current_setting('server_version'),'(\d)\.(\d)\.(\d)','\1.\3.\2')...
    – GT.
    Jul 12, 2016 at 22:18
  • 1
    OK - all upgraded now (although can't get 9.5.3 to run as a Windoze service). Anyhow... one little thing about the example given - the second json_build_object has colons instead of commas.
    – GT.
    Jul 13, 2016 at 10:19
  • does not work for me on pg v9.6
    – Pak
    May 19, 2017 at 13:22
  • 2
    For completeness, it's likely that the geometry vertices are not in the correct order for strict geojson (the right handed rule), to rectify that, we can reorder the vertices in the geom with ST_ForcePolygonCCW - postgis.net/docs/manual-dev/ST_ForcePolygonCCW.html
    – chrismarx
    Nov 2, 2017 at 13:30
  • 1
    @chrismarx this is a good point and raises the issue of whether PostGIS' ST_AsGeoJSON function should be modified to correct the orientation on its own.
    – dbaston
    Nov 6, 2017 at 16:51
25

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
   }
}
4
  • since you moved this from the body of your question to the answer, does this mean this query and result now working correctly? Running this through GeoJSONLint, it still does not appear to be giving valid output. Aug 28, 2014 at 20:05
  • 1
    Great, that makes sense. I guess I just didn't look closely enough. Feel free to mark this as "Accepted" once GIS.SE allows it to close out the question. Thanks! Aug 29, 2014 at 14:38
  • 1
    It's not just GeoJSONLint that doesn't accept single quotes. JSON doesn't formally recognize single quotes, either. If any parser recognizes them, it's a nonstandard extension and probably best avoided.
    – jpmc26
    Aug 30, 2016 at 0:34
  • @BelowtheRadar That is a dict, not JSON. They are very different things. JSON is a string. Always. It's a text format, in the same way XML is just a text format. A dict is an in memory object.
    – jpmc26
    Aug 30, 2016 at 15:12
10

@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;
9

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.