ESRI SDE: ST_GEOMETRY and the Re-Unification of Data Persistence

It looks like ESRI is finally getting serious about letting non-ESRI applications talk to their geodatabases. Whether this is due to angry SAP consultants, the commoditization of 50% of ESRI's software stack due to open source competition, or simple generosity is an interesting question. Never underestimate the importance of angry SAP consultants!

I attended a seminar at the ESRI 2007 Developer Summit this morning devoted entirely to the "SQL API" in SDE 9.2. SDE is settling on deep support for OGC standard geometry objects in all databases that can be so extended: Oracle, DB2, Informix. They have even implemented their own ST_GEOMETRY functions as Oracle extensions (though, interestingly, Oracle already had their own.) They will also, as always, honor SDO_GEOMETRY objects, while presumably holding their nose and making juvenile remarks. The presenter made ominous noises about extending SQL Server with a spatial type also.

Fancy geodatabase functionality (that which ESRI can still get away with charging a lot of money for) obviously gets no special treatment. You still have to live in a world of DCOM happiness to work with topologies, custom feature class extensions, etc. But for the points, lines, and polygons that make up 90% of the real world, this is pretty exciting news.

For years we've had to program with two separate data persistence layers. Since ArcObjects demands such total control of its own spatial data, it has been difficult to use the GeoDatabase for all aspects of an application's data. I mean, why would you use a geodatabase to hold user registrations, study definitions, and log files? The .NET world wants to use the ADO.NET stack, since that's what everyone uses. I want to start my transactions in ADO.NET, do fancy DataSet work, and deal with any geometry information that's relevant for my task. I might have even generated these geometries without ESRI's help!

But in the real world, we had to do clumsy programming-at-arm's-length. One connection for "attribute data", and another for geometries or geoprocessing. Two-phase commits were the rule, and incredible duplication of transaction, search, and update functionality ruined months of our programmers' lives.

But what if we could now write a IDbDataAdapter or even adapt an ORM like NHibernate to automagically read and write geometries as if they were just another column in our tables? After all, all the DB vendors except SDE seem to think this is the case. I think that future is close.

With the right .NET implementation of OGC geometries (and I think there are several, and we have one on our project), and a sensible ADO.NET-compatible layer for dealing with these geometries, we'd have a glorious world of programming without any hard ESRI references in our code!

We can do heavy geoprocessing with ArcObjects code written and deployed on a server, executed via a remote API, like SOAP. We can do all our application logic with the tools we use everywhere else in our application, like ADO.NET. Truly, it would be a beautiful world.

To insert or update data, an in-memory geometry object would contain a ToWkb function, this blob would be placed in a IDbDataParameter and stored via direct SQL insertion using the ST_GEOMETRY.FromWkb(:1) syntax. To read from the database one simply reads using ST_GEOMETRY.AsWkb(xxx.shape) and constructs the in-memory object from the resulting Wkb blob. One would still need to deal with projection issues, if relevant, and that's not trivial, but at least all work is now done on one connection and via one paradigm. Time to go build it!