5

I have spatial tables in ArcSDE 10.1 (MSSQL 2008 R2) geodatabases and need to update the same spatial tables in PostgreSQL 9.1(PostGIS 2.0.1) geodatabases. Basically I use my ArcSDE environment for editing and geoprocessing, but my final output needs to be in Postgres(PostGIS) data store for geoserver. The main hurdle for me is keeping my data updated and in sync (no pun intended) between databases.

Using the Data Interoperability extension I set up a spatial ETL model using the "quick export" toolset and can effortlessly move my spatial table(s) from an ArcSDE geodatabase to a PostGIS geodatabase. This is only a one time dump though. How can I regularly keep my table(s) updated in PostGIS as I make update/insert/delete changes in my ArcSDE geodatabases. I know with ArcGIS 10.1 SP1 you should be able to connect, view and edit spatial data in PostGIS geodatabases if you are using ArcSDE for PostgreSQL. I have ArcSDE 10.1 over MSSQL 2008. Ideally I would need to update my Postgres databases tables once a day to reflect changes made in the ArcSDE geodatabases tables. I am banging my head trying to explore triggers, stored procedures, SQL "magic" and even python scripting.

Please help

Improve this question
3

1 Answer 1

Reset to default
1

GeoServer supports SQL Server 2008 data source through an extension. Have you tried it?

enter image description here

enter image description here

If it works for your case then you won't need to sync it with PostGIS. You can just use SQL Server directly.

Improve this answer
3
  • 3
    ArcSDE is a layer on top of MSSQL. I do not think this will work properly since ArcSDE is needed to properly read geography from SQL layers.
    – ragnvald
    Nov 8, 2012 at 7:56
  • 1
    Noted. Will wait for the OP's feedback just in case :)
    – R.K.
    Nov 8, 2012 at 8:19
  • @R.K. This data will be used in the ESRI environment for further analysis before the ETL to PostGIS, hence stuck with SDE/PostGIS ETL. This is why using SQL server as a data store isn't an option at least for some of our data. Also the tables have been registered with ArcSDE geodatabases they aren't technically SQL server native. I converted the ETL model to python and will run as a job but this is like a bulkoad and I need to be able to update the table with each ETL. Not sure of the postgres commands to update or merge additional data after the first import and how to auto execute that.
    – EnzovsJacques
    Nov 10, 2012 at 4:09

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.