Update All Spatial Index Extents in One Query – SQL Server

indexspatialsql serversql server 2014

I work for a county government and probably 95% of the data in the GIS database (running on Microsoft SQL Server 2014) is within the county area.

I've noticed that in some of the data the extent of the spatial indices is too large and in a few cases too small, which is causing queries to run slower than they should.

Question

Is there a way I can run down all the spatial indices and update the extent? I have the X/Y min/max for the minimum bounding rectangle of the county plus a 2 mile buffer already to plug in.

In ArcGIS there is a function to rebuild a spatial index but it doesn't alter the extent of the index. I see in the docs that I have to create a new index with the updated properties with the option of dropping the old index that has the same name. Not sure how to code this out.

Best Answer

This is generally the way that I tackle rebuilding spatial indexes in a consistent way.

This assumes that your geometries are all in the same projection and have already been indexed. It will exclude the usual ESRI Geodatabase System tables.

I avoid having ArcGIS create or maintain indexes. They never seem to get nice extents on the projections that I use.

declare @sqlCmd nvarchar(4000)

declare cSI cursor fast_forward for 
    select 'CREATE SPATIAL INDEX ' + quotename(i.name) + ' ON ' + 
            quotename(sc.name) + '.' + quotename(t.name) + '(' +  quotename(c.name)  + ') ' + 
            'USING GEOMETRY_AUTO_GRID WITH ' +
            '(BOUNDING_BOX = ( xmin=950000, ymin=4500000, xmax=3500000, ymax=7000000 ), ' + -- Alter to suit
            'CELLS_PER_OBJECT = 1000, PAD_INDEX  = ON, DROP_EXISTING = ON)' sqlcmd
    from sys.tables t
        inner join sys.schemas sc on t.schema_id = sc.schema_id
        inner join sys.indexes i on t.object_id = i.object_id
        inner join sys.index_columns ic on i.index_id = ic.index_id and ic.object_id = t.object_id
        inner join sys.columns c on ic.column_id = c.column_id and t.object_id = c.object_id
        inner join sys.spatial_index_tessellations s on i.index_id = s.index_id and i.object_id = s.object_id
    where i.type_desc = 'SPATIAL'  -- spatial indexes
        and (t.name not like 'GDB_%' and t.name not like 'SDE_%') -- exclude ESRI system tables

open cSI
fetch from cSI into @sqlCmd

while @@fetch_status = 0
begin
    print @sqlCmd
    exec(@sqlCMD)
    fetch from cSI into @sqlCmd
end

close cSI
deallocate cSI
Related Question