Spatial index not used

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

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.

StackExchange Database Administrators Q#59840, answer score: 6