How to Create Spatial Index in MySQL: Syntax and Example

To create a spatial index in MySQL, use the CREATE SPATIAL INDEX statement on a column with a spatial data type like GEOMETRY. This index improves spatial query performance by organizing spatial data efficiently.

Syntax

The syntax to create a spatial index in MySQL is:

• CREATE SPATIAL INDEX index_name ON table_name (spatial_column);
• index_name: Name you give to the spatial index.
• table_name: The table containing the spatial column.
• spatial_column: The column with spatial data type (e.g., GEOMETRY, POINT).

This command creates a spatial index to speed up spatial queries on the specified column.

sql

CREATE SPATIAL INDEX index_name ON table_name (spatial_column);

Example

This example shows how to create a table with a POINT column and add a spatial index to it.

The spatial index helps MySQL quickly find points within a certain area.

sql

CREATE TABLE places (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  location POINT NOT NULL,
  SPATIAL INDEX(location)
) ENGINE=InnoDB;

-- Insert sample data
INSERT INTO places (name, location) VALUES
('Park', ST_GeomFromText('POINT(10 20)')),
('Museum', ST_GeomFromText('POINT(15 25)'));

-- Query using spatial index
SELECT name FROM places WHERE ST_Contains(ST_Buffer(location, 10), location);

Output

name
-----
Park
Museum

Common Pitfalls

Common mistakes when creating spatial indexes include:

• Trying to create a spatial index on a non-spatial column (must be GEOMETRY or its subtypes).
• Using storage engines that do not support spatial indexes (only InnoDB and MyISAM support them).
• Not defining the column as NOT NULL, which is required for spatial indexes.

Example of a wrong and right way:

sql

-- Wrong: spatial index on VARCHAR column
CREATE TABLE wrong_table (
  id INT PRIMARY KEY,
  location VARCHAR(100),
  SPATIAL INDEX(location)
);

-- Right: spatial index on POINT column
CREATE TABLE right_table (
  id INT PRIMARY KEY,
  location POINT NOT NULL,
  SPATIAL INDEX(location)
) ENGINE=InnoDB;

Quick Reference

Command	Description
CREATE SPATIAL INDEX index_name ON table_name (column);	Creates a spatial index on a spatial column.
SPATIAL INDEX(column)	Defines a spatial index inline during table creation.
ST_GeomFromText('POINT(x y)')	Creates a spatial point from text representation.
Column must be NOT NULL	Spatial indexes require the column to be NOT NULL.
Supported engines: InnoDB, MyISAM	Only these storage engines support spatial indexes.

Key Takeaways

Use CREATE SPATIAL INDEX on columns with spatial data types like POINT or GEOMETRY.

Spatial indexes require the column to be NOT NULL and supported storage engines.

Spatial indexes improve performance of spatial queries like distance or containment checks.

Define spatial indexes inline during table creation or with CREATE SPATIAL INDEX after.

Avoid creating spatial indexes on non-spatial columns or unsupported storage engines.