3

I have a column of type geography. I have created a a spatial index, but its not being used:

declare @geog geography 
select @geog = 'POINT (18.12 -33.2)' 

select  *
from  dbo.product  WITH(INDEX(IX_Spatial))  
where 
@geog.STDistance(GeoLocation) > 1000

The index is created like this:

 CREATE SPATIAL INDEX [IX_Spatial] ON [dbo].[Product] 
(
 [GeoLocation]
)USING  GEOGRAPHY_GRID 
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 1024, 
PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

The values for grid density at the various levels are for not deliberately set to medium. It makes no difference what I set them to, If I view the estimated execution plan, the index is not used.

[http://blogs.msdn.com/b/isaac/archive/2008/08/29/is-my-spatial-index-being-used.aspx][1]

If I try to add a hint to the query optimizer

declare @geog geography 
select @geog = 'POINT (18.12 -33.2)' 

select  *
from  dbo.product  WITH(INDEX(IX_Spatial))  
where 
@geog.STDistance(GeoLocation) > 1000

I get this error:

The query processor could not produce a query plan for a query with a spatial index hint. Reason: Spatial indexes do not support the comparator supplied in the predicate

My database is running at SQL Server 2008 (100) compatibility level.

.

1 Answer 1

2

SQL Server creates spatial indexes in the following way: it splits the whole map into several rectangles (tiles) and indexes the number of each tile.

For any given point, it is possible to calculate the numbers of rectangles within a distance but not out of the distance (there can be infinitely many).

Sign up to request clarification or add additional context in comments.

1 Comment

Which explains why using less than utilizes the index. Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.