Spatial database, strange index behavior

Question

So, my database has a Table with a Id of type BigInt and a Geometry of type Geometry. The Geometry field has a spatial index called idx_Geometry

The following query works as expected using the index:

DECLARE @Geometry geometry
SET @Geometry = geometry::Parse('Polygon((300000 300000, 300000 325000, 325000 325000, 325000 300000, 300000 300000))')
SELECT Id FROM [Database].[dbo].[Table] WITH(index(idx_Geometry)) WHERE Geometry.STIntersects(@Geometry) = 1

However when I try the query

DECLARE @Geometry geometry
SET @Geometry = geometry::Parse('Polygon((300000 300000, 300000 325000, 325000 325000, 325000 300000, 300000 300000))')
SELECT a.Id FROM [Database].[dbo].[Table] a with(Index(idx_Geometry)) WHERE a.Geometry.STIntersects(@Geometry) = 0

I get the error message:

The query processor could not produce a query plan for a query with a spatial index hint. Reason: Spatial indexes do not support the comparand supplied in the predicate. Try removing the index hints or removing SET FORCEPLAN.

As far as I'm concerned those two queries are basically equivalent. Can anyone explain why this is happening and how (or if) I can get the index to work with the second query?

Thanks

Edit: Just noticed the second one was = 0, not =1 in the where clause, anyone know why the index can't be used with = 0? (the 2nd query works with = 1)

Edit 2: Just a update of what works and what doesn't

DECLARE @Geometry geometry
SET @Geometry = geometry::Parse('Polygon((300000 300000, 300000 325000, 325000 325000, 325000 300000, 300000 300000))')

--Works
SELECT Id FROM [RoadRoutingDatabase].[dbo].[Node] WITH(index(idx_Geometry)) WHERE Geometry.STIntersects(@Geometry) = 1
SELECT a.Id FROM [RoadRoutingDatabase].[dbo].[Node] a with(Index(idx_Geometry)) WHERE a.Geometry.STIntersects(@Geometry) = 1 

--Gives Error Message
SELECT Id FROM [RoadRoutingDatabase].[dbo].[Node] WITH(index(idx_Geometry)) WHERE Geometry.STIntersects(@Geometry) = 0
SELECT a.Id FROM [RoadRoutingDatabase].[dbo].[Node] a with(Index(idx_Geometry)) WHERE a.Geometry.STIntersects(@Geometry) = 0

--Works but doesn't use Index
SELECT Id FROM [RoadRoutingDatabase].[dbo].[Node] WHERE Geometry.STIntersects(@Geometry) = 0
SELECT a.Id FROM [RoadRoutingDatabase].[dbo].[Node] a WHERE a.Geometry.STIntersects(@Geometry) = 0

Edit 3: I have found a work around for my issue with a left join and null check but I am still curious about why you can't use a index on a false intersect if anyone can enlighten me

stevehem · Accepted Answer · 2011-02-25 16:39:17Z

1

There is no technical reason the spatial index could not support this query, however the query plan generated would be essentially the same as doing it yourself with a left anti semi join. Supporting this was considered, but doing so involves additional changes to the Query Optimizer in order to match this predicate and generate the correct query plan.

So given this is not a common query shape and it is still relatively easy to write the query to use the index yourself, this pattern was not included in the list of supported predicates for spatial indexes.

answered Feb 25, 2011 at 16:39

stevehem

6713 silver badges6 bronze badges

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

1 Comment

Manatherin Over a year ago

I don't understand though, isn't the Intersect query basically the same just the check of the query result different, why does that effect whether or not the hint can be used

Collectives™ on Stack Overflow

Spatial database, strange index behavior

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related