Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
1  
Doesn't Geoserver support SQL Server 2008 now? –  R.K. Nov 8 '12 at 7:19
1  
Related gis.stackexchange.com/questions/12989/… –  R.K. Nov 8 '12 at 7:25
1  
Can the Data Interop ETL model be scripted in Python and then run as a job? –  Chad Cooper Nov 8 '12 at 14:07
add comment

1 Answer

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.

share|improve this answer
2  
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 '12 at 7:56
1  
Noted. Will wait for the OP's feedback just in case :) –  R.K. Nov 8 '12 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 '12 at 4:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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