Go to content Go to navigation and search


Current SQL Server Blog Articles



RSS / Atom

Email me


Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

On hinting spatial indexes

Wednesday September 28 2011 at 00:55

Recently, using SQL Server 2008 (not R2), I came across an issue with hinting a SELECT statement with a spatial index. This particular issue is different from the one Bob Beauchem wrote about in his article On hinting spatial indexes and query complexity (in turn he references Isaac Kunen’s Is my spatial index being used?).

The difference with the issue I found is to do with a very simple query that uses a constructed geometry. Here is the original coding of the query:

  2.   FROM dbo.admin_sorted a
  3.   WITH (INDEX (admin_sorted_geom))
  4.  WHERE a.geom.STIntersects(geometry::STLineFromText('LINESTRING(320000,5340000,321000,5341000)',a.geom.STSrid)) = 1;
  5. -- Result
  6. Msg 8635, Level 16, State 8, Line 1
  7. The query processor could NOT produce a query plan FOR a query WITH a spatial INDEX hint.  Reason: The spatial parameter REFERENCES a COLUMN that IS defined below the predicate.  Try removing the INDEX hints OR removing SET FORCEPLAN.

Now, there could not be anything more simple that this query, so why is an error being thrown?

Following a hunch I discovered (I think) that the geometry::STLineFromText() constructor is the culprit. On this basis, I played around with the query and, using a CTE, came up with the following that correctly uses the spatial index.

Note: The reason for the “(SELECT TOP 1…. )” in the CTE is because I want the constructed linestring to have the same SRID as the host table.

  1. WITH sg AS (
  2.   SELECT geometry::STLineFromText('LINESTRING(300000 5200000,500000 5400000)',a.Geom.STSrid) AS geom
  3.     FROM (SELECT TOP 1 geom
  4.             FROM  dbo.admin_sorted) a
  5. )
  6. SELECT COUNT(*) AS AdminCount
  7.   FROM sg a,
  8.        dbo.admin_sorted b
  9.   WITH (INDEX (admin_sorted_geom))
  10.  WHERE b.geom.STIntersects(a.geom) = 1;
  11. -- Results
  12. AdminCount
  13. ----------
  14. 101

Here is that part of the actual explanation plan showing the use of the spatial index.

Perhaps this is no longer an issue in R2 or Denali.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter