ESRI 2008 UC: SQL Server 2008 and ArcGIS

I attended the Intro and Advanced sessions of Administration for SQL Server.  The intro was a pretty straight forward topic of installation and configuration for setting up ArcSDE with SQL Server.

The interesting points were how ArcSDE integrates with SQL Server 2008.  As I mentioned in my talk on SQL Server 2008 at the IE .Net User Group storing spatial data in a SQL database is not a new thing, just new to SQL Server.  ArcGIS already has implementations to take advantage of server level spatial handling in Oracle, DB2 and possibly others.  Only in 9.3 are SQL Server 2008 and PosgresSQL now supported.d

Since SQL Server 2008 hasn’t been officially released yet (all info still points to Q3 2008, which leaves less than 2 more months) ESRI is working with Microsoft on the latest release candidate version.  While you can play with it ESRI of course highly discourages using ArcSDE against SQL 2008 until the official release.  At that time ESRI will certify ArcSDE against SQL 2008 and will most likely release SP1 to deal with any changes since the 9.3 release.

That being said, what exactly is ArcSDE offloading to SQL Server?

Remember that the ArcGIS geodatabase is a very proprietary data model, allowing you to store not only geographic items (features) but also tables, topology rules, data access rules, locking schemes, business rules, etc.  SQL Server 2008 is still at heart simply a data storage platform.  This means that the bulk of the management of your GIS data will still be handled by ArcSDE. 

SQL Server now includes spatial features including two new data types (GEOGRAPHY and GEOMETRY) as well as a whole host of functions to manipulate these data types with.  The data is indexed using new Spatial Indexes on the server.  These are the things that ArcSDE will take advantage of.

ArcSDE will store geographic data in the new GEOGRAPHY and GEOMETRY data types.  This allows SQL to manage it’s own spatial indexes on the data.  Doing this also allows ArcSDE to take advantage of the built-in spatial functions for simple requests.

ArcSDE will take advantage of the indexing schemes built in to SQL Server 2008, however, all the settings are left to Microsoft defaults.  ESRI claims this is because every user’s data is different.  While this is true, I got the impression that it’s more because the indexing scheme is so new that everyone is still coming to terms on it’s quality and how to actually manually maintain it.

Now that you can store your data in the underlying database, several questions come to mind:

• Can I simply enter my own geographic data in SQL Server and use these in ArcGIS?
  • Yes you can, however, you must register it with ArcSDE before you can integrate it with other ArcSDE data.  If you never plan to process this data using ArcGIS tools then you never have to register it.  However, that doesn’t seem like a likely scenario since you imported the data into your geodatabase.
    To register the data use the sdelayer tool.  This sets up all the geodatabase data and relationships so that the features can be managed using standard ArcGIS tools.
• Can I manipulate ArcGIS data directly in SQL?
• Yes!  You can hit the data directly using the built-in spatial methods.  This is pretty awesome for automated tasks that might fire at scheduled moments or for triggers. 
• For instance, at our school district our Student Information System (SIS) has a field for each student storing what their school of residence is.  Since students may request to attend another school in our district, often it is important to know what school they normally would have gone to.  Currently we autopopulate this data do this once a week or so.  This is a manual process by using ArcGIS Desktop to simply join our students against our school polygons, output the results to a table and import these values back into the SIS.  Once our GIS data is in ArcSDE on SQL 2008 I will be able to setup a trigger on the student record causing it to use the ST_INTERSECT method to find this data on the fly.  Sweet! 🙂
• However, (you knew there had to be a however right?) you MUST BE VERY CAREFUL.  As with manipulating most SQL data you must know exactly what you’re doing.  If you are inserting new objects you must find what the next ObjectID should be.  If you are working with versioned features it’s more complicated as you have to work with versioned views within a versioning session.  SQL 2008 has no concept of these so ArcSDE installed several utility stored procedures into SQL Server that you will use to manage this process.
• What about the SRID?
• If you are new to SQL Server 2008 you may have noticed this weird SRID value that is attached to every spatial value.  This is the equivalent of the Spatial Reference used in ArcGIS such as NAD State Plain 1984 or WGS.  However, the SQL Server SRID corresponds to the EPSD code (European Petroleum Survey Group).  This is a table of spatial references all attached to a unique ID. 
  This is required when registering your imported data with ArcGIS using the sdelayer command.
  What’s different is ArcGIS’s SRID’s do not correspond to the EPSD code.  So what do you do?  ArcGIS stores it’s SRID as auth_SRID.  Here is an example of how to join the ArcGIS srid_auth against the EPSD reference table to find out which SRID to map to:
  select sr.auth_srid from SDE_layers l join SDE_spatial_references sr on l.srid = sr.srid where l.table_name = ‘buildings’ and l.owner = ‘dbo’
  This command joins the SDE_layers table against the SDE_spatial_references table and looks for the layer named buildings owned by dbo.

All in all some really great stuff!  I can’t wait to play with this.

I guess my one complaint is that while you can request an ArcGIS Desktop eval cd from the ESRI website I don’t know that you can get some type of an eval version of ArcGIS Server.