Determining grid sizes for a spatial grid index

Before you creating a spatial grid index, use the Index Advisor to determine appropriate grid sizes.

Before you begin

Your user ID must hold the SELECT privilege on this table.
If your table has more than one million rows, you might want to use the ANALYZE clause to analyze a subset of the rows to have reasonable processing time.

Procedure

To determine appropriate grid sizes for a spatial grid index:

Use the Index Advisor to determine a recommended grid cell size for the index that you want to create.
1. Enter the command that invokes the Index Advisor with the ADVISE keyword to request grid cell sizes. For example, to invoke the Index Advisor for the SHAPE column in the COUNTIES table, enter:
```
gseidx CONNECT TO mydb USER userID USING password GET GEOMETRY 
STATISTICS FOR COLUMN userID.counties(shape) ADVISE
```
  Restriction: If you enter this gseidx command from an operating system prompt, you must type the entire command on a single line. Alternatively, you can run gseidx commands from a CLP file, which allows you to split the command over multiple lines.
  
  The Index Advisor returns recommended grid cell sizes. For example, gseidx command with the ADVISE keyword shown previously returns the following recommended cell sizes for the SHAPE column:
```
Query Window Size  Suggested Grid Sizes             Cost
-----------------  --------------------------       ----
       0.1          0.7,       2.8,      14.0        2.7
       0.2          0.7,       2.8,      14.0        2.9
       0.5          1.4,       3.5,      14.0        3.5
         1          1.4,       3.5,      14.0        4.8
         2          1.4,       3.5,      14.0        8.2
         5          1.4,       3.5,      14.0         24
        10          2.8,       8.4,      21.0         66
        20          4.2,      14.7,      37.0        190
        50          7.0,      14.0,      70.0        900
       100         42.0,         0,         0       2800
```
2. Choose an appropriate query window size from the gseidx output, based on the width of the coordinates that you display on your screen.
  In this example, latitude and longitude values in decimal degrees represent the coordinates. If your typical map display has a width of about 0.5 degrees (approximately 55 kilometers), go to the row that has the value 0.5 in the Query Window Size column. This row has suggested grid sizes of 1.4, 3.5, and 14.0.
Create the index with the suggested grid sizes. For the example in the previous step, you can execute the following SQL statement:
```
CREATE INDEX counties_shape_idx ON userID.counties(shape) 
EXTEND USING DB2GSE.SPATIAL_INDEX(1.4,3.5,14.0);
```