Skip to main content

Create a spatial index

seekdb allows you to create a spatial index 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 (STORED) generated columns, but not on virtual (VIRTUAL) generated columns.

Constraints​

  • The column definition for creating a spatial index must include the NOT NULL constraint.
  • The column with a spatial index must have an SRID defined. Otherwise, the spatial index on this column will not take effect during queries.
  • If you create a spatial index on a STORED generated column, you must explicitly specify the STORED keyword in the DDL when creating the column. If neither the VIRTUAL nor STORED keyword is specified when creating a generated column, a VIRTUAL generated column is created by default.
  • After an index is created, comparisons use the coordinate system corresponding to the SRID defined in the column. Spatial indexes store the Minimum Bounding Rectangle (MBR) of geometric objects, and the comparison method for MBRs also depends on the SRID.

Preparations​

Before using the GIS feature, you need to configure GIS metadata. 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 import.
-- infile specifies the relative path of the SQL file to import.
ALTER SYSTEM LOAD MODULE DATA module=gis infile = 'etc/default_srs_data_mysql.sql';

The following result indicates that the data file was successfully imported:

Query OK, 0 rows affected

Examples​

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;

Contents

Edit this page