I just sat through a session titled “Working With the Geodatabase Effectively Using SQL.” It was heavily focused on ST_GEOMETRY. That’s pretty much what they want us to use. Interestingly, though, there won’t be ST_GEOMETRY in SQL Server 2008. They’ve added it to PostgreSQL (more on that later) but not SQL Server. Seems a little strange to me.
There was a brief discussion of what PostgreSQL support will look like. There are no big surprises, just a little more detail. As I said, ESRI is implementing ST_GEOMETRY and also fully supporting the PostGIS spatial type. ST_GEOMETRY will be the default spatial type for ArcGIS but you will be able to use the PostGIS type via configuration keyword. The differences are:
1. ST_GEOMETRY types and functions will reside in the “sde” schema (I guess that’s more efficient than call it the “arcgis_server_enterprise_basic” schema). PostGIS types and functions will reside in the public schema by default as is currently the case.
2. The ST_GEOMETRY implementation will be consistent with those on Oracle, DB2 and Informix so, if you’re developing an app that is cross-platform (RDBMS-wise), you may want to use ST_GEOMETRY. It was said that the PostGIS spatial type only resides on PostgreSQL (we’ll dodge the obvious OGC-compliance piece which makes that statement somewhat shaky).
3. The ST_GEOMETRY data type supports parametric geometry such as arcs and circles and the like whereas the PostGIS type does not. (Can you believe I’ve never looked into that? If that’s not correct, please feel free to correct me.) Of course, the REST API doesn’t support those and converts them to simple geometry…
4. The ST_GEOMETRY spatial data is stored in ESRI’s compressed shape format which is sent directly to clients without translation. PostGIS stores in WKB format, which ArcGIS must first convert to compressed shape before sending so there will be something of a performance hit. Interpret that however you want, I’m just reporting.
ST_GEOMETRY will use the PostGIS GIST spatial indexing framework but will implement an RTREE strategy through its own set of geometry operators. Additionally, ST_GEOMETRY columns can be defined within a CREATE TABLE statement compared to the use of the AddGeometryColumn function in PostGIS. Regardless of the spatial type used, tables created through SQL will still need to be registered with the geodatabase through the “sdelayer -o register” process in order to be used in ArcGIS. I’ve done this before with Oracle Spatial as a means to let Oracle do all of the heavy lifting. I would imagine the same strategy could be used on PostgreSQL if you want PostGIS to do all of the management of the data.
As I said, there’s nothing earth-shattering there. It’s pretty much what I expected but now there are more details.
Paul,
As always, your inputs are most appreciated. I am not surprised to see the ST_GEOMETRY implementation but I find myself more interested in seeing how much I can really do with the PostGIS type.
With regard to the standard, you and I are in agreement. The comment about using ST_GEOMETRY if you’re targeting multiple platforms was actually their words although, now that I look at it again, I didn’t make that clear. I chuckled at the idea of using a proprietary type to achieve cross platform consistency.
Thanks again. Your comments are great information.
Errata:
1. Is correct, and nice to see that ESRI has done what is probably the right thing and installed in their own schema. This will require them to alter the search path for SQL clients to find their functions, which won’t be a problem when the client is an ESRI product, but might be confusing to users connecting to the database directly with psql or pgdamin.
2. The point of the standard is that you don’t need one vendor in order to get cross-database compatibility. When we heard ESRI was interested in PostgreSQL, we implemented all the function in their existing SDE/SQL documentation, so they would have 100% compliant SQL available if they chose to use PostGIS.
3. PostGIS has Arcs, but the utility of them is as fraught for us as they. If you have to linearize them to use them… the point is?
4. There are a couple SE_ functions in the ESRI implementation that return their own binary serialization instead of WKB. If it really is a Huge Performance Win, then moving to compressed arrays in PostGIS might be worth while. I’ve been thinking that since the disk array stubbornly remains the slowest part of the server hardware stack, that this might be the next obvious target of optimization. Note that if ESRI failed to be terse in their header definitions, they could blow away all the advantages of their compression schemes quite quickly… but we’ll never know that.
5. The ST_GEOMETRY will use the PostgreSQL GiST framework, the same one that PostGIS uses, but their index binding is all their own. The GiST framework is a PostgreSQL infrastructure that extensions, like PostGIS and ST_GEOMETRY all use for indexing.
6. You can create PostGIS geometry table with a simple CREATE TABLE, the only penalty is that you don’t get your GEOMETRY_COLUMNS table auto-updated with the right metadata. ESRI simply forces you to fill in their metadata tables by hand, a great leap forward. (The metadata tables are a PITA, I wish there was a nicer solution.)