Create a spatial index

OceanBase Database allows you to create spatial indexes by using the syntax for creating regular indexes. However, you must use the SPATIAL keyword. The columns in a spatial index must be declared as NOT NULL.

The following examples show how to create a spatial index:

• Use the CREATE TABLE statement:

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));

• Use the ALTER TABLE statement:

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
ALTER TABLE geom ADD SPATIAL INDEX(g);

• Use the CREATE INDEX statement:

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
CREATE SPATIAL INDEX g ON geom (g);

The following examples show how to delete a spatial index:

• Use the ALTER TABLE statement:

ALTER TABLE geom DROP INDEX g;

• Use the DROP INDEX statement:

DROP INDEX g ON geom;

When you create spatial indexes, observe the following constraints:

• The columns in a spatial index must be declared as NOT NULL.
• After an index is created, the coordinate system of the SRID defined in the column is used for comparison. A spatial index stores the minimum bounding rectangle (MBR) build of a geometry object. The comparison mode of MBR also depends on the SRID.
• Preferably, the column on which a spatial index is created has an SRID defined. Otherwise, the spatial index created on the column cannot take effect in a query.