When Geodatabase Design becomes Rocket Science

Some time ago I’ve read two interesting articles by Dave Bouwman about several problems with designing/upgrading ESRI ArcSDE geodatabases and that the whole design process actually should not be as hard as rocket science. Though for some reasons lots of things involved in this actually are cumbersome and in some area really feels like rocket science. One big issue with ArcSDE is the lack of tools DBA’s usually love to do backups, imports, exports, upgrades with. I think ESRI noticed that and eventually invented the Visio UML modelling tool. If you ask me… improvement for the worse.

“In the beginning the Universe was created. This has made a lot of people very angry and been widely regarded as a bad move.” D.N.A.

The whole ArcCatalog schema wizard thing (probably been extremely costly to develop) is not practical for database change management; neither scriptable nor automatable. It might be a convenient way to set up a database from scratch but it’s rather useless for upgrading existing schemas. So finally it is nothing else then the very dark side of ArcCatalog.

If I want to roll out a set of DB schema changes, I’ll release a bunch of SQL files. But what if I want to roll out changes specific to the geodatabase – like adding topological network rules or modifying a feature class? What I need for ArcSDE is a dead easy way to deliver short, comprehensible scripts that contain all this DDL stuff.

Just because I’ve had very good experiences with Apache Ant to handle database change management, I started to develop some Ant task’s for ArcSDE. For example if I want to add a column to a feature class, now I can add a simple task along with my usual Ant build scripts.

  <sde:addcolumn connection="sde.conn" tablename="BUILDINGS">
        <columndef name="BUILD_DATE" type="date" nullable="false"/>
  </sde:addcolumn>

Push all of the capability of ArcSDE down to the database level

Hopefully ESRI will continue the process of pushing all ArcSDE capabilities to the RDBMS, as it already been done with the implementation of the st_geometry type. And thus hopefully someday we’ll be able to do all the DBA tasks on the database level where it belongs.