With the advent of ArcGIS 10.1, you can now direct-connect in a read-only fashion to spatial data on SQL Server. That has worked well and while the read-only aspect means no editing, it also means no locks on the data, which is a problem with File Geodatabases. I’ve found that I can programmatically refresh or rebuild a table in SQL Server and just by panning or zooming have ArcMap redraw it. I’d never be able to leave an FGDB open in ArcMap while trying to do the same.

However, I ran into one problem with really large datasets brought into ArcMap. They couldn’t be exported to FGDB or Shape format without a spatial index on the table in SQL Server. Understandable and really it’s better for performance if a spatial index exists. Since it’s an application that creates the tables and populates them, it fell to the application to also create a spatial index.

I could, of course, have used a parameterized SQL command to do this, but since I’m already using SMO to create a tabular index thought I’d try it for a spatial geometry column. Of course, as I soon realized, there’s a catch…one that almost makes it just as well to use parameterized SQL. But if you like being able to specify things like:

 SpatialIndexType = SpatialIndexType.GeometryAutoGrid 

as needed, then there is some case to be made for mixing SMO with SQL…and mix you have to. Because here’s the catch, you can’t create a spatial index if you don’t already know the bounding box (or envelope) coordinates. The only way I know to do this is via a SQL query to the server first. So here’s a code sample:

var serverConn = new ServerConnection("servername");

serverConn.ConnectTimeout = 180;

// provide appropriate login credentials here

var srv = new Server(serverConn);

Database db = srv.Databases["tablename"];
var tb = db.Tables["tableName"];
try
{
 if (db != null)
 {
 // Perform spatial query to get the bounding box
 var sql = String.Format(@"SELECT
 geometry::EnvelopeAggregate(GEOM).STPointN(1).STX AS MinX,
 geometry::EnvelopeAggregate(GEOM).STPointN(1).STY AS MinY,
 geometry::EnvelopeAggregate(GEOM).STPointN(3).STX AS MaxX,
 geometry::EnvelopeAggregate(GEOM).STPointN(3).STY AS MaxY
FROM {0}", "tableName");

var dataSet = db.ExecuteWithResults(sql);

if ((dataSet != null) && (dataSet.Tables.Count > 0) && (dataSet.Tables[0].Rows.Count > 0))
 {
 var boundingBoxXMin = (Double)dataSet.Tables[0].Rows[0]["MinX"];
 var boundingBoxYMin = (Double)dataSet.Tables[0].Rows[0]["MinY"];
 var boundingBoxXMax = (Double)dataSet.Tables[0].Rows[0]["MaxX"];
 var boundingBoxYMax = (Double)dataSet.Tables[0].Rows[0]["MaxY"];
 //spatial index
 var spatialIndex = new Index(tb, "Spatial" + tableName)
 {
 SpatialIndexType = SpatialIndexType.GeometryAutoGrid,
 BoundingBoxXMax = boundingBoxXMax,
 BoundingBoxXMin = boundingBoxXMin,
 BoundingBoxYMax = boundingBoxYMax,
 BoundingBoxYMin = boundingBoxYMin,
 CellsPerObject = 16,
 PadIndex = false,
 CompactLargeObjects = false,
 DisallowPageLocks = false,
 SortInTempdb = false,
 OnlineIndexOperation = false,
 DisallowRowLocks = false
 };

spatialIndex.IndexedColumns.Add(new IndexedColumn(spatialIndex, "GEOM"));

spatialIndex.Create();

return true;
 }
 }
}
catch (Exception err)
{

Debug.WriteLine(err.message)
}