5

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\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\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

ziggy
  • 4,515
  • 3
  • 37
  • 83
  • Do you need to specify filename with extension? C:\path\Desktop\geo.json for example? – ProudGIS Apr 03 '17 at 13:11
  • no that would work for only 1 file – ziggy Apr 03 '17 at 13:14
  • Lots of loop options are listed here - http://gis.stackexchange.com/questions/25366/using-ogr2ogr-to-convert-all-shapefiles-in-directory. 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 03 '17 at 13:23
  • cool il give that a try – ziggy Apr 03 '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 03 '17 at 14:46
  • @ProudGIS look at my edit, still not working – ziggy Apr 03 '17 at 17:18

2 Answers2

3

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\Desktop\geo\%f.json 
PG:"host=localhost user=postgres dbname=free password=xx" %f -t_srs EPSG:4326
ziggy
  • 4,515
  • 3
  • 37
  • 83
ProudGIS
  • 753
  • 3
  • 18
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.

Matte
  • 6,235
  • 13
  • 18