Teradata Database 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
- ST_Contains
- ST_Crosses
- ST_Distance
- ST_Equals
- ST_Intersects
- ST_Overlaps
- ST_Touches
- ST_Within
- Within_MBB
- Intersects_MBB
- ST_Relate (if this predicate is configured to execute one of the above methods)
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.