ogr2ogr PostgreSQL to geojson batch export

Question

trying to export 20 postgreSQL tables to geoJSON using ogr2ogr
ogr2ogr -f "GeoJSON" C:pathDesktopgeo 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:UsersDesktopgeo

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:UsersDesktopgeo%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

ProudGIS · Accepted Answer

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:UsersDesktopgeo%f.json 
PG:"host=localhost user=postgres dbname=free password=xx" %f -t_srs EPSG:4326

Matte · Answer

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

C:pathDesktopgeo 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.

ogr2ogr PostgreSQL to geojson batch export

2 Answers

Add your own answers!

Ask a Question