[GIS] How to export thousands of features from PostGIS to a single GeoJSON file

geojsonogr2ogrpostgis

I know how to export one feature from a PostGIS database using ogr2ogr:

$ogr2ogr -f GeoJSON out.json "PG:host=localhost dbname=xxx user=xxx password=xxx" 
 -sql 'select id, county, wkb_geometry from cities where id=47927526'

This produces a nice GeoJSON file with one feature. But how can I run this query for thousands of IDs and produce a single file?

Currently my approach is to produce many GeoJSON files and glue them all together, but I'm wondering if there's a better way?

Best Answer

There is, yes, using a combination of row_to_json, array_to_json, array_agg and ST_AsGeoJSON. I realize, on rereading your question, that you asked for ogr2ogr approach, but seeing as your source is Postgis, I thought you might appreciate a pure Postgres/Postgis approach. I have used this approach with Google Maps, so it works well if you need to create GeoJSON dynamically, which would be tricker with ogr2ogr.

SELECT row_to_json(featcoll)
   FROM 
    (SELECT 'FeatureCollection' As type, array_to_json(array_agg(feat)) As features
     FROM (SELECT 'Feature' As type,
        ST_AsGeoJSON(tbl.geom)::json As geometry,
       row_to_json((SELECT l FROM (SELECT id, prop1, prop2, ...) As l)
  ) As properties
  FROM your_table As tbL   
   WHERE st_intersects(geom, st_setsrid(st_makebox2d(st_makepoint(x1, y1),st_makepoint(x2, y2)), srid))    
 )  As feat 
)  As featcoll;

where there is an optional where clause with an ST_Intersects. In the inner query, you can add to Select id, prop1, etc, to get a list of any properties that you would like to appear in the output GeoJSON.

The original idea for this comes from this BostonGIS blog

EDIT: If you have Postgres 9.4 or higher, use @dbaston's answer. It is so much cleaner.

Related Question