DB2 Version 9.7 for Linux, UNIX, and Windows

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 DB2® SQL CREATE INDEX statement. The user ID must have at least one of the following authorities or privileges:
- DBADM authority on the database where the table that has the column resides
- Both of the following authorities or privileges:
  - One of the following table privileges:
    - CONTROL privilege on the table
    - INDEX privilege on the table
  - One of the following authorizations or privileges on the schema:
    - IMPLICIT_SCHEMA authority on the database, if the schema of the index does not exist
    - CREATEIN privilege on the schema, if the schema name of the index refers to an existing schema
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

Restriction:

The same restrictions for creating indexes using the CREATE INDEX statement are in effect when you create a spatial grid index. That is, the column on which you create an index must be a base table column, not a view column or a nickname column. The DB2 database system will resolve aliases in the process.

You create spatial grid indexes to improve the performance of queries on spatial columns.

When you create a spatial grid index, you give it 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.

You can create a spatial grid index in one of the following ways:

Use the Spatial Extender window of the DB2 Control Center.
Use the SQL CREATE INDEX statement with the db2gse.spatial_index extension in the EXTEND USING clause.
Use a GIS tool that works with DB2 Spatial Extender. If you use such a tool to create the index, the tool will issue the appropriate SQL CREATE INDEX statement.

This topic presents the steps for the first two methods. For information about using a GIS tool to create a spatial grid index, see the documentation that comes with that tool.

To do this task... :

Creating a spatial grid index using SQL CREATE INDEX

Procedure

Determine the CREATE INDEX statement using the EXTEND USING clause and the db2gse.spatial_index grid index extension. For example, the following statement creates 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)
```
Issue the CREATE INDEX command on the DB2 Command Editor, the DB2 Command Window, or the DB2 command line processor.