Creating spatial grid indexes

Create spatial grid indexes to define two-dimensional grid indexes on spatial columns to help optimize spatial queries.

Before you begin

Before you create a spatial grid index:
  • Your user ID must hold the authorizations that are needed for the CREATE INDEX SQL statement.
  • You must know the values that you want to specify for the fully qualified spatial grid index name and the three grid sizes that the index will use.
Recommendations:
  • Before you create a spatial grid index on a column, use the Index Advisor to determine the parameters for the index. The Index Advisor can analyze the spatial column data and suggest appropriate grid sizes for your spatial grid index.
  • If you plan to do an initial load of data into the column, you should create the spatial grid index after you complete the load process. That way, you can choose optimal grid cell sizes that are based on the characteristics of the data by using the Index Advisor. In addition, loading the data before creating the index will improve the performance of the load process because then the spatial grid index does not need to be maintained during the load process.

About this task

You can create spatial grid indexes to improve the performance of queries on spatial columns. When you create a spatial grid index, you must provide the following information:
  • A name
  • The name of the spatial column on which it is to be defined
  • The combination of the three grid sizes helps optimize performance by minimizing the total number of index entries and the number of index entries that need to be scanned to satisfy a query.

This task describe the steps on how to create spatial grid indexes by using the CREATE INDEX SQL statement. You can also create spatial grid indexes using a GIS tool that works with Spatial Extender. For information about using a GIS tool to create a spatial grid index, see the documentation that comes with that tool.

Procedure

To create spatial grid indexes:

Issue the CREATE INDEX command, specifying the db2gse.spatial_index grid index extension with the EXTEND USING clause.
The following example shows how to create the spatial grid index TERRIDX for table BRANCHES that has a spatial column TERRITORY. 
CREATE INDEX terridx 
   ON branches (territory)
   EXTEND USING db2gse.spatial_index (1.0, 10.0, 100.0)