How to export thousands of features from PostGIS to a single GeoJSON file?

Question

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?

Answer 1

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.

Answer 2

Use SQL abilities, in particular IN operator instead of processing each GeoJSON after your SQL query.

Your

$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'

would be

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

I suppose that if you provide thousands of id, they come from another table. So another alternative could be to use JOIN SQL keyword to get them. See the documentation at http://www.gdal.org/ogr_sql.html