Using Spatial Index

Question

I want to learn how to use Spatial Index in SQL SERVER 2012. I have some very time-consuming query so I need it.

I would be very grateful for some example and explanation.

Thanks!

Answer 1

I presented a session at the SQLBits conference a while ago that you might find helpful. You can still view the video and slidedeck online at: http://sqlbits.com/Sessions/Event5/Creating_High_Performance_Spatial_Databases

In a nutshell:

• Spatial indexes are totally different to clustered and non-clustered indexes in SQL Server.
• They work by providing a "primary filter" for a spatial query - the spatial index uses a grid to select an approximate set of possible candidate results, but which may include false positives.
• A secondary filter then eliminates the false positives to get the true result set for the query.
• The key to getting a fast-performing spatial query is to create an index that is accurate (i.e. few false positives) but small (i.e. not too many records to search) so that most of the work is done by the primary filter, and not the more expensive secondary filter.
• You can create a spatial index on a column of geometry/geography data using SQL Server Management Studio, or using a CREATE INDEX T-SQL statement. However, that index may not always be automatically used in queries. To check whether a spatial index is being used, look at the query execution plan for a Clustered Index Seek (Spatial) item.
• To make sure that a spatial index is used to fulfil a query, you can add an explicit index hint by including WITH(index(YourSpatialIndex)) following the table name in your SELECT statement.

There are several spatial-index specific settings that can affect the performance of the primary filter. To start with, I'd recommend that you stick to the default, which is MEDIUM resolution at all four levels of grids, and 16 cells per object. If you're using the geometry datatype, make sure you set a BOUNDING_BOX that encompasses the whole of your dataset (for geography, spatial indexes always cover the entire globe so this is not necessary). Then, try tweaking one setting at a time and measure the performance increase/decrease.

<plug>There's a chapter dedicated to spatial indexing and improving performance of spatial queries in the upcoming "Pro Spatial with SQL Server 2012"</plug> - http://www.amazon.com/Pro-Spatial-SQL-Server-2012/dp/1430234911/ref=sr_1_3?ie=UTF8&qid=1334310547&sr=8-3 (full disclaimer - I'm the author)