Spatial Index Tuning Scripts

I have posted some of the scripts I used when performing the analysis for my previous post on Spatial Index Tuning in SQL Server


Density Analysis Script


This script was used for analysing the maximum spatial density of features in the Geography based data set. This was done to get an indication of the granularity of features compared to the size of the data set in order to make a decision on the granularity of the spatial index that would be created. As discussed in my previous article the index for the Geographic data could not be defined

The only difference between this script and the Geometry based test script was that the instance of the grid cell polygon was created using geometry::STPolyFromText, supplying 0 as the SRID.

This script can take a long time to execute on a table with lots of rows, or complicated shapes (or a combination of both), so it is best to apply a spatial index of some sort before executing this script. You will notice the script is using an index hint to get the query engine to use the spatial index.

declare @xmin decimal(19,6)
declare @ymin decimal(19,6)
declare @xmax decimal(19,6)
declare @ymax decimal(19,6)

-- Define the data extent
set @xmin = 137.9946
set @ymin = -29.178
set @xmax = 153.5518
set @ymax = -9.142

declare @iterations int
declare @iterationRow int
declare @iterationColumn int
declare @cellWidth decimal(19,6)
declare @cellHeight decimal(19,6)
declare @count int
declare @maxCount int

declare @cellXmin decimal(19,6)
declare @cellYmin decimal(19,6)
declare @cellXmax decimal(19,6)
declare @cellYmax decimal(19,6)

-- The number of rows/columns in the grid of cells that will be analysed
set @iterations = 20

set @iterationRow = 0
set @iterationColumn = 0

set @count = 0
set @maxCount = 0

-- Calculate the size of each grid cell (note that these will be rectangular if the data extent is rectangular)
set @cellWidth = abs((@xmax - @xmin) / @iterations)
set @cellHeight = abs(@ymax - @ymin) / @iterations

while @iterationRow < @iterations
begin

    -- Determine the grid cell's y coords from the row we are analysing
    set @cellYmin = (@iterationRow * @cellHeight) + @ymin
    set @cellYmax = ((@iterationRow + 1) * @cellHeight) + @ymin

    set @iterationColumn = 0

    while @iterationColumn < @iterations
    begin
        
        -- Determine the grid cell's x coords from the column we are analysing
        set @cellXmin = (@iterationColumn * @cellWidth) + @xmin
        set @cellXmax = ((@iterationColumn + 1) * @cellWidth) + @xmin   

        -- Select the number of features in the grid cell
        select @count = count(id) from state_1 with(index(IX_SP_state_1)) where geom.STIntersects(geography::STPolyFromText('POLYGON((' + CONVERT(varchar(20), @cellXmin) + ' ' + CONVERT(varchar(20), @cellYmax) + ',' + CONVERT(varchar(20), @cellXmin) + ' ' + CONVERT(varchar(20), @cellYmin) + ',' + CONVERT(varchar(20), @cellXmax) + ' ' + CONVERT(varchar(20), @cellYmin) + ',' + CONVERT(varchar(20), @cellXmax) + ' ' + CONVERT(varchar(20), @cellYmax) + ',' + CONVERT(varchar(20), @cellXmin) + ' ' + CONVERT(varchar(20), @cellYmax) + '))', 4283)) = 1;
                 
        -- If this is the new maximum count, retain it
        if @count > @maxCount set @maxCount = @count
    
        set @iterationColumn = @iterationColumn + 1
    
    end
    
    set @iterationRow = @iterationRow + 1

end

-- Display the maximum number of features found in any grid cell, and relate their relative density/size to the overall extent
print 'Maximum cell records: ' + convert(varchar(10), @maxCount)
print 'Feature size relative to data extent: ' + convert(varchar(20), @maxCount * @iterations * @iterations)

Spatial Selection Test Script


This script was used to test the time taken to execute four separate spatial selections on the Geography based data set. The four selections are executed 100 times and the time then averaged to arrive at a result.

The difference between this script and the Geometry based test script was that the test extents were defined as Geometry variables, and the instance of the Geometry was created using geometry::STPolyFromText, supplying 0 as the SRID.

declare @test1Extent geography
declare @test2Extent geography
declare @test3Extent geography
declare @test4Extent geography

-- Define the selection extents
set @test1Extent = geography::STPolyFromText('POLYGON((153.035517 -27.417948,153.032598 -27.42869,153.049808 -27.431585,153.05161 -27.420271,153.035517 -27.417948))', 4283)
set @test2Extent = geography::STPolyFromText('POLYGON((149.182384 -21.138607,149.181783 -21.14275,149.1867 -21.1433783,149.187362 -21.139408,149.182384 -21.138607))', 4283)
set @test3Extent = geography::STPolyFromText('POLYGON((153.130231 -26.753869,153.129566 -26.756992,153.13199 -26.756609,153.131733 -26.753831,153.130231 -26.753869))', 4283)
set @test4Extent = geography::STPolyFromText('POLYGON((152.753145 -27.611423,152.753885 -27.613172,152.757018 -27.61244,152.755065 -27.610358,152.753145 -27.611423))', 4283)

