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');

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)

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);

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;

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.

• ST_Contains
• ST_Crosses
• ST_Disjoint (PostgreSQL only)
• ST_Equals
• ST_Intersects
• ST_Overlaps
• ST_Relate (Oracle only)
• ST_Touches
• ST_Within

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 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.

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.

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:

• Consider how many grid levels are needed and remember that the database optimizer scans the spatial index table once per grid level. Often, a single grid level is the best solution for a feature class despite the notion of distributing geometries evenly across many grid levels to minimize the spatial index entries.
• Use one grid level for pure point type feature classes and consider making the cell sizes large. Spatial queries generally process point geometries faster than other geometry types.
• Monitor the spatial index. Tuning a spatial index is difficult if the data changes frequently. Tuning depends on the structure of the spatial data. Periodically assess the spatial index as your spatial data changes.
• Base the spatial index on the application. Match the spatial index grid cell sizes to the extent of the application window. By doing so, the application is probably viewing exact entries in the spatial index table. This helps to size the spatial index table suitably and reduces the amount of processing, because fewer candidate feature IDs must be evaluated against the feature table.
• For unknown or variable application windows, start by defining one grid level with a cell size three times the average feature extent size. Query the business table to obtain the average feature size with the following SQL statement:

SELECT (AVG(ST_MAXX.shape – ST_MINX.shape) + AVG(ST_MAXY.shape – ST_MINY.shape)) / 2  from <N>; 



(where <N> is the spatial table and shape is the <N> table's geometry column)
Such a spatial index configuration minimizes the number of rows in the spatial index table while maintaining the proficiency of the index, because the majority of the features can be referenced by less than one or two grid cells.
• Design the spatial table around spatial data categories such as type, geometry size, and distribution. Sometimes, a carefully designed feature class, using these categories, can substantially boost the performance of spatial queries.

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:

• Symptoms when a grid size is wrong
If a grid size is determined to be too large or too small when creating a spatial index in Oracle, the following error occurs:

CREATE INDEX farm_idx on farm(shape)
INDEXTYPE is SDE.ST_SPATIAL_INDEX
PARAMETERS ('st_grids=1000,3000,9000 st_srid=31');

CREATE INDEX farm_idx on farm(shape)
*
ERROR at line 1:
ORA-20006: InValid Grid Size
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 43
ORA-06512: at "SDE.ST_POLYGON", line 236



To identify common issues with DB2, please see chapter 15, "Identifying Problems," in the DB2 Spatial Extender and Geodetic Extender User's Guide and Reference. When using Informix, see the "Error Messages" section of the Informix Spatial DataBlade Module User's Guide.
• Deferring creation of a spatial index after loading data
Loading or appending data into a spatial table can decrease the total time it takes to complete the load. It also results in less fragmentation of column indexes, which improves query performance. Therefore, it can be useful to defer creating the spatial index until you complete loading your data into the spatial table.
• Defragmenting the spatial index
If you find that your indexes have become fragmented as a result of prolonged interactive editing, you can rebuild those indexes by dropping and re-creating the individual indexes.
• Using ST_AsText to display the ST_Geometry contents
The human-readable well-known text representation is useful when you want to examine the content of individual geometries. Use the ST_AsText function in a SELECT statement to translate the ST_Geometry contents into well-known text for display.
• Indexing an empty table
You can add a spatial index to the table prior to registering it with ArcSDE. However, if the table is in an Oracle database, contains an ST_Geometry column, but does not contain any data, do not add a spatial index before registering with ArcSDE. If you do, you will receive an error message similar to the following:

Error: Underlying DBMS error (-51)
Error: Cannot Create Layer.
Column index already exists
Failure to register ST_Geometry layer (OBY.LAKES). Cannot
register an empty table with an existing spatial index.



You can build spatial indexes after the table is registered with ArcSDE and the geodatabase.