Create a spatial index

OceanBase Database allows you to use the SPATIAL keyword to create a spatial index during table creation. The spatial index column must be declared as NOT NULL.

Considerations

• You can create a spatial index only on columns that have a NOT NULL constraint.
• 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.
• Preferably, the column on which a spatial index is created has an SRID defined. Otherwise, the spatial index created on the column cannot take effect in a query.

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

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;