Oracle spatial and the web mercator spatial reference system
We have been attempting to streamline the way we store and process spatial data in Oracle. Normally, we would create our spatial tables as FeatureClasses from ArcCatalog and create plain old Oracle tables for our business/attribute data that would be joined with the spatial data in the FeatureClasses through a shared key field to create a SDE spatial view. This works just fine, but there are some drawbacks to this approach
- SDE creates unnecessary fields on the FeatureClass table in Oracle, like the field cad annotation that just don’t need to be there.
- Creating FeatureClasses manually from ArcCatalog is a process that cannot be automated as a part of the build process. Ideally, we like to execute a command from our build scripts that will delete our entire model from the database and run another command to recreate the entire model to start off with a clean slate for testing our every build. These scripts will also be used during the deployment process when the database needs to be recreated in another environment.
- FeatureClasses will need to be edited through ArcObjects (versioned/unversioned) or ArcMap(versioned) which would need an ESRI license on any client machine wanting to edit the spatial data. In a web environment, this would mean a ESRI license on the web server.
We wanted our setup to try and work around some of the drawbacks above. So, we wrote SQL scripts that would
- Create the spatial table
- Insert metadata about the spatial table into the Oracle geometry metadata table USER_SDO_GEOM_METADATA
- Create a spatial index on the table
- Register the Oracle spatial table with ArcSDE as a FeatureClass using the ‘sdelayer -0 register’ command
Here is some sample SQL scripts to perform the first three steps with Oracle
CREATE TABLE customers ( customer_id NUMBER, last_name VARCHAR2(30), first_name VARCHAR2(30), street_address VARCHAR2(40), city VARCHAR2(30), state_province_code VARCHAR2(2), postal_code VARCHAR2(9), cust_geo_location SDO_GEOMETRY); INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('CUSTOMERS', 'CUST_GEO_LOCATION', SDO_DIM_ARRAY (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5), SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)), 8307); CREATE INDEX customers_sidx ON customers(cust_geo_location) INDEXTYPE IS mdsys.spatial_index;
The sample sde command to register the table as a multi-polygon FeatureClass with ArcSDE specifying the bounds
sdelayer -o register -l CUSTOMERS,CUST_GEO_LOCATION -e a+M -C CUSTOMER_ID -i sde:oracle11g -s SERVER_NAME -u XXX -p YYY@orcl -t SDO_GEOMETRY -P High -x -180,-90,11132000
As you can see from the commands above the spatial table is registered with Oracle to have an SRID (spatial reference id) of 8307 which denotes the WGS 84 Geographic Coordinate Systems. Oracle maintains its own CS_SRS table where it maintains a list of Oracle supported spatial reference systems. The SRID specified when adding the geometry metadata with Oracle is expected to be present in the CS_SRS table, else Oracle will insert the metadata into its tables. The bounds specified when registering the spatial table as a FeatureClass with ArcSDE also reflects the WGS 84 GCS.
So far so good. The above solution works like a charm. Now that the ArcGIS Online base maps have moved to the Web Mercator projection system used by google and bing, we also wanted to maintain our data in Web Mercator so that the GIS server doesn’t have to reproject the data when rendering maps and also so that we could serve out spatial data as GeoJSON/ArcJSON from our custom web services to consumed by web clients. Here is where trouble started. Oracle does not have a SRID for the web mercator projection system. We ran through the CS_SRS table to check for it maintained under a different id but with no luck. The process of registering a new SRID with Oracle is not documented anywhere as far as I can tell. Apparently, it is not as simple as adding an entry into the CS_SRS table which we tried unsuccessfully.
So, to work around this issue, we registered our spatial table with the Oracle geometry metadata table without an SRID. So, Oracle thinks the SRID for the spatial table in NULL. This becomes a problem when we try to insert geometries with ESRI’s SRID 102113 into the spatial table. Oracle doesn’t let us insert geometries into the spatial table whose is not NULL to match the entry in its geometry metadata tables. So, we are forced to insert geometries with a NULL SRID into the table. But ArcSDE needs to know that the spatial table is in the Web Mercator projection system. To do this, we registered the spatial table using the ‘sdelayer -o register’ specifying the projection file “WGS 1984 Web Mercator (Auxiliary Sphere).prj” from ESRI. See the sample below
sdelayer -o register -l CUSTOMERS,CUST_GEO_LOCATION -e a+M -C CUSTOMER_ID -i sde:oracle11g -s SERVER_NAME -u XXX -p YYY@orcl -t SDO_GEOMETRY -P High -G file=”C:\TEMP\WGS 1984 Web Mercator (Auxiliary Sphere).prj”
The insert statement for the Oracle geometry metadata tables look like this
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO)
VALUES (‘CUSTOMERS’, ‘CUST_GEO_LOCATION’,
(SDO_DIM_ELEMENT(‘X’, -20037700, 20037700, 0.1),
SDO_DIM_ELEMENT(‘Y’, -20037700, 20037700, 0.1)));
This is ugly, but it works. We were able to view and edit the data in ArcMap just fine.
Here are some more things discovered along the way
- Oracle makes all the table names and the field names all upper case by default even when you specify the names in lower case in the SQL statements. We can force Oracle to use lower cases alphabets in the table/field names by specifying the table/field names in the SQL statements by enclosing them in quotes.
- If you use the technique above to use table/field names in lower case for the spatial tables, you will not be able to register the geometry metadata for the table with Oracle. This is because Oracle expects the spatial table/field name to be in all upper case for insertion into the geometry metadata table. This is just a crazy crazy thing and i can’t imagine this requirement being intentional.
- Oracle spatial SQL syntax is ugly. Very ugly. They can definitely learn from the sweet SQL syntax in MS SQL SERVER 2008.
- The free version of Oracle called Oracle XE does support the spatial data types. That is, in Oracle XE we can store columns whose data type is MDSYS.SDO_GEOMETRY. Oracle XE also allows us to perform some spatial operations on these spatial columns but not all spatial operations that are available in the enterprise edition of Oracle. The spatial features that are available in the XE edition is not documented anywhere as far as I can tell. Oracle spatial extensions bring more spatial features to Oracle enterprise edition like raster support etc.
- The MDSYS.Oracle ST_GEOMETRY is different from the ESRI ST_GEOMETRY and is a wrapper around MDSYS.SDO_GEOMETRY
If you are aware of a better way to do things with Oracle and SDE for the web mercator projection, please let me know, I am all ears. I hope this post save some other poor soul some pain and suffering.