32

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.

54

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;
  • 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 '16 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 '16 at 10:19
  • does not work for me on pg v9.6 – Pak May 19 '17 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 '17 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 '17 at 16:51
21

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
   }
}
  • 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. – RyanDalton Aug 28 '14 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! – RyanDalton Aug 29 '14 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 '16 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 '16 at 15:12
5

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

4

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

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.