Fine-tuning a geospatial index

问题:

I just converted my SQL Server 2008 database from using a lat/long pair to using the new geography type. I run queries on businesses that are within 30 miles of a geography::Point, using the STDistance function, like so:

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

Here is the index that I have on 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 don't really understand what the grid levels or the cells per object mean, but what I'm looking for are the best settings for my scenario, where I'm searching for businesses that are within 30 miles of a point (lat/long).

Any tips?

回答1:

This is a little known secret (or at least it was to 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. It gives you an idea of the efficency of the primary and internal filters (higher the better of course). For a quick overview of spatial indexing, including what grids and cells equate to try here.

Also, i found this video pretty interesting.