19

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.

Improve this question
4
  • 1
    Doesn't ArcSDE manage a RDBMS datastore? What is your data in now?
    – Sean
    Aug 2, 2011 at 19:24
  • 1
    What Version of ArcSDE are you using? - ArcSDE 10 can write to Postgres. resources.arcgis.com/content/arcsde/10.0/…
    – Mapperz ♦
    Aug 2, 2011 at 20:52
  • If you read Med's question carefully, I think s/he is really trying to replicate the data onto PostGIS for testing and web publishing purposes until he can successfully transition off of ArcSDE. So the point is not to synchronize ArSDE to native PostGIS for the long term, but just for the short term.
    – RyanKDalton
    Aug 2, 2011 at 21:28
  • Perhaps synchronize was a poor chose of question title. Indeed, i want this as a short term method of moving and maintaining datasets from SDE to PostGIS. my SDE has about 600 datasets on it and i dont want to be mirgrating the data manually. Med (mr).
    – geosmiles
    Aug 4, 2011 at 18:12

7 Answers 7

Reset to default
19

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.

Improve this answer
6
  • +1 for a very clear and helpful response. Even being complicated, it is a direct path.
    – D.E.Wright
    Aug 3, 2011 at 0:57
  • In theory i like the idea of doing a direct DB transfer but i'm looking for something 'billy basic'. However we are SDE 9.3.1 at the mo, therefore the above answer isn't a go'a. As i say i'm new to PostGIS and SQL but i would have thought that there was a script 'out there' where by you could automate a bulk upload!
    – geosmiles
    Aug 4, 2011 at 18:00
  • 1
    The other "DB->DB" option for all versions of SDE (and hundreds of other formats/DBs) is FME. FME is non-free (there is a free trial you can test out), which means they can provide support to help your synchronization solution. But this obviously requires a budget. The best free option is to tailor a good BAT script with shp2pgsql, psql, etc.
    – Mike T
    Aug 4, 2011 at 22:32
  • 2
    One of reasons I wrote the ArcObjects GDAL driver was to do exactly this. Since they have an ESRI license, the wanted to maintain their complex FeatureClasses and dump them to PostGIS every night. The arcsde driver goes through the arcsde api layer, but the arcobjects driver goes, well, through arcobjects. We have been using it for a year and it has worked quite nicely.
    – Ragi Yaser Burhum
    Aug 6, 2011 at 22:25
  • Where you get the "SDE" driver so you can use that existing Python script? I can't find...
    – ePascoal
    Oct 16, 2015 at 15:35
6

PostGIS has a loader named shp2pgsql that you can use to load shapefiles into a PostGIS database. One of its options ( "-d" ) drops the existing database table before loading the data. It should be pretty simple to create a batch file or script that can loop through all of the shapefiles and load them for you.

If you wanted to get fancier about it, you could install GDAL, and use the OGR tools (which have an ArcSDE connector) and skip the shapefile export. Info on the ArcSDE connector can be found here.

Improve this answer
2
  • In billy basic terms, how/what would i write a script (to be added as a windows scheduled task) to drop an exsiting table, lets say its called Listed_Buildings and then upload a newer version of it held as a shp in D:\sde_export.
    – geosmiles
    Aug 4, 2011 at 18:08
  • 1
    shapefile approach is worse when field name is greater than 11 chars since other 11 chars will be deleted!!
    – Learner
    Sep 7, 2015 at 17:20
2

I know I'm late to the party but there's another option for this that avoids having 2 databases.

You can have ArcSDE on top of Postgresql+Postgis. This would be your master and only db.

ArcSDE can be configured to use postgis geometries and not st_geometries (ESRI proprietary).

This means you can then use any postgis tool to directly use ArcSDE loaded/edited spatial tables, since they are in fact postgis native tables.

For instance you can use qgis to connect to the postgis database directly and read the same data as arcgis through arcsde.

Improve this answer
1

Another approach would be to use a spatial ETL like Geokettle (open source).

http://www.spatialytics.org/projects/geokettle/

I use Geokettle to move data between sql server and PostGIS all the time. However, while it works well with PostGIS there are challenges when working with SQL Server's spatial data type (see hints below). Also, this assumes you are storing SDE geometries as the SQL Server native spatial data type. You can do this with the Geometry keyword when loading data through ArcCatalog.

Hint 1: when selecting data FROM sql server apply .STAsText() to the geometry field or Geokettle will choke on the sql server data type.

Hint 2: When inserting data INTO sql server you will need to insert the geometry as text into a text field. Then use the sql step to add a new geometry column (if needed) and populate it from the text geometry. That way you use SQL Server to build the geometry from an OGC standard text representation of a geometry.

Hint 3: Be sure to register your new spatial table with SDE.

Improve this answer
1
  • to use SDE.ST_AsText() you will need to have SDE.ST_GEOMETRY_SHAPELIB_PKG, am i right?
    – ePascoal
    Oct 15, 2015 at 10:36
0

Consider setting up database replication on the original 9.3 db.
It doesn't work as well as the new 10 does but...
replicate that to another sde in postgresql using the postgis data type pg_geometry.

Here is some esri help
NOTE: This will require duplicate licenses if both dbs are not on the same machine

Improve this answer
2
  • Getting a duplicate licence seems to be defeating the point of mirgrating to open source!
    – geosmiles
    Aug 4, 2011 at 18:02
  • If your original db were in postgresql you could do it all in the same db (multi-sde instances). OR install postgresql on the same machine as your sde. If not you might consider it development and buy EDN (another sde) for the interim ~2.5k yr.
    – Brad Nesom
    Aug 4, 2011 at 18:11
0

You could write ArcObjects to:

A) dump your arcsde database; B) Plain c# code to generate shp2pgsql sqls; C) Plain c# code to dump old tables; D) Plain c# to execute all shp2pgsql into your postgresql;

You can use npgsql driver to achieve this, executing functions and sql statements directly into your postgis database;

Improve this answer
0

What is your ArcSDE geometry (Geodatabase storage in relational databases) ? In a such scenario, you may consider using the ST_Geometry type and then use PostGre/PostGIS replication (PostGIS Replication @ FOSS4G) tools. This scheme may have the advantage of using ArcSDE/ArcGIS/geodatabase features for edition (edition database) and open-source tools for the diffusion (replicated diffusion database).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.