Spatial index not used

Question

I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting in bad performance.

Example script:

--Create the table
create table Location(
    LocationID int not null identity(1,1) primary key,
    LocationPoint geometry
)


--add records
declare @counter int =0
WHILE @counter<150000
BEGIN
    set nocount on
    --select 
    set @counter =@counter +1
    declare @RandomLocation geometry=geometry::Point(RAND() *1000, RAND() *1000, 0)     
    insert into Location(LocationPoint) values  (@RandomLocation)   
END

--create index
CREATE SPATIAL INDEX SPATIAL_StructureBE ON dbo.Location(LocationPoint)
USING GEOMETRY_GRID WITH ( 
    BOUNDING_BOX  = (xmin  = 0.0, ymin  = 0.0, xmax  = 1000, ymax  = 1000), 
    GRIDS = ( LEVEL_1  = MEDIUM, LEVEL_2  = MEDIUM, LEVEL_3  = MEDIUM, LEVEL_4  = MEDIUM),
    CELLS_PER_OBJECT  = 16, 
    STATISTICS_NORECOMPUTE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON)

--Search, this query should use the index but it doesn't
declare @CurrentLocation geometry=geometry::Point(24,50, 0)
select top 1 *
from Location 
order by LocationPoint.STDistance(@CurrentLocation) asc

Answer 1

Correct. Spatial indexes don't get leveraged in that situation, sadly.

Spatial indexes provide a set of grids, allow the system to identify geometries (or geographies) that overlap these grids.

Your best bet is to set a threshold of acceptable closeness, and try that, using something like STBuffer. STIntersects works well, and you can increase this threshold if nothing is found (eg, using a second OUTER APPLY if nothing is found.

Edit: They do now! Check my post on it... http://blogs.lobsterpot.com.au/2014/08/14/sql-spatial-getting-nearest-calculations-working-properly/

In summary, to get your index to be used, you need to make sure that your ORDER BY value can't be NULL - just add a WHERE...IS NOT NULL clause and it should work.

Answer 2

As you can see here: http://technet.microsoft.com/en-us/library/bb895373%28v=sql.105%29.aspx, not only is the number of methods that can use a spatial index limited, it can only be used in a WHERE or JOIN ON clause.

You're trying to use the STDistance method in an ORDER BY clause, where index usage is not supported.

EDIT:

You might get away with creating a distances table, joining on that (thus - theoretically - using the spatial index) and then ordering over the joined table's columns.

Something a'la ... INNER JOIN [distances] ON LocationPoint.STDistance(@CurrentLocation)<[distances].[maxdistance] AND NOT(LocationPoint.STDistance(@CurrentLocation)<[distances].[mindistance])

(Using 2 columns, so you don't necessarily have to scale linear)