Create spatial indexes

OceanBase Database allows you to create spatial indexes by using the SPATIAL keyword. When you create a table, the column for the spatial index must be declared as NOT NULL. You can create spatial indexes on stored (STORED) generated columns, but not on virtual (VIRTUAL) generated columns.

Constraints

• The column definition for creating a spatial index must include the NOT NULL constraint.
• The column on which you create a spatial index must have an SRID defined. Otherwise, the spatial index cannot be used in queries.
• If you create a spatial index on a stored generated column, the STORED keyword must be explicitly specified when creating the column. If you do not specify the VIRTUAL or STORED keyword when creating a generated column, it is created as a virtual generated column by default.
• After an index is created, the SRID in the column definition is used for coordinate system comparison. Spatial indexes store the Minimum Bounding Rectangles (MBRs) of geometric objects. The comparison method for MBRs also depends on the SRID.

Prerequisites

Before using the GIS features, you must configure GIS metadata for the business tenant. After connecting to the server, execute the following command to import the default_srs_data_mysql.sql file into the database:

-- module specifies the module to be imported.
-- tenant specifies the tenant to be imported.
-- infile specifies the relative path of the SQL file to be imported.
ALTER SYSTEM LOAD MODULE DATA module=gis tenant=mysql infile = 'etc/default_srs_data_mysql.sql';

For more information about the syntax of this statement, see LOAD MODULE DATA.

If the following result is returned, the data file is imported successfully:

Query OK, 0 rows affected

Examples

The following examples show how to create spatial indexes on regular columns:

• 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 spatial indexes:

• Use the ALTER TABLE statement:

ALTER TABLE geom DROP INDEX g;

• Use the DROP INDEX statement:

DROP INDEX g ON geom;