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.

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 '19 at 09:02

score 94 · Answer 1 · edited Jun 27 '18 at 14:58

94

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;

edited Jun 27 '18 at 14:58

jufaua

308
3
6

answered Apr 27 '16 at 18:56

dbaston

13,048
3
49
81

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
Typos in the last query: to_json is actually to_jsonb, andFeatureCollectionis wrapped in''. Moreoversqshould befeatures`. – Pak May 19 '17 at 13:32
@dbaston you're welcome. How would you wrap the whole thing in a main json key: my_features: ? Somehow I can't get it to work, using another json_build_object adds nested jsonb_build_object keys to my JSON document. – Pak May 19 '17 at 14:29
Genius. This is so much nicer than the old array_agg, row_to_json, array_to_json approach. – John Powell Aug 30 '17 at 10:23
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 - https://postgis.net/docs/manual-dev/ST_ForcePolygonCCW.html – chrismarx Nov 02 '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 06 '17 at 16:51
I edited this as the argument to jsonb_agg for creating a FeatureCollection should be to features, not feature, which refers to the inner query where the geometry and properties are created. I also think using row as an alias is potentially confusing, as it is a keyword, so changed it to inputs. Feel free to undo. Once again, such as useful answer. – John Powell May 04 '18 at 12:43

score 26 · Accepted Answer · edited Apr 13 '17 at 12:34

26

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
   }
}

edited Apr 13 '17 at 12:34

Community

1

answered Aug 28 '14 at 19:14

Below the Radar

3,593
1
31
58

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. – RyanKDalton 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! – RyanKDalton 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 00: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

score 10 · Answer 3 · answered Jun 27 '18 at 13:50

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

score 9 · Answer 4 · edited Apr 13 '17 at 12:33

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

score 1 · Answer 5 · edited Sep 07 '23 at 06:25

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.

Martyn Hanmore · Answer 6 · 2024-01-14T16:58:14.187

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:

I needed to build a sub-table to apply ST_Transform first, and
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

SQL query to have a complete geojson feature from PostGIS?

6 Answers6

Linked

Related