Vantage supports creation of nonunique secondary indexes (NUSIs) on geospatial data columns. These indexes are implemented internally using a Hilbert R-tree structure. Geospatial indexes can greatly improve the performance of geospatial queries that contain single-table predicates or join predicates using the following geospatial methods:
- MBB_Filter
- MBR_Filter
- ST_3DDistance Method
- ST_Contains Method
- ST_Crosses Method
- ST_Distance Method
- ST_Equals Method
- ST_Intersects Method
- ST_Overlaps Method
- ST_Touches Method
- ST_Within Method
- Within_MBB
- Intersects_MBB
- ST_Relate Method (if this predicate is configured to execute one of the geospatial methods listed previous)
For more information on how geospatial indexes and predicates can speed geospatial queries, see Geospatial Predicates and the Optimizer .
The syntax used to create indexes on geospatial data columns is the same as the syntax used with CREATE INDEX and CREATE TABLE to create any NUSI with the following restrictions:
- Geospatial NUSIs must be single-column indexes.
- Geospatial NUSIs cannot be created on geospatial columns used in join indexes.
- The ALL keyword is not supported for geospatial indexes.
- The ORDER BY VALUES and ORDER BY HASH clauses are not supported for geospatial indexes.
- Geospatial indexes cannot be created on global temporary or volatile tables. Consequently, the TEMPORARY keyword is not supported for geospatial indexes.
- Geospatial indexes can be created for 3D data, however the z coordinate is ignored. The MBR of the geometry, which is based only on the x and y coordinates, is used to create the index.
- Because geospatial indexing is based on x and y coordinates, performance can be adversely affected by indexing if 3D geospatial objects being indexed are stacked in the z axis, with very little distribution in the x and y axes.
For more information about indexes, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - Database Design, B035-1094. For more information on the Hilbert R-tree structure, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.