Spatial Indexes
This chapter describes Spatial indexes provided by Tibero Spatial. The indexes are implemented in RTREE format and used to improve the performance of spatial queries. There are indexes for the planar
Creating Spatial Indexes
The following describes how to create a Spatial index.
Usage
CREATE INDEX index_name on [schema_name.]
table_name ON col_name RTREEindex_name
Name of a Spatial index.
schema_name
Owner of a table for which an index is created.
table_name
Name of a table for which an index is created.
col_name
Name of a GEOMETRY type column for which an index is created.
RTREE
Indicates an RTREE index.
Example
SQL>CREATE TABLE GIS (ID INTEGER PRIMARY KEY, GEOM GEOMETRY);
Table 'GIS' created.
SQL>CREATE INDEX RT_IDX_GIS ON GIS(GEOM) RTREE;
Index 'RT_IDX_GIS' created.
INSERT INTO GIS VALUES (101, ST_GEOMFROMTEXT('POINT(1 1)'));
INSERT INTO GIS VALUES (102, ST_GEOMFROMTEXT('MULTIPOINT(1 1, 2 2)'));
INSERT INTO GIS VALUES (103, ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)'));
INSERT INTO GIS VALUES (104, ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2),
(3 3, 4 5))'));
INSERT INTO GIS VALUES (105, ST_GEOMFROMTEXT('POLYGON((1 1, 2 1, 2 2, 1 2,
1 1))'));
INSERT INTO GIS VALUES (106, ST_GEOMFROMTEXT('POLYGON((0 0, 0 12, 12 12, 12 0,0 0),
(6 10, 6 11, 9 11, 9 10,6 10), (6 3, 6 6, 9 6, 9 3,6 3))'));
INSERT INTO GIS VALUES (107, ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 2 1, 2 2, 1 2, 1 1)),
((3 3, 3 5, 5 5, 5 3, 3 3)))'));
INSERT INTO GIS VALUES (108, ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1),
LINESTRING(2 2, 3 3))'));
INSERT INTO GIS VALUES (109, ST_BOUNDARY(ST_GEOMFROMTEXT('POINT(10 10)')));
INSERT INTO GIS VALUES (110, ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1),
LINESTRING(2 2, 3 3))'));
COMMIT;Spatial Index Constraints
Spatial indexes have the following constraints.
A single index cannot be used for multiple columns.
A Spatial index cannot be partitioned.
A Spatial index cannot be created for non-GEOMETRY type columns.
A Spatial index can be used only when the following functions are implemented in a WHERE clause of SQL statements. - ST_CONTAINS - ST_COVEREDBY - ST_COVERS - ST_CROSSES - ST_DWITHIN - ST_EQUALS - ST_INTERSECTS - ST_OVERLAPS - ST_TOUCHES - ST_WITHIN
A Spatial index of the spheroid coordinate system can be created by adding a constraint to the GEOMETRY column of the table.
Usage
ALTER TABLE table_name ADD CONSTRAINTS
constraint_name CHECK(ST_SRID(col_name)=srid);Example
SQL>DROP INDEX RT_IDX_GIS;
Index 'RT_IDX_GIS' dropped.
SQL>UPDATE GIS SET GEOM=ST_SETSRID(GEOM,4326);
10 rows updated.
SQL>ALTER TABLE GIS ADD CONSTRAINTS SRID4326 CHECK(ST_SRID(GEOM)=4326);
Table 'GIS' altered.
SQL>CREATE INDEX RT_IDX_4326 ON GIS(GEOM) RTREE;
Index 'RT_IDX_4326' created.
COMMIT;In some cases, it is necessary to change the coordinate system of the existing GEOMETRY column and create a Spatial index. If there are existing constraints, delete them, change the SRID of GEOMETRY, and then add appropriate constraints for the SRID to create the Spatial index.
Usage
ALTER TABLE table_name DROP CONSTRAINTS constraint_name;Example
SQL>DROP INDEX RT_IDX_4326;
Index 'RT_IDX_4326' dropped.
SQL>ALTER TABLE GIS DROP CONSTRAINTS SRID4326;
Table 'GIS' altered.
SQL>UPDATE GIS SET GEOM=ST_SETSRID(GEOM,0);
10 rows updated.
SQL>ALTER TABLE GIS ADD CONSTRAINTS SRID4326 CHECK(ST_SRID(GEOM)=0);
Table 'GIS' altered.
SQL>CREATE INDEX RT_IDX_GIS ON GIS(GEOM) RTREE;
Index 'RT_IDX_GIS' created.
COMMIT;Dropping Spatial Indexes
The following describes how to drop a Spatial index.
The method is the same as for other indexes.
Usage
DROP INDEX index_nameindex_name
Name of an index to drop
Last updated