Create a spatial index

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

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 created on this column will not take effect during queries.
• If you create a spatial index on a stored generated column, you must explicitly specify the STORED keyword when creating the column. If you do not specify the VIRTUAL or STORED keyword when creating a generated column, a virtual generated column will be created by default.
• After creating an index, the SRID in the column definition is used for coordinate system comparison. Spatial indexes are built based on the MBR (Minimum Bounding Rectangle) of geometric objects, and the comparison method for MBRs also depends on the SRID.

Prerequisites

Before using GIS features, you need to configure GIS meta data in 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 successfully imported:

Query OK, 0 rows affected

Examples

The following examples show how to create a spatial index on a regular column:

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