Spatial indexes in geodatabases in Informix

The IBM Informix Spatial DataBlade provides support for building a spatial index called an R-tree spatial index, which is used to query spatial columns that contain two-dimensional geographic data.

The R-tree index differs from the hierarchical btree index provided by the Informix Dynamic Server software.

The btree index cannot be applied to a spatial column because the two-dimensional characteristic of the spatial column requires an R-tree index. For the same reason, you cannot apply R-tree indexes to a nonspatial column or a composite column.

Use the USING RTREE clause in the CREATE INDEX statement when creating an R-tree index rather than a btree index. The full syntax is as follows:

CREATE INDEX <index> 
ON <table> (<spatial column> ST_Geometry_Ops) 
USING RTREE (<parameters>) <index options>;

The ST_Geometry_Ops is the Informix Spatial DataBlade operator class. ST_Geometry_Ops manages the R-tree index.

ArcSDE creates a spatial index when a feature class is first created and when it is switched from load_only_io mode to normal_io mode. The spatial index is created with the default parameter bottom_up_build = 'yes' and no index options.

You do not need to tune the spatial index for performance, since this is all handled through the R-tree index. Therefore, you do not have to experiment with the spatial index by trying different cell sizes and different grid-level configurations. ArcSDE for Informix does not require specifying a spatial index or defining spatial grid sizes. This means you do not need to specify the –g Spatial Index flag if you use the ArcSDE administration commands for data management, such as shp2sde. Setting the –g flag will not hurt anything, but the –g flag is ignored for geodatabases stored in Informix and, therefore, is not used.

For details on ArcSDE administration commands, see the ArcSDE Administration Command Reference installed with the commands or with the ArcSDE application server.

R-tree index statistics

The Informix optimizer does not use the R-tree index unless the statistics on the table are up-to-date. If the R-tree index is created after the data has been loaded, the statistics are up-to-date, and the optimizer uses the index. However, if the index is created and data is loaded afterwards, the optimizer does not use the R-tree index because the statistics are out of date. To update the statistics, use the UPDATE STATISTICS Informix SQL statement.

UPDATE STATISTICS FOR TABLE <table_name>