Create a spatial index

seekdb allows you to create a spatial index by using the SPATIAL keyword. When you create a table, the column for which you want to create a spatial index 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.

Considerations

• The column for which you want to create a spatial index must be declared as NOT NULL.
• The column for which you want to create a spatial index must have an SRID defined. Otherwise, the spatial index created on the column cannot be used in queries.
• If you want to create a spatial index on a 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 a generated column, a virtual generated column is created by default.
• After you create an index, the spatial index uses the coordinate system corresponding to the SRID defined in the column definition for comparisons. A spatial index is built based on the MBR (minimum bounding rectangle) of a geometry object. The MBR comparison also depends on the SRID.

Prerequisites

Before you use the GIS features, you must configure the GIS meta data. After you connect to the server, run the following command to import the default_srs_data_mysql.sql file into the database:

## $password must be replaced with the password for logging in to the database.
## $localhost must be replaced with the IP address for logging in to the database.
## $tenant_name must be replaced with the tenant name for logging in to the database.
## $port must be replaced with the port number for 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 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:

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;

Create a spatial index on a generated column

A generated column is a special column in a database table. For more information, see Operations on generated columns.

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

• Create a spatial index on a generated column of the LINESTRING type. Other types, such as POINT, POLYGON, MULTIPOINT, MULTILINESTRING, and 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 generated column of the GEOMETRY type:

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

• You cannot create a spatial index on a virtual generated column. If you attempt to do so, an error is returned:

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 an index after a spatial index 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 create a spatial index on 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;