enterprise geodatabase - Migrating data from ArcSDE to PostGIS?

Its been suggested that i ask this question again, but perhaps worded differently than how to Bulk load multiple shapefiles into PostGIS.

I'm new to postgis and sql but i want to use postgis as a slave (for the time being) to ArcSDE. Therefore what i'm after is some help in woking out how i might go about batch uploading ArcSDE data into PostGIS every night.

I can produce a python script to export ArcSDE data into SHP nightly, but i'm then not sure how to then batch upload the exported SHP files into PostGIS, overwriting those that already existed in postgis all as an automative task (batch file i guess).

My grand plan is to replace ArcSDE/ArcGIS in time with PostGIS, QGIS and GeoServer or MapServer.

Answer

Rather than doing a DB -> file -> DB conversion, where Esri Shapefiles are the middleman file-based component, it could be worthwhile investigating a more direct DB -> DB transfer. There are a few tools out there that can do this, but I'm only focusing on the open source GDAL/OGR tools.

Assuming you have a Windows Server, you can easily install GDAL/OGR using OSGeo4W. With the Advanced Install mode, you have the ability to select custom package configurations, such as the SDE driver (see this list of packages to ensure there is a suitable match for your SDE version -- only SDE versions 9.0 to 9.2 are supported). These packages are only the headers and wrappers, and requires you to install the SDE libraries, which you should have on a CD/DVD ROM, and ensure the PATH variables are appropriately set for GDAL/OGR to find it. A successful install will show "SDE" somewhere with the following command in an OSGeo4W shell: ogr2ogr --formats

After you configure GDAL/OGR, you can use a few tools:

• The command-line tool ogr2ogr from the OSGeo4W shell, which will need a command something like: ogr2ogr -f "PostgreSQL" PG:"host=localhost user=someuser dbname=somedb password=somepassword port=5432" \ SDE:server,instance,database,username,password,layer,[version] (you can also explore the various -append, -overwrite or -update options)


• Copy and modify an existing Python script that connects to SDE, and you could modify it to transfer data to another data source.

The major caveat to this method is that it is complicated to setup.