SQL SPATIAL

What is the SQL SPATIAL keyword?

Declares a spatial index on geometry columns to speed up spatial queries.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SPATIAL:

SQL SPATIAL Full Explanation

The SPATIAL keyword creates a special index optimized for two-dimensional geometric data (POINT, LINESTRING, POLYGON, MULTIPOLYGON, etc.). In MySQL and MariaDB it can appear in CREATE TABLE, ALTER TABLE, or CREATE INDEX statements. The index stores minimum bounding rectangles (MBRs) and lets the optimizer use R-tree search, drastically reducing the number of rows examined by spatial functions such as ST_Within, ST_Distance, or MBRContains. Key points: - Allowed only on columns of spatial types defined by OpenGIS. - Columns must be NOT NULL in most engines. - InnoDB requires the column to have an SRID and supports only one geometry column per table with a SPATIAL index prior to MySQL 8.0. - Unlike regular indexes, no length prefix is permitted. - A spatial index is ignored when the query uses functions that cannot be resolved with MBRs (e.g., exact-shape predicates without && operator). - In SQL Server and PostgreSQL the same concept exists but is implemented with CREATE SPATIAL INDEX (SQL Server) or GiST/BRIN indexes (PostGIS). The SPATIAL keyword itself is MySQL-specific.

SQL SPATIAL Syntax

-- Stand-alone
CREATE SPATIAL INDEX index_name ON table_name (geometry_column);

-- Inside CREATE TABLE
CREATE TABLE table_name (
  id INT PRIMARY KEY,
  geom GEOMETRY NOT NULL,
  SPATIAL INDEX idx_geom (geom)
);

SQL SPATIAL Parameters

index_name (identifier) - Name of the spatial index
------------ (------------) - ---------------------------------------
table_name (identifier) - Table that owns the geometry column
geometry_column (column) - Column of a spatial data type to be indexed

Example Queries Using SQL SPATIAL

-- 1. Add a spatial index to an existing table
ALTER TABLE cities
  ADD SPATIAL INDEX idx_city_location (location);

-- 2. Create a new table with a spatial index
CREATE TABLE parks (
  park_id INT PRIMARY KEY,
  boundary POLYGON NOT NULL SRID 4326,
  SPATIAL INDEX (boundary)
);

-- 3. Query that benefits from the index
SELECT park_id
FROM parks
WHERE ST_Within(POINT(-73.98, 40.75), boundary);

Expected Output Using SQL SPATIAL

The index is built immediately (or online if supported)
Subsequent spatial predicates on the indexed column use the R-tree index, returning results faster and with fewer examined rows

Use Cases with SQL SPATIAL

Accelerating point-in-polygon lookups in mapping applications
Filtering candidates before precise distance calculations in ride-sharing apps
Speeding up geofencing checks for IoT devices
Improving performance of bounding-box searches in GIS dashboards

Common Mistakes with SQL SPATIAL

Indexing non-spatial columns with SPATIAL keyword
Using SPATIAL on nullable geometry columns (ignored by engine)
Forgetting to set SRID for InnoDB, causing index creation failure
Expecting the index to help with functions that require exact shape comparison, not MBR checks

First Introduced In

MySQL 4.1 (MyISAM), InnoDB support added in MySQL 5.7

Frequently Asked Questions

What storage engines support SPATIAL indexes in MySQL?

MyISAM has supported SPATIAL indexes since MySQL 4.1. InnoDB gained full support in MySQL 5.7, provided the geometry column is NOT NULL and has an SRID.

Can I create multiple SPATIAL indexes on the same table?

Yes, as long as each one targets a different geometry column. You cannot add two SPATIAL indexes to the same column.

How can I verify my query uses the SPATIAL index?

Run EXPLAIN on the statement. The Extra column should include "Using MBR" or list the spatial index name, indicating it is leveraged.

Does a SPATIAL index help with ST_Distance calls?

It speeds up candidate selection by bounding box, but the engine still performs exact distance calculations on the filtered set.