SDE 9.2's ST_GEOMETRY: Part Zero

It was over a year ago I sipped the ESRI ST_GEOMETRY Kool-Aid. Fiteen months later, things finally worked out where I could try out some small examples. This should be the first in a series of posts. We'll see. In brief, I'm glad I waited a year to try, and more time yet may be in order, depending on what you're trying to do.

Installing SDE 9.2 is easy enough, and creating tables which use the ST_GEOMETRY type is easy, too. You just, you know, do it.

CREATE TABLE THINGS (X INT, SHAPE ST_GEOMETRY)

The critical documentation is called Working with a Geodatabase using SQL. It's easy to find the basic operations.

What's not as easy is a few administrative details. First, setting up spatial references is no picnic. If you load data via ArcGIS, it magically gets taken care of for you. If you're loading it manually, you need to create your own spatial references, including resolution, offsets, and the whole nine yards. I haven't done that yet, and have been happy to let utilities create them. Unfortunately, these utilities create a custom SRID for each dataset I load, even though they have the same coordinate system. Each SRID has its own offsets and grid spacings. I'll have to figure out how to force data to be loaded into existing SRIDs, probably by labeling the column in SDE's metadata. I loaded data using shp2sde, which is certainly a cop out.

Then comes querying data. If you just run and try "select st_astext(shape) from zip", you may get an interesting listener error: ORA-28575: unable to open RPC connection to external procedure agent. It turns out all the ESRI ST_GEOMETRY functions are implemented using an out-of-process procedure call, which the Oracle listener has to be configured to support. It comes this way with normal installs, but somewhere along the line I'd lost mine. Never mind, ESRI has a good article explaining what to do.

Now comes the part where I'm glad I waited a year. Doing any of the most trivial operations, such as the aforementioned "select st_astext(shape)..." resulted in a host of clear nastiness, the most common of which was ORA-28579: network error during callback from external procedure agent. A lot of hunting finally revealed that these bugs were only fixed in SDE SP5. (My money says they weren't all fixed; we'll see.) It turns out SP5 for Oracle 10g R2 was withdrawn a few days ago because of a nasty regression. Now this regression was unrelated to ST_GEOMETRY support and Friday afternoons of playing around with technology don't come around very often, so I was very relieved to find that James Fee had a copy of it still available. That fixed the bugs so I could start doing some real testing.

More details in the next post, but first impressions are that doing some casual spatial joins (e.g. setting a where clause in ArcMap to only show cities which intersect Harris county) seems pleasantly fast. (Update, 2008-May-5: Further investigations reveal this was only fast on small datasets. Spatial joins are slow, too.) Selecting the data as WKB is intolerably and comically slow. This seems to be because the call to functions like st_asbinary is made out of process to Oracle's extproc.exe. I am currently testing on an old single core laptop, so it may be that a dual processor machine would handle more process switches per second, but it probably goes without saying that calling an out-of-process function to convert a few kilobytes of binary data from one form to another is extravagant. If this is the case, then while the spatial indexing and intersection type functions will be immensely useful, there appears to be no future (on Oracle, at least) at all in writing tools which actually go against the WKB representation made possible by SDE.ST_GEOMETRY. Unless you're happy to query less than 1000 rows per second.

SDE, of course, goes "under the covers" when rendering data from ST_GEOMETRY columns, and so its plenty fast. Something to ponder.

Until next time.