Nov 03 2010

Getting OGC spatial tables back into ESRI ArcSDE on SQL Server 2008 R2

Published by at 1549h under GIS in general,Spatial SQL

Sure, all the OGC-style spatial queries are great stuff, but when an organization already has very many feature classes in an ArcSDE geodatabase, there’s little appeal to making copies of reference data  for the sole sake of spatial queries.  Yet as of ArcSDE 9.3.1, when installed on MS SQL Server 2008 and 2008 R2, the option exists to bypass the classic SDE_BINARY format for geometry features, and instead use SQL Server’s native GEOMETRY format as the default format for new SDE features created in that geodatabase through ArcGIS.

Sounds great!  Less work!  And yet, frequently, it often doesn’t work because valid SDE geometry must still be made OGC-valid.  Three days ago I found myself with a working syntax to apply the (.NET CLR invocation of) MakeValid() method to my existing GIS data, and so I had valid OGC spatial tables available.  However, these OGC tables were missing a couple of things that needed to earn their registration back into the SDE fold.  Things needed, or just good-to-have when invoking sdelayer to register an OGC table include:

  1. A defined primary key field with its own clustered index
  2. OGC Spatial indexes (up to four levels available in OGC tables, and three in ArcSDE), with a name to pass along to SDE
  3. A numeric expression of the geometry’s extent, as bounding rectangle limits
  4. A pointer to the geometry’s projection “srid”  in the SDE_spatial_references table, linking it to real SRID as “auth_srid”
  5. A friendly 63-character name to describe the spatial features
  6. A known instance of ArcSDE, such as port ‘5151‘ or DirectConnect ‘sde:sqlserver:servername
  7. A resolvable name or IP address for the ArcSDE server
  8. The name of the ArcSDE database on the server
  9. Login credentials for the database under the ArcSDE instance, most useful with CREATE privileges

One last detail: you’ll likely need to have the administrative parts of ArcSDE installed on your workstation.  This means installing ArcSDE 10, but not configuring it to run as a server locally.  Mercifully, this type of install does not require access to an ArcGIS Server license.  Of course, you’ll use it to perform actions a server instance where that license was required.  But after that install, your workstation should be able to run command line administrative tasks on a remote SDE server, often with either the ArcSDE monitor ‘sdemon’ or the Feature Class utilities of ‘sdelayer’, each of which has very many options.

1) Update the OGC table to ensure you have the correct SRID on every Geometry feature

UPDATE [SpatialTesting].user.PARCEL_OGC_LI
	SET Shape.STSrid = 2872

2) Define the primary key in your OGC table using SQL Server Management Studio (SSMS), and doing so will create a cluster index for you. In SSMS 2008 R2 this could mean a right-click on the OGC table name, selecting the Design view (second choice), then highlighting the OBJECTID attribute row by clicking its box along the left, right-click and choose Set Primary Key.  Save the change, or close the view and accept to save the change.  This should provide the “gold key” icon for OBJECTID as a Primary key, add an entry in the table’s Keys folder, and list an index named like “PK_your_table name  (Clustered)” in the Indexes folder.
Please note that you can’t skip this step and succeed in building a spatial index.   The spatial index appears to require a clustered index on a defined Primary Key.

3) Create the spatial indexes for the OGC table by right-clicking the table’s Indexes folder and choosing “New Index…” option.
With the General page chosen in “Select a page”, give the index a name (e.g. “spx_50”) and choose Index type: Spatial, then click “Add…”
In the “Select Columns from” popup, hopefully you will only  have one spatial column’s row to choose from (e.g. Shape).
Check its box and click OK.
Then in “Select a page”, choose Spatial, and here you will enter bounding coordinates for the OGC Geometry extents.
(e.g. 5816131, 2125631, 6028424, 2312384 as X-min, Y-min, X-max, Y-max)
For Geometry, ensure that the “Geometry grid” Tessellation Scheme remains chosen, and 16 Cells Per Object seems OK thus far.
While SQL Server 2008 offers four levels of spatial index grids, it only has qualitative labels for its options; by contrast ESRI file or SDE geodatabases have three grid levels, but one can specify precise index grid scales.
Given the prompts in the interface, I have typically understood Level 1 / top-level grid to be the coarsest, and chosen “Low”, then at Level 2 chosen “Medium”, and for both Level 3 and Level 4, chosen “High”.   Once you’ve chosen, click OK and wait while the index is built.

4) Once the spatial index has been built on the OGC spatial table, it should be ready to register with SDE and return it to ArcGIS service. The example below (less credentials) is what worked for us with line feature class with over 275,000 features.
Just open up a ‘CMD’ command-prompt window  and invoke the ‘sdelayer’ command with options along these lines:

sdelayer -o register -l PARCEL_OGC_LI,SHAPE -e l -t GEOMETRY -C OBJECTID,SDE
 -E 5816131,2125631,6028424,2312384 -R 2 -S "OGC Version of 2010 10 Parcels"
 -i sde:sqlserver:sqlgisdev -s SQLGISDEV -D SpatialTesting -u <user> -p <password>

And that should do it.

Another bit of syntax that I feel more comfortable with is making use of “UNION ALL” to chain queries together, so that they are easier to visualize in SSMS as overlays.

select Shape.STCentroid().STBuffer(10000) from SpatialTesting.bquinn.COUNTYBNDY_PG_OGC
UNION ALL
select Shape.STExteriorRing() from SpatialTesting.bquinn.COUNTYBNDY_PG_OGC

Takes our county boundary polygon, finds its centroid, buffers that centroid point out 10k feet, and then overlays that centroid blob on the outline of the county polygon, garnered from the poly’s exterior ring. Sounds simple enough, and the pasted code worked.  But 10 days ago I would have been very hard pressed to come up with that syntax!

No responses yet

Trackback URI | Comments RSS

Leave a Reply

You must be logged in to post a comment.