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