4

trying to export 20 postgreSQL tables to geoJSON using ogr2ogr

ogr2ogr -f "GeoJSON" C:\path\Desktop\geo PG:"host=localhost user=postgres dbname=free password=xx" -t_srs EPSG:4326

getting these 2 errors

ERROR 6: The GeoJSON driver does not overwrite existing files.
ERROR 1: GeoJSON driver failed to create C:\Users\rizagha\Desktop\geo

I know there is a for loop option but I cannot figure out how to construct it or where to even the cmd location to write it in? in the GDAL folder

For %f in (* PG:"host=localhost user=postgres dbname=free password=xx ACTIVE_SCHEMA=public") do ogr2ogr -f "GEOJSON"% ~ nf.json% f

with my modified script

For %f in ("oak_available_parcels","oak_buffers_union","oak_nonavailable_parcels","oak_park_buffers","oak_park_parcels","oak_reccon_buffers","oak_school_buffers","oak_school_parcels","oak_worship_buffers","oak_worship_parcels","parcels","park_centroid","school_centroid","worship_centroid") 
do ogr2ogr -f "GeoJSON" C:\Users\rizagha\Desktop\geo\%f.json 
PG:"host=localhost user=postgres dbname=free password=xx" %f -t_srs EPSG:4326

giving me this error: what is wrong with my cmd statement???

FAILURE: Unable to open datasource `user=postgres' with the following drivers

and postgres is my username for that database

  • Do you need to specify filename with extension? C:\path\Desktop\geo.json for example? – ProudGIS Apr 3 '17 at 13:11
  • no that would work for only 1 file – ziggy Apr 3 '17 at 13:14
  • Lots of loop options are listed here - gis.stackexchange.com/questions/25366/…. I'm not sure how Postgres will handle not specifying table name. Perhaps trial by running to shp, as this format seems to handle a directory and no filename – ProudGIS Apr 3 '17 at 13:23
  • cool il give that a try – ziggy Apr 3 '17 at 13:30
  • I think you will need to list the tables (either manually comma seperated or by some other means). This should run in cmd prompt: For %f in (list of tables) do ogr2ogr -f "GeoJSON" C:\path\Desktop\geo\%f.json PG:"host=localhost user=postgres dbname=free password=xx" %f -t_srs EPSG:4326 – ProudGIS Apr 3 '17 at 14:46
2

I have tested this my end and it only needs a minor change from the query you added (based on my comment above). If you remove the double quotes from the table names it should work.

For %f in (oak_available_parcels,oak_buffers_union,oak_nonavailable_parcels,oak_park_buffers,oak_park_parcels,oak_reccon_buffers,oak_school_buffers,oak_school_parcels,oak_worship_buffers,oak_worship_parcels,parcels,park_centroid,school_centroid,worship_centroid) 
do ogr2ogr -f "GeoJSON" C:\Users\rizagha\Desktop\geo\%f.json 
PG:"host=localhost user=postgres dbname=free password=xx" %f -t_srs EPSG:4326 
2

It is working without a loop if you want to export all table from one schema. Your output into the folder is correct.

C:\path\Desktop\geo PG:"host=localhost user=postgres dbname=free password=xx ACTICE_SCHEMA=public"

Alternativ is to use the option "tables=" instead of "Active_Schema=". In this case ogr2ogr uses a comma seperated list. You can create this list quite easy within postgres.

SELECT string_agg(table_name, ',') FROM information_schema.tables 
WHERE table_schema = 'public'

You can set another condition in the where clause to filter your tables if you don´t want all. Like:

WHERE table_schema = 'public' AND table_name LIKE '%somesearchstring%'

You then copy the output behind the tables= within your connection string.


This works only for the shapefile driver i realized (-f "ESRI Shapefile"). The GeoJSON driver does not support export to folder it seems.

For the GeoJson you need a loop:

Enter into the windows command:

for %x in (table1,table2) do ogr2ogr -f "GEOJson" C:\exportfolder\%x.json PG:"dbname= host= port= user= password= tables=%x"

You can use the output comma-seperated list from the postgres query directly into the parentheses.

  • i added ACTIVE_SCHEMA=public to my original statement, but it just outputs an empty JSON file in my geo folder – ziggy Apr 3 '17 at 13:53
  • how would I construct a for loop with postgres to GEOJSON – ziggy Apr 3 '17 at 13:54
  • Updated with the loop for GeoJSON driver. – Matte Apr 3 '17 at 14:32
  • FAILURE: Unable to open datasource `user=postgres' with the following drivers. giving me this error – ziggy Apr 3 '17 at 16:12

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.