CODEWITHSUNDEEP
sql-servert-sqlgeospatialspatial-index
Nenad
Nenad

Reputation: 419

Error when using Spatial Index in SQL Server

I use Spatial Index and when I try to execute WHERE statement:

WHERE T.GEOMETRY.STIntersects(O.GEOMETRY) = 1 AND T.GEOMETRY.STTouches(O.GEOMETRY) = 0

It works fine, but when I try to execute with OR:

WHERE T.GEOMETRY.STOverlaps(O.GEOMETRY) = 1 OR T.GEOMETRY.STWithin(O.GEOMETRY) = 1

I get this error:

The query processor could not produce a query plan for a query with a spatial index hint. Reason: Could not find required binary spatial method in a condition. Try removing the index hints or removing SET FORCEPLAN.

Any help, what is a problem in this statement?

Upvotes: 5

Views: 1720

Answers (2)

Damian Socha
Damian Socha

Reputation: 1

Important is that 1 value need to be on the right side of the equation. Otherwise spatial index is not used. https://learn.microsoft.com/en-us/sql/relational-databases/spatial/spatial-indexes-overview?view=sql-server-ver16#queries-that-use-spatial-indexes

Upvotes: 0

Tomas
Tomas

Reputation: 3643

A general solution for issues with OR queries ( typically performance) is to separate them and use UNION ALL between then. This would typically be done in a sub-query, or a Common table expression.

If you could post more of your query, and preferably the relevant table schema, then I can update my answer with a more detailed answer.

Upvotes: 1

Related Questions