5

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?

benni_mac_b
  • 8,803
  • 5
  • 39
  • 59
Nenad
  • 419
  • 6
  • 17
  • So, the error message alludes to a spatial index hint. Are you trying to force the optimizer's hand by doing something like "from tbl with (index(idx_spatial))"? – Ben Thul Jun 18 '12 at 19:11
  • Yeah, I'm using "FROM Table WITH (INDEX (spatial_index))". I don't understand this error, why does it work with AND but not with OR. – Nenad Jun 18 '12 at 20:46
  • 2
    I don't know, but the server is obviously angry at your attempts to circumvent what it would normally do. Ditch the index hint: they're typically reserved for when you really know what you're doing (not saying you're a rookie, but it's rare that anyone is smarter than the optimizer). – Ben Thul Jun 18 '12 at 21:01
  • similar? : http://stackoverflow.com/questions/10911473/using-stcrosses-with-a-spatial-index-in-sql-server – AakashM Jun 19 '12 at 08:04
  • No, this is different error: "Could not find required binary spatial method in a condition" – Nenad Jun 19 '12 at 08:22
  • Check on this MSDN post, a similar issue was found and it turned out to be the compatibility level of the database: http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/06c5076a-b41d-4bf1-9dd0-0ce65c5b307a – Kevin Dahl Jul 05 '12 at 20:15
  • Do you have the complete query? You say you are using an index hint? Have you tried to execute the query without the hint? – Mark Kremers Oct 02 '12 at 13:31
  • I had something similar. Best I could figure is the And allowed for a linear type execution plan and the Or did not. I did not say it was an answer - said the best I could figure. – paparazzo Nov 16 '12 at 18:23
  • Doesn't "within" imply "overlap"? – usr Nov 21 '12 at 11:01

1 Answers1

1

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.

Tomas
  • 3,573
  • 2
  • 20
  • 25