ArcSDE Database Refactoring Tool options

Question

We are using liquibase as evolutionary DB change management tool in our applications, it works great when we use it in "common" database schemas.

But we also work with GIS applications using esri arcSDE 9.3 platform over Oracle and in this case, all (or almost all) tables (both GIS and 'alphanumeric' tables) in the schema are managed (create table, grants, etc.) through arcSDE. So when we want to create new feature classe now we use arcCatalog, and this way, it's not possible to manage the feature classes’ changes directly through SQL using liquibase or other automate refactoring tool.

So if we cannot use liquibase to manage changes, at least we want to execute the management operations over our features through command line. We’ve started looking for tools that avoid the use of arcCatalog, and then try to automate the changes using scripts, we are investigating these possibilities:

1. Try to capture the SQL that arcCatalog/arcSDE is executing each time we make a change in one Feature Class monitoring the oracle connection. It outcomes us a too complex set of SQL instructions that involves indexes, versioning tables, etc. so we give up this way.

2. Use the sdelayer and sdetable admin commands installed on arcSDE server.

3. Use the data management tool: a python based library to manage the feature classes, but it has to be executed from a machine with the desktop version installed.

These last two options will provide a way to manage features from command-line, but our target is to find/develop a tool to manage changes similar to the way liquibase do. But with these tools we will have to find a tool that let us map each SQL DDL operation to an arcSDE command, and currently no db refactoring tool provides this (currently we have check liquibase, dbdeploy, flyway).

Anybody had solved this problem of evolutionary change management with arcSDE? Any insight of another way to face this problem?

Answer 1

I'll take a stab at this, although I'm unfamiliar with one of the products you mention (liquibase specifically - I have used Oracle and I'm very familiar with ArcGIS (ArcMap & ArcCatalog).

Here's just some additional information that may help and my interpretation of your question.

My interpretation - "What's a simple way to manage or enable us to automate the management of our tables of GIS data in our Oracle database without having to use ArcCatalog all the time?"

So - I'll throw this concept back in the ring - I know SQL Server has spatial datatypes "geometry", etc. and that you can bypass SDE and let ArcGIS directly connect and interpret this data without even installing SDE. I also know Oracle has compatible spatial types. So I would possibly consider migrating my data from the managed FC's that ArcCatalog creates and push them into oracle-native geometry based tables. This way you can treat them like regular tables, cut ESRI out of the solution, and manage them with liquibase, etc. Hopefully that helps.

I would also consider upgrading to 10.1 or at least 10.0 (I promise I'm not an undercover salesman), although that will require your users to come with you on the client side (http://resources.arcgis.com/en/help/main/10.1/index.html#//002q000000n8000000) because the newer python APIs are much easier and faster to use (arcpy vs. the GP model), if you do choose to use Python to manage your stuff. (Regardless, either API isn't very well developed and isn't intuitive to code in or fast.)

Good luck.