Create a spatial index

OceanBase Database supports creating spatial indexes using the same syntax as for creating regular indexes, but with the SPATIAL keyword. Columns in a spatial index must be declared as NOT NULL. Spatial indexes can be created on stored (STORED) generated columns, but not on virtual (VIRTUAL) generated columns.

Considerations

• The column definition for creating a spatial index must include the NOT NULL constraint.
• The column on which a spatial index is created must have an SRID defined. Otherwise, the spatial index will not be effective during queries.
• If you create a spatial index on a stored generated column, the STORED keyword must be explicitly specified during column creation. If you do not specify VIRTUAL or STORED when creating a generated column, it will default to a virtual generated column.
• After an index is created, the SRID defined in the column is used for coordinate system comparison. Spatial indexes store the MBR (Minimum Bounding Rectangle) of geometric objects, and MBR comparisons also depend on the SRID.

Prerequisites

Before using GIS features, you need to 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:

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

Create a spatial index

The following examples show 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 examples show how to drop a spatial index:

• Using ALTER TABLE:

ALTER TABLE geom DROP INDEX g;

• Using DROP INDEX:

DROP INDEX g ON geom;

Create a spatial index on a generated column

A generated column is a special type of column in a database table. For more information, see Generated columns.

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

• Create a spatial index on a linestring-type generated column. Other types such as POINT/POLYGON/ | MULTIPOINT/MULTILINESTRING/ | MULTIPOLYGON are also supported:

CREATE TABLE `receivable_items` (
  `from_unit` int NOT NULL,
  `to_unit` int NOT NULL,
  `unit_range` linestring GENERATED ALWAYS AS (linestring(point(-(1),`from_unit`), point(1,`to_unit`))) STORED NOT NULL srid 0,
  SPATIAL KEY `idx_unit_range` (`unit_range`)
);

• Create a spatial index on a geometry-type generated column:

CREATE TABLE `receivable_items` (
  `id` int(32) NOT NULL auto_increment,
  `geo_text` varchar(1024) NOT NULL,
  `unit_range` geometry GENERATED ALWAYS AS (st_geomfromtext(geo_text, 4326, 'axis-order=long-lat')) STORED NOT NULL srid 4326
);

• Creating a spatial index on a virtual generated column is not supported. The creation statement will return an error:

CREATE TABLE `receivable_items` (
`id` int(32) NOT NULL auto_increment,
`geo_text` varchar(1024) NOT NULL,
`unit_range` geometry GENERATED ALWAYS AS (st_geomfromtext(geo_text, 4326, 'axis-order=long-lat')) NOT NULL srid 4326,
SPATIAL KEY `idx_unit_range` (`unit_range`)
);
ERROR 3106 (HY000): 'unit_range' is not supported for generated columns.

• Creating an index after the fact is supported:

CREATE TABLE `receivable_items` (
  `id` int(32) NOT NULL auto_increment,
  `geo_text` varchar(1024) NOT NULL,
  `unit_range` geometry GENERATED ALWAYS AS (st_geomfromtext(geo_text, 4326, 'axis-order=long-lat')) STORED NOT NULL srid 4326
);

INSERT INTO receivable_items(geo_text) VALUES('point(120.34904267189361 30.320965261625222)');
INSERT INTO receivable_items(geo_text) VALUES('point(120.34904267189360 30.320965261625222)');
CREATE SPATIAL INDEX IF NOT EXISTS `idx_unit_range` ON `receivable_items` (`unit_range`);

• Partitioned table scenarios are supported:

CREATE TABLE `receivable_items` (
  `id` int(32) NOT NULL auto_increment primary key,
  `geo_text` varchar(1024) NOT NULL,
  `unit_range` geometry GENERATED ALWAYS AS (st_geomfromtext(geo_text, 4326, 'axis-order=long-lat')) STORED NOT NULL srid 4326,
  SPATIAL KEY `idx_unit_range` (`unit_range`)
) PARTITION BY hash(id) PARTITIONS 3;