Spatial Indexing

Together with the new spatial datatypes, geometry and geography, SQL Server 2008 also introduces a new type of index called (unsurprisingly) a spatial index. Spatial indexes act as a primary filter on the results of certain spatial operations, ensuring that your spatial applications find the data you want quickly and efficiently.

Spatial indexes in SQL Server 2008 use a 4-level grid system, which can be customised by altering the following parameters:

• BOUNDING_BOX: The bounding box specifies the extent of space over which the grid index is applied. Features that lie outside the bounding box will not be included in the index.
• GRIDS: You may set the resolution at each of the four grid levels to be either LOW, MEDIUM, or HIGH. This determines the density of cells contained at the appropriate grid level - LOW corresponds to a 4x4 grid, MEDIUM is a 8x8 grid, and HIGH is a 16x16 grid. Each level may be set independently.
• CELLS_PER_OBJECT: To avoid the index entry growing too large, and therefore becoming inefficient, you may manually specify a limit on the number of cells used to describe each object. Once this limit is reached, cells are not further subdivided into lower grid levels.

In this chapter, I'll explain the effect of choosing different values for these index parameters, and give you some tips to help you choose the right index settings for your data. Then I'll show you how to create a spatial index, using syntax as shown below:

CREATE SPATIAL INDEX indexname
  ON tablename (columnname)
  USING GEOMETRY_GRID
  WITH (
    BOUNDING_BOX = ( -180, -90, 180, 90),
    GRIDS = (
      LEVEL_1 = HIGH, 
      LEVEL_2 = HIGH, 
      LEVEL_3 = HIGH, 
      LEVEL_4 = HIGH),
    CELLS_PER_OBJECT = 16
  )