Create a spatial index

OceanBase Database supports creating spatial indexes using the SPATIAL keyword. When creating a table, spatial index columns must be declared as NOT NULL. Creating spatial indexes on stored generated columns is supported, but creating them on virtual generated columns is not supported.

Considerations

• You can create a spatial index only on columns that have a NOT NULL constraint.
• The column on which a spatial index is created must have an SRID defined. Otherwise, the spatial index created on the column cannot take effect in a query.
• If you create a spatial index on a stored (STORED) generated column, you must explicitly specify the STORED keyword when you create the column. If you do not specify the VIRTUAL or STORED keyword when you create the generated column, the system creates a virtual (VIRTUAL) generated column by default.
• After an index is created, the coordinate system corresponding to the spatial reference identifier (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 the MBR also depends on the SRID.

Preparations

Before you use the geographic information system (GIS) feature, you need to configure GIS metadata in the business tenant. Connect to the server and run the following command to import the default_srs_data_mysql.sql file to the database:

## Replace $password with the login password of the database.
## Replace $localhost with the IP address of the database.
## Replace $tenant_name with the tenant name of the database.
## Replace $port with the port number of the database.
$python /home/admin/oceanbase/bin/import_srs_data.py -p'$password' -h $localhost -P $port -t $tenant_name -f /home/admin/oceanbase/etc/default_srs_data_mysql.sql

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

INFO: succeed to import srs data
INFO: oceanbase.__all_spatial_reference_systems old result rows -- 1
INFO: oceanbase.__all_spatial_reference_systems new result rows -- 5152

Examples

Create a spatial index

The following examples show how to create a spatial index:

• Use the CREATE TABLE statement to create a spatial index.

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));

• Use the ALTER TABLE statement to create a spatial index.

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
ALTER TABLE geom ADD SPATIAL INDEX(g);

• Use the CREATE INDEX statement to create a spatial 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:

• Use the ALTER TABLE statement to drop a spatial index:

ALTER TABLE geom DROP INDEX g;

• Use the DROP INDEX statement to drop a spatial index:

DROP INDEX g ON geom;

Create a spatial index on a generated column

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

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

• Create a spatial index on a linestring-type generated column. Other POINT/POLYGON/ | MULTIPOINT/MULTILINESTRING/ | MULTIPOLYGON-type generated columns also support spatial indexes:

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

• Spatial indexes cannot be created on virtual generated columns. If you create a table that contains a virtual generated column and attempt to create a spatial index, the statement will fail and the following error will be reported:

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.

• You can create a spatial index on a generated column after the table is created.

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`);

• You can also create a spatial index on a generated column in a partitioned table:

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;