ArcGIS Server Banner

Creating spatial indexes on tables with an ST_Geometry column

Creating spatial indexes on tables with an ST_Geometry column

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
When an ST_Geometry column is added to a business table, a spatial index on that geometry column is usually created. When ArcSDE creates a feature class with an ST_Geometry column, it creates a spatial index by default. Alternatively, ArcSDE can create feature classes with no spatial index. Spatial indexes greatly increase the speed of spatial queries, so using them is important when there are more than a few geometries in a table.

Spatial indexes used with ST_Geometry are implemented differently depending on the DBMS used. The IBM Informix Spatial DataBlade module and the PostgreSQL implementation use an R-tree index to allow indexing of spatial data. The Oracle and DB2 implementations use a spatial grid index.

You can create a spatial index several ways.

ArcSDE automatically drops and re-creates any spatial index created by ArcSDE whenever the feature class is switched between LOAD_ONLY_IO and NORMAL_IO mode.

When you create a spatial index, you need to provide the following information:

The following are examples of creating a spatial index on an ST_Geometry column in each of the supported DBMSs.

Oracle

CREATE INDEX sa_idx ON sensitive_areas(zone)
INDEXTYPE IS sde.st_spatial_index
PARAMETERS('st_grids=1,3,0 st_srid=1');

CREATE INDEX hs_idx ON hazardous_sites(location)
INDEXTYPE IS sde.st_spatial_index
PARAMETERS('st_grids=1,0,0 st_srid=1');

DB2

CREATE INDEX sa_idx ON sensitive_areas(zone) 
EXTEND USING db2gse.spatial_index (1.0, 3.0, 0.0)

CREATE INDEX hs_idx ON hazardous_sites(location) 
EXTEND USING db2gse.spatial_index (1.0, 0.0, 0.0)

PostgreSQL

ArcSDE for PostgreSQL uses an R-tree index created using the Generalized Search Tree (GiST) index and, therefore, does not deal with grid sizes. An example statement to create spatial indexes for the same tables in PostgreSQL follows:

CREATE INDEX sa_idx ON sensitive_areas 
USING gist(zone st_geometry_ops);

CREATE INDEX sa_idx ON hazardous_sites
USING gist(location st_geometry_ops);

Informix

Informix uses R-tree indexes and, therefore, also does not deal with grid sizes. An example statement to create spatial indexes in Informix is shown below:

CREATE INDEX sa_ix
ON sensitive_areas (zone ST_Geometry_ops)
USING RTREE;

CREATE INDEX hs_ix
ON hazardous_sites (location ST_Geometry_ops)
USING RTREE;

When a spatial index is used

Spatial indexes are used with queries that call functions to compare geometries. The Oracle and PostgreSQL ST_Geometry functions using spatial indexes are the ones that test spatial relationships.

For information on DB2 ST_Geometry functions that can use a spatial index, see the DB2 documentation.

Consult the IBM Informix Spatial DataBlade Module User's Guide for information on functions that utilize Informix's R-tree spatial index.

The R-tree index

The two-dimensional R-tree index differs from the traditional hierarchical (one-dimensional) B-tree index. Spatial data is two-dimensional, so you cannot use the B-tree index for spatial data. Similarly, you cannot use an R-tree index with nonspatial data. The R-tree access method organizes data in a tree-shaped structure with bounding boxes at the nodes.

A search using an R-tree index descends the tree to find objects in the general area of interest and then perform tests on the objects themselves. An R-tree index eliminates the need to examine objects outside the area of interest. Without an R-tree index, a query would need to evaluate every object to find those that match the query criteria.

For more information about the Informix R-tree index, consult the IBM Informix Spatial DataBlade Module User's Guide.

In PostgreSQL, the R-tree index is implemented using the Generalized Search Tree index infrastructure. For information on GiST indexing, see chapter 51 of the PostgreSQL 8.3 documentation.

The spatial grid index

ArcSDE geodatabases in Oracle and DB2 use grid indexes. The spatial index is built by applying a grid to the data in the spatial column. The spatial grid index is two-dimensional and spans a feature class, similar to the reference grid you might find on a common road map. You can assign the spatial grid index one, two, or three grid levels, each with its own distinct cell size. The mandatory first grid level has the smallest cell size. The optional second and third grid cell levels are disabled by setting them to 0. If enabled, the second grid cell size must be at least three times larger than the first grid cell size, and the third grid cell size must be three times larger than the second grid cell size.

In the following example, the feature class has two grid levels. Area shape 101 is located in grid cell 4 on level 1. A record is added to the spatial index table because the feature resides within four grid cells (in this case it is one). The envelope for area feature 102 is located in cells 1 through 8 on level 1. Because the feature's envelope resides in more than four grid cells, the feature is promoted to level 2, where its envelope fits within two grid cells. Feature 102 is indexed at level 2, and two records are added to the spatial index table.

Shape 101 is indexed on grid level 1; shape 102 is indexed on grid level 2, where it is in only two grid cells.

Inserting, updating, or deleting a feature updates the spatial index. The extent of each feature is overlaid onto the lowest grid level to obtain the number of grid cells. If the feature exceeds four cells, the geometry is promoted to the next highest grid level, if you have defined one. The feature continues to be promoted until it fits within four cells or less or until the highest-defined grid level is reached. On the highest-defined grid level, geometries can be indexed by more than four grid cells.

For Oracle databases, you can specify the location in which the spatial index is created by setting the S_STORAGE parameter of the configuration keyword you use to create the feature class. See Oracle DBTUNE configuration parameters and the sdedbtune command in the ArcSDE Administration Command Reference for information on setting configuration parameters.

Choosing a spatial index grid size

Grid cell size impacts the size of the spatial index table. Setting up the spatial index means balancing the cell sizes—smaller cell sizes mean more cells per shape, which require more entries in the spatial index table.

If you create your data through the ArcGIS Desktop client, the spatial grid index is calculated for you and shouldn't need many, if any, alterations.

If you are using third-party client applications, the applications and spatial data profiles can vary from one system to another. In these cases, you may need to experiment with the spatial index, trying different cell sizes and different grid-level configurations.

The following guidelines can help improve the performance of spatial queries when you have to manually calculate the size of the spatial index:

For more information about the DB2 Spatial Extender grid index, see the IBM DB2 Spatial Extender and Geodetic Extender User's Guide and Reference.

Troubleshooting the spatial grid index

The following are tips on using the spatial index grid that is used by Oracle and DB2:

See Also

  • Querying tables with an ST_Geometry column