3

I have an ogr2ogr command which uploads an ESRI File Geodatabase to a PostgreSQL database, updating/overwriting the existing data:

ogr2ogr -f "PostgreSQL" PG:"host=hosting_url port=5432 dbname=database_name user=username password=password" -lco SCHEMA=schema_name "D:\data\Data.gdb" -overwrite -progress -lco OVERWRITE=YES --config PG_USE_COPY YES

This works as expected when run from OSGeo4W Shell (installed with my QGIS installation). However, I'd like to automate this command to run at the same time as an existing python script I have. So my challenge is to run the ogr2ogr command from python. How can I make it compatible?

I've tried:

import os, subprocess
command = ["C:\\Program Files\\QGIS 2.18\\bin\\ogr2ogr.exe", 
        "-f", "PostgreSQL", "PG:\"host=hosting_url port=5432 dbname=database_name user=username password=password\"", "-lco", "SCHEMA=schema_name", "D:\data\Data.gdb", "-overwrite", "-progress", "-lco", "OVERWRITE=YES", "--config", "PG_USE_COPY", "YES"]

I adapted this code from the top answer here: Execute ogr2ogr from python

When I run it I get the error 'returned non-zero exit status 1.'

  • How are you actually executing it? check_output() will raise an exception that contains output that would be helpful for debugging – mikewatt Jan 10 at 17:34
  • Why not use the Python bindings for gdal? pypi.org/project/GDAL – pauldzy Jan 11 at 1:37
  • @gberard executing it in IDLE 3.6.5. I'll try that check_output() – Theo F Jan 11 at 9:21
  • You can probably also get some ideas from the Python OGR Cookbook--there are examples for reading ESRI FileGDB and writing to a PostGIS table that you could probably mash together. – neuhausr Jun 21 at 15:17
1

First, we need gdal installed and set an an environment variable in Windows. Then:

Uploading Shapefile to postgres:

import os
connection = r"host=localhost port=5432 dbname=db1 user=postgres password=password"
schema = "schemaname"
target_shp = r"D:\Data\shapefile.shp"
command = r'start cmd /K ogr2ogr -f "PostgreSQL" PG:"%s" -lco SCHEMA=%s "%s" -overwrite -progress -lco OVERWRITE=YES' % (connection, schema, target_shp)
print(command)
os.system(command,)

Uploading an ESRI FileGeodatabase Feature class to postgres:

import os
connection = r"host=localhost port=5432 dbname=db1 user=postgres password=password"
schema = "schemaname"
target_gdb = r"D:\Data\Geodatabase.gdb"
target_fc = "FeatureClassName"
command = r'start cmd /K ogr2ogr -f "PostgreSQL" PG:"%s" -lco SCHEMA=%s "%s" "%s" -overwrite -progress -lco OVERWRITE=YES' % (connection, schema, target_gdb, target_fc)
print(command)
os.system(command,)

If you want to upload the entire GeoDatabase, not just a Feature Class, then remove the last "%s" in the 'command' variable and remove the 'target_fc' line. Also remove the reference to 'target_fc' at the end of the 'command' variable.

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.