I have a geography type column. I created a spatial index, but it is not 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 as follows:
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
Grid densities at different levels are not intended to be intentionally installed on a medium. It doesn't matter why I install them. If I look at the estimated execution plan, the index will not be 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 was unable to produce a query plan for the query with a spatial index pointer. Reason: Spatial indexes do not support the comparator specified in the predicate
SQL Server 2008 (100).
.