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 drop a spatial index:

• Use the ALTER TABLE statement:

  ALTER TABLE geom DROP INDEX g;
  

• Use the DROP INDEX statement:

  DROP INDEX g ON geom;
  

Limitations on creating spatial indexes are as follows:

• 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.