Determining spatial index grid cell sizes
Determining an optimal grid cell size or grid cell sizes may not be obvious. In general, for line and polygon spatial values, the grid cell size should be a little larger than the size that would hold an average size line or polygon. For point spatial values, the grid cell size should be about 1/10 of the width of a typical query region.
In order to simplify this determination, two tools are provided that analyze the spatial values in a table and provide recommended grid cell sizes to use when creating the index. Don't worry about getting this precisely correct; the performance will be good for a fairly wide variation in the index parameters.
Using the gseidx index advisor tool
The gseidx command-line tool is provided with the installation of Spatial Extender and is available on all of the supported platforms. Significant information about spatial indices and the details of gseidx is provided in the DB2 Knowledge Center in the section Using indices and views to access spatial data.
Listing 10 shows a simple example of using gseidx.
advise_counties.sql:
!gseidx "connect to sample
get geometry statistics
for column test.counties(shape) advise";
This command can be executed using the command db2 -tvf advise_counties.sql and will result in the output shown in Listing 11.
Listing 11. Output of db2 -tvf advise_counties.sql command
Number of Rows: 3141
Number of non-empty Geometries: 3141
Number of empty Geometries: 0
Number of null values: 0
Extent covered by data:
Minimum X: -178.217598
Maximum X: -66.969271
Minimum Y: 18.921786
Maximum Y: 71.406235
Query Window Size: Suggested Grid Sizes: Index Entry Cost:
-------------------- ----------------------------- ----------------------
0.01: 1.2, 3, 14 4.6
0.02: 1.2, 3, 14 4.6
0.05: 1.2, 3, 14 4.7
0.1: 1.2, 3, 14 4.9
0.2: 1.2, 3, 14 5.3
0.5: 1.2, 3, 14 6.6
1: 1.2, 3, 14 9.3
2: 1.2, 3, 14 16
5: 1.8, 3.6, 13 49
10: 2.9, 8.7, 26 130
20: 4.6, 14, 49 390
50: 12, 72, 0 1700
The first two sections of Listing 11 provide statistics about the spatial data that was analyzed. The middle three columns of the last section provide suggested grid sizes to use in creating a spatial index.
The first column contains values that correspond to typical query window sizes. In the case of an application that is displaying a map, this is the typical width of the region that would be displayed. For example, if a user would most commonly display a map that was 10 miles across, this would correspond to about 0.25 degrees. Looking at the table, you can see that the suggested grid sizes are the same for query window sizes of 0.2 and 0.5 degrees, so you would use (1.2, 3, 14) to create the index.
The last column isn't particularly important. It is an estimate of the number of index entries that would be referenced in satisfying a query.