2

I was working with SQL Server 2008 Spatial Data, but I got a weird problem that the Spatial Index created for the tables doesn’t work when I query them with the view which is created based on this table. Following is the scripts I was using:

 declare @Geometry1 geometry = geometry::STGeomFromText(
'POINT(937767.89433333278 -230404.864666667)', 102003)

exec sp_executesql 
N'SELECT shape FROM view WHERE (@Geometry1.STIntersects(SHAPE)=1);',
 N'@Geometry1 geometry', @Geometry1

I googled a lot and found an workaround at http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/ , but seems like this workaround just works when the queried geometry is point type, for polygons, like the script as following:

declare @Geometry2 geometry = geometry::STGeomFromText(
'POLYGON((-2079214.0399 1392052.275,-2079214.0399 -1156112.025,
1981332.1069 -1156112.025,1981332.1069 1392052.275,
-2079214.0399 1392052.275))', 102003)

exec sp_executesql 
N'SELECT shape FROM view WHERE (@Geometry2.STIntersects(SHAPE)=1);',
 N'@Geometry2 geometry', @Geometry2

The spatial still doesn’t work. Anybody knows how to deal with this situation? Seems like the Microsoft doesn’t give a good instructions about this. Any response will be appreciated.

Improve this question
1
  • 1
    Is there a question here? I don't see one. Commented Nov 4, 2013 at 10:28

1 Answer 1

Reset to default
2

Please use the diagnostics stored procedure sp_help_spatial_geography_index_xml and look at its' output.

This is the primary stored procedure you can use to get an idea of how your geospatial index could be used.

Given that your query is so simple, the @query_sample argument to sp_help_spatial_geography_index_xml can be the same as your @geometry parameter. This will tell you some fairly useful information, such as whether the spatial index needs a full scan to evaluate the question.

Another thing to point out: The dynamic management object view sys.dm_db_missing_index_details explicitly states in the documentation that it does not support spatial indexes, ergo SQL Server does not instrument a lot of intelligence around these indexes.

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

8 Comments

This does not provide an answer to the question. Please write a comment instead.
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
@EricBrown The question was "Anybody knows how to deal with this situation?" This is one of the best ways to deal with the situation. There are truly limited ways to debug spatial queries, as the cost-based optimizer does not surface anything more than an opaque table-valued function called PlanarTesselation in the query plan output. However, in this case, the user is reporting they are not even seeing the index being used. Given it's the geospatial equivalent of bookmark lookup (on a single point), the BEST the user can do is to ask SQL Server for the diagnostics on the index for the sample
I've updated the answer to make it more obvious to people who don't do SQL regularly why this is the correct answer.
@EricBrown Stack Overflow is not Jeopardy. We don't penalize people for how they phrase an answer. Do you work with SQL Server and understand why my answer is correct or not correct, or are you just giving me Jeopardy adjudication? See: stackoverflow.blog/2018/04/26/…
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.