4

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?

  • Do neither of these solutions work for you? If not, could you state what is missing, so we can attempt to augment the answers. – John Powell Dec 8 '14 at 10:51
15

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.

  • What if you don't have any properties you'd like to include (i.e. your GeoJSON feature is only geometry)? Basically, how do you include an empty properties object? Ah .. figured it out: NULL As properties – Martin Burch Dec 17 '14 at 5:29
7

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

  • You can also use ogr2ogr with -append and add more features into an existing GeoJSON file. – user30184 Dec 5 '14 at 11:22
  • You are right. Possible but It will require to loop for each id in the question scenario. In another context, I would personally adopt your proposed option ;) – ThomasG77 Dec 5 '14 at 11:33

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.