Spatial indexes and ST_Geometry

Spatial indexes used with ST_Geometry are implemented differently depending on the DBMS used. ST_Geometry in Oracle and DB2 uses a spatial grid index. The IBM Informix Spatial DataBlade module, the ST_Geometry implementation in PostgreSQL, and SQLite use an R-tree index to index the spatial data. In SQLite, the R-tree index is a virtual table.

You can create a spatial index in several ways:

Be aware that when you issue spatial relationship queries against tables that contain ST_Geometry columns, they utilize a spatial index to speed the query process only when you execute certain spatial relationship functions. These are listed in When are spatial indexes used?.

Oracle

A feature class created using ST_Geometry storage with a spatial index creates an additional table within the Oracle database. The spatial index table is named S<n>_IDX$, where <n> is the geometry index value for the table. The value can be obtained by querying the SDE.ST_GEOMETRY_COLUMNS table. The spatial index table is created as an Oracle Indexed Organized Table (IOT). The spatial index on the ST_Geometry attribute appears as A<n>_IX1 when viewed through Enterprise Manager. The value of <n> represents the LAYER_ID value stored in the LAYERS table.

Two additional indexes are created on the S<n>_IDX$ table: S<n>$_IX1 and S<n>$_IX2. You can specify how these indexes are stored in the DBMS by altering the S_STORAGE parameter in the DBTUNE configuration keyword you specify when creating a feature class.

If you create partitioned business tables that contain an ST_Geometry column, you may also want the spatial index to be partitioned. There are two types of partitioning methods: global and local. By default, global partitioned indexes are created on partitioned business tables. To create a local partitioned index, you must add the keyword LOCAL to the end of the CREATE INDEX statement. To enable ArcGIS to add LOCAL to the end of the CREATE INDEX statement for the spatial index, set the parameter ST_INDEX_PARTITION_LOCAL to TRUE under the DEFAULTS keyword.

PostgreSQL

In PostgreSQL, the R-tree index is implemented using the Generalized Search Tree (GiST) index infrastructure. For information on GiST indexing, see the PostgreSQL documentation.

SQLite

The spatial index in SQLite is a set of tables used as an R-tree index.

IBM DB2 and Informix

For information on spatial indexes in DB2, see Spatial indexes generated by the DB2 Spatial Extender. For information on spatial indexes in Informix, see Spatial indexes in geodatabases in Informix.

Related Topics

6/19/2015