declare @iterations int
declare @iterationCount int
declare @start datetime
declare @diff decimal
declare @sumTest1 decimal(10,6)
declare @sumTest2 decimal(10,6)
declare @sumTest3 decimal(10,6)
declare @sumTest4 decimal(10,6)
declare @count int

set @iterations = 100
set @iterationCount = 0

set @sumTest1 = 0
set @sumTest2 = 0
set @sumTest3 = 0
set @sumTest4 = 0

while (@iterationCount < @iterations)
begin

set @start = GETDATE()

-- Execute selection 1
select @count = COUNT(id)
from state_1 with (index(IX_SP_state_1))
where geom.STIntersects(@test1Extent) = 1;

set @diff = DATEDIFF(ms, @start, GETDATE())

-- Add the elapsed time to the total for selection 1
set @sumTest1 = @sumTest1 + convert(decimal(10,6),(@diff/1000))

set @start = GETDATE()

-- Execute selection 2
select @count = COUNT(id)
from state_1 with (index(IX_SP_state_1))
where geom.STIntersects(@test2Extent) = 1;

set @diff = DATEDIFF(ms, @start, GETDATE())

-- Add the elapsed time to the total for selection 2
set @sumTest2 = @sumTest2 + convert(decimal(10,6),(@diff/1000))

set @start = GETDATE()

-- Execute selection 3
select @count = COUNT(id)
from state_1 with (index(IX_SP_state_1))
where geom.STIntersects(@test3Extent) = 1;

set @diff = DATEDIFF(ms, @start, GETDATE())

-- Add the elapsed time to the total for selection 3
set @sumTest3 = @sumTest3 + convert(decimal(10,6),(@diff/1000))

set @start = GETDATE()

-- Execute selection 4
select @count = COUNT(id)
from state_1 with (index(IX_SP_state_1))
where geom.STIntersects(@test4Extent) = 1;

set @diff = DATEDIFF(ms, @start, GETDATE())

-- Add the elapsed time to the total for selection 4
set @sumTest4 = @sumTest4 + convert(decimal(10,6),(@diff/1000))

set @iterationCount = @iterationCount + 1

end

-- Calculate the average times
print 'Test 1 Time: ' + convert(varchar(10), convert(decimal(10,6), (@sumTest1/@iterations))) + ' seconds'
print 'Test 2 Time: ' + convert(varchar(10), convert(decimal(10,6), (@sumTest2/@iterations))) + ' seconds'
print 'Test 3 Time: ' + convert(varchar(10), convert(decimal(10,6), (@sumTest3/@iterations))) + ' seconds'
print 'Test 4 Time: ' + convert(varchar(10), convert(decimal(10,6), (@sumTest4/@iterations))) + ' seconds'

Index Tuning


This is the script I executed to examine the spatial index efficiency for the Geography based data set.

The difference between this script and the Geometry based index analysis script was that the extents were defined as Geometry variables, and the instance of the Geometry was created using geometry::STPolyFromText, supplying 0 as the SRID, and the sp_help_spatial_geometry_index stored procedure was used for the Geometry data set.

declare @test1Extent geography
declare @test2Extent geography
declare @test3Extent geography
declare @test4Extent geography

-- Define the selection extents
set @test1Extent = geography::STPolyFromText('POLYGON((153.035517 -27.417948,153.032598 -27.42869,153.049808 -27.431585,153.05161 -27.420271,153.035517 -27.417948))', 4283)
set @test2Extent = geography::STPolyFromText('POLYGON((149.182384 -21.138607,149.181783 -21.14275,149.1867 -21.1433783,149.187362 -21.139408,149.182384 -21.138607))', 4283)
set @test3Extent = geography::STPolyFromText('POLYGON((153.130231 -26.753869,153.129566 -26.756992,153.13199 -26.756609,153.131733 -26.753831,153.130231 -26.753869))', 4283)
set @test4Extent = geography::STPolyFromText('POLYGON((152.753145 -27.611423,152.753885 -27.613172,152.757018 -27.61244,152.755065 -27.610358,152.753145 -27.611423))', 4283)

exec sp_help_spatial_geography_index 'state_1', 'IX_SP_state_1', 1, @test1Extent;
exec sp_help_spatial_geography_index 'state_1', 'IX_SP_state_1', 1, @test2Extent;
exec sp_help_spatial_geography_index 'state_1', 'IX_SP_state_1', 1, @test3Extent;
exec sp_help_spatial_geography_index 'state_1', 'IX_SP_state_1', 1, @test4Extent;