OceanBase AI Database supports creating spatial indexes using the SPATIAL keyword. When creating a table, the spatial index column must be declared as NOT NULL. Spatial indexes can be created on stored-generated columns, but not on virtual-generated columns.
Constraints
- The column definition for creating a spatial index must include the
NOT NULLconstraint. - The column for which a spatial index is created must have an SRID defined; otherwise, the spatial index on that column will not take effect during queries.
- If you create a spatial index on a STORED generated column, you must explicitly specify the
STOREDkeyword in the DDL statement when creating the column. If theVIRTUALorSTOREDkeyword is not specified when creating the generated column, it is created as a VIRTUAL generated column by default. - After an index is created, the coordinate system corresponding to the SRID in the column definition is used for comparisons. Spatial indexes are stored as the Minimum Bounding Rectangle (MBR) of the geometric object, and the comparison of MBRs also depends on the SRID.
Prerequisites
Before using GIS features, you must configure GIS metadata 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:
Notice
This import command can only be executed in the SYS tenant. When it is executed, data is written to the primary tenant and synchronized to the standby tenants.
-- 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 statement syntax, see LOAD MODULE DATA.
The following results indicate a successful import of the data file:
Query OK, 0 rows affected
Examples
The following example shows how to create a spatial index on a regular column:
- Using
CREATE TABLE:
CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));
- Using
ALTER TABLE:
CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
ALTER TABLE geom ADD SPATIAL INDEX(g);
- Using
CREATE INDEX:
CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
CREATE SPATIAL INDEX g ON geom (g);
The following example shows how to drop a spatial index:
- Using
ALTER TABLE:
ALTER TABLE geom DROP INDEX g;
- Using
DROP INDEX:
DROP INDEX g ON geom;