Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Not sure how finely you need to order location distances, if it doesn't have to be sorted 100% accurately, see my edit below. –  Sascha Rambeaud Feb 28 at 11:08

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.