Fine tuning the geospatial index

Question

I simply converted the SQL Server 2008 database using a lat / long pair to use a new type of geography. I run queries at enterprises that are within a 30 mile radius of :: Point using the STDistance function, for example:

WHERE this_.GeoLocation.STDistance(geography::Point(42.738963, -84.5522, 4326)) <= 48280.32 

Here is the index that I have in the geography column:

 CREATE SPATIAL INDEX IDX_Business_GeoLocation ON Business (GeoLocation) USING GEOGRAPHY_GRID WITH ( GRIDS = ( LEVEL_1 = LOW, LEVEL_2 = LOW, LEVEL_3 = LOW, LEVEL_4 = LOW), CELLS_PER_OBJECT = 64 ) 

I really don’t understand what the grid or cell levels are for the object, but what I am looking for is the best settings for my scenario, where I am looking for businesses that are within 30 miles of the point (latitude / longitude).

Any tips?

Answer 1

This is a little-known secret (or at least it was for me before I discovered it!)

 EXEC sp_help_spatial_geography_index @tabname = '[TABLE_NAME]', @indexname = '[SPATIAL_INDEX_NAME]', @verboseoutput = 1, @query_sample = 'POLYGON((xy,xy,xy,xy))' 

Fill in the obvious bits and keep @verboseoutput to 1 . This gives you an idea of ​​the effectiveness of primary and internal filters (higher, of course, better). For a brief overview of spatial indexing, including which grids and cells are aligned, to try here .

In addition, I found this video quite interesting.