How a query uses a spatial grid index

When the query optimizer chooses a spatial grid index, the query execution uses a multiple-step filter process.

The filter process includes the following steps:
  1. Determine the grid cells that intersect the query window. The query window is the geometry that you are interested in and that you specify as the second parameter in a spatial function (see examples below).
  2. Scan the index for entries that have matching grid cell identifiers.
  3. Compare the geometry MBR values in the index entries with the query window and discard any values that are outside the query window.
  4. Perform further analysis as appropriate. The candidate set of geometries from the previous steps might undergo further analysis to determine if they satisfy the spatial function (ST_Contains, ST_Distance, and so on). The spatial function EnvelopesIntersect omits this step and typically has the best performance.
The following examples of spatial queries have a spatial grid index on the column C.GEOMETRY: 

SELECT name
FROM counties AS c
WHERE EnvelopesIntersect(c.geometry, -73.0, 42.0, -72.0, 43.0, 1) = 1

SELECT name 
FROM counties AS c
WHERE ST_Intersects(c.geometry, :geometry2) = 1

In the first example, the four coordinate values define the query window. These coordinate values specify the lower-left and upper-right corners (42.0 –73.0 and 43.0 –72.0) of a rectangle.

In the second example, IBM® Spatial Support for Db2 for z/OS® computes the MBR of the geometry specified by the host variable :geometry2 and uses it as the query window.

When you create a spatial grid index, you should specify appropriate grid sizes for the most common query window sizes that your spatial application is likely to use. If a grid size is larger, index entries for geometries that are outside of the query window must be scanned because they reside in grid cells that intersect the query window, and these extra scans degrade performance. However, a smaller grid size might generate more index entries for each geometry and more index entries must be scanned, which also degrades query performance.