PostGIS - How to do ST_UNION on each feature of a geojson FeatureCollection

Question

I have a simple geoJSON featureCollection composed of 2 features:

featureCollection = {u'type': u'FeatureCollection', u'features': [{u'geometry': {u'type': `u'MultiPolygon', u'coordinates': [[[[-184557.61264043, 384896.54253906], [-134666.391073443, 239616.414560895], [-308616.222736376, 238788.813082666], [-184557.61264043,384896.54253906]]]]}, u'type': u'Feature', u'properties': {u'feat_id': 0}}, {u'geometry': {u'type': u'MultiPolygon', u'coordinates': [[[[-313889.06448095, 334448.10387786], [-206265.7286704, 279107.6954071], [-249376.21261724, 359825.19726501], [-31388`9.06448095, 334448.10387786]]]]}, u'type': u'Feature', u'properties': {u'feat_id': 2}}]}

I would like to do a ST_UNION on those features in order to have only one feature merged.

SQLQuery = '''SELECT ST_AsGeoJSON(ST_Union(%s))::json As f''' % (featureCollection)

First, the ST_GeomFromGeoJSON() function accepts only the geometry part of the geoJSON as input, so the featureCollection gives an error.

Do I have to loop over the featureCollection to extracts each feature's geometry or is there another solution ?

Have you checked out http://gis.stackexchange.com/questions/14514/how-to-export-feature-geojson-from-postgis?rq=1 — raphael, Sep 16 '14 at 18:29
@raphael thank you but I want a PostGIS solution, not ogr2ogr — Below the Radar, Apr 21 '16 at 11:44

score 6 · Accepted Answer · answered Sep 17 '14 at 16:38

You must first extract the geometries from the JSON before merging them. This should do the trick:

WITH source AS(
SELECT '{"type": "FeatureCollection",
 "features": [{
    "geometry": {"type": "MultiPolygon"
            , "coordinates": [[[[-184557.61264043, 384896.54253906], [-134666.391073443, 239616.414560895], [-308616.222736376, 238788.813082666], [-184557.61264043,384896.54253906]]]]}
, "type": "Feature", "properties": {"feat_id": 0}}, 
{"geometry": {"type": "MultiPolygon", "coordinates": [[[[-313889.06448095, 334448.10387786], [-206265.7286704, 279107.6954071], [-249376.21261724, 359825.19726501], [-313889.06448095, 334448.10387786]]]]},
             "type": "Feature", "properties": {"feat_id": 2}}]}'::json as json
             ),
geom AS(
    SELECT ST_GeomFromGeoJSON((json_array_elements(json->'features')->'geometry')::text) AS g FROM source
)            
SELECT ST_AsGeoJSON(ST_Union(g))::json FROM geom;

PostGIS - How to do ST_UNION on each feature of a geojson FeatureCollection

1 Answers1