CREATE INDEX statement for a spatial grid index

Use the CREATE INDEX statement with the EXTEND USING clause to create a spatial grid index.

Syntax

Read syntax diagramSkip visual syntax diagramCREATE INDEXindex_schema.index_name ONtable_schema.table_name (column_name) EXTEND USINGdb2gse.spatial_index(finest_grid_size, middle_grid_size,coarsest_grid_size)

Parameters

index_schema.
Name of the schema to which the index that you are creating is to belong. If you do not specify a name, the schema name in the CURRENT SCHEMA special register is used.
index_name
Unqualified name of the grid index that you are creating.
table_schema.
Name of the schema to which the table that contains column_name belongs. If you do not specify a name, Db2® uses the schema name that is stored in the CURRENT SCHEMA special register.
table_name
Unqualified name of the table that contains column_name.
column_name
Name of the spatial column on which the spatial grid index is created.
finest_grid_size, middle_grid_size, coarsest_grid_size
Grid sizes for the spatial grid index. These parameters must adhere to the following conditions:
  • finest_grid_size must be larger than 0.
  • middle_grid_size must either be larger than finest_grid_size or be 0.
  • coarsest_grid_size must either be larger than middle_grid_size or be 0.
When you create the spatial grid index using the CREATE INDEX statement, the validity of the grid sizes are checked when the first geometry is indexed. Therefore, if the grid sizes that you specify do not meet the conditions of their values, an error condition is raised at the times described in these situations:
  • If all of the geometries in the spatial column are null, Db2 Spatial Extender successfully creates the index without verifying the validity of the grid sizes. Spatial Extender validates the grid sizes when you insert or update a non-null geometry in that spatial column. If the specified grid sizes are not valid, an error occurs when you insert or update the non-null geometry.
  • If non-null geometries exist in the spatial column when you create the index, Spatial Extender validates the grid sizes at that time. If the specified grid sizes are not valid, an error occurs immediately, and the spatial grid index is not created.

Example

The following example CREATE INDEX statement creates the TERRIDX spatial grid index on the spatial column TERRITORY in the BRANCHES table: 

CREATE INDEX terridx 
   ON branches (territory)
   EXTEND USING db2gse.spatial_index (1.0, 10.0, 100.0)