DB2 Spatial Extender works with a spatial index to improve the performance of a spatial query. Consider the most basic and probably most popular spatial query, the box query. This query asks DB2 Spatial Extender to return all geometries that are either fully or partially within a user-defined box. If an index does not exist, DB2 Spatial Extender must compare all of the geometries with the box. However, with an index, DB2 Spatial Extender can locate all the index entries that have a lower left-hand coordinate greater than or equal the box's and an upper right-hand coordinate less than or equal to the box's. Because the index is ordered by this coordinate system, DB2 Spatial Extender is able to quickly obtain a list of candidate geometries. The process just described is referred to as the first pass.
A second pass determines if each candidate's envelope intersects the box. A geometry that qualifies for first pass because its grid cells' envelope intersects the box may itself have an envelope that does not.
A third pass compares the actual coordinates of the candidate with the box to determine if any part of the geometry is actually within the box. This last and rather complex process of comparison operates on a list of candidates composed of a subset of the total population, which is significantly reduced by the first two passes.
All spatial queries perform the three passes except for the EnvelopesIntersect function. It performs only the first two passes. The EnvelopesIntersect function was designed for display operations that often employ their own built-in clipping routines and don't require the granularity of the third pass.
The irregular shape of the geometry envelopes complicates the selection of the grid cell size. Because of this irregularity, some geometry envelopes intersect several grids, while others fit inside a single grid cell. Conversely, depending on the spatial distribution of the data, some grid cells intersect many geometry envelopes.
For a spatial index to function well, it is essential that the correct number and size of grids are selected. Consider a spatial column containing uniformly sized geometry. In this case, a single grid level will suffice. Start with a grid cell size that encompasses the average geometry envelope. While testing your application, you might find that increasing the grid cell size improves the performance of your queries. This is because each grid cell contains more geometries, and the first pass is able to discard non-qualifying geometries faster. However, you will find that as you continue to increase the cell size, performance will begin to deteriorate. This because eventually the second pass will have to contend with more candidates.
If the objects that you want to index are about the same relative size, you can use a single grid level. Although this is true, not all columns will contain geometry of the same relative size. Usually geometries of spatial columns can be grouped into several size intervals. For example, consider a road network in which the geometries are divided into streets, major roads, and highways. The streets are all about the same length and can be grouped in one size interval. This is also true for the major roads and highways. Therefore, the streets, representing one size interval, could be grouped into the first grid level, the road networks into the second, and the major highways into the third grid level. Another example includes a county-parcel column that contains clusters of small urban parcels surrounded by larger rural parcels. In this instance, there are two size intervals and two grid levels, one for the small urban parcels, and another for the larger rural parcels. These situations are very common and require the use of a multilevel grid.
To select the cell size of each grid level, select grid cell sizes that are slightly larger than each size interval. Test the index by performing queries against the spatial column.
Each additional level requires an extra index scan. Try adjusting the grid sizes up or down slightly to determine if an appreciable improvement in performance can be obtained.