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 RTREE

Item	Description
index_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_name

Item	Description
index_name	Name of an index to drop