MySQL Spatial Data and GIS Functions Guide

Managing geographic and location-based data is a crucial requirement for modern applications. This article provides a practical guide on how to implement spatial data types and Geographic Information System (GIS) functions in a MySQL database. You will learn how to define spatial columns, insert geographic coordinates, create spatial indexes for optimized performance, and query location data using standard MySQL GIS functions.

1. Understanding MySQL Spatial Data Types

MySQL supports several spatial data types defined by the Open Geospatial Consortium (OGC). The most commonly used types include:

• POINT: Represents a single coordinate (X, Y) or (Latitude, Longitude).
• LINESTRING: A curve path consisting of one or more line segments connected by points.
• POLYGON: A multisided area defined by a closed ring of outer boundary points (and optional inner holes).
• GEOMETRY: A generic type that can store any of the geometry types above.

Spatial Reference System (SRS)

Every spatial value in MySQL is associated with a Spatial Reference System Identifier (SRID). The most common SRID for GPS and web mapping (such as Google Maps) is SRID 4326, which represents the WGS 84 ellipsoid (using latitude and longitude).

2. Creating a Table with Spatial Columns

To store spatial data, you define a column with a spatial data type. It is recommended to enforce an SRID on the column to ensure data integrity and enable spatial indexing.

The following SQL statement creates a places table that stores a geographical point for each location:

CREATE TABLE places (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    coordinates POINT NOT NULL SRID 4326,
    SPATIAL INDEX (coordinates)
);

Note: The spatial column must be defined as NOT NULL to be included in a SPATIAL INDEX.

3. Inserting Spatial Data

You can insert spatial data using Well-Known Text (WKT) helper functions such as ST_GeomFromText.

In MySQL 8.0, when using SRID 4326, the coordinate order for a POINT is Latitude followed by Longitude.

-- Inserting a location (latitude, longitude)
INSERT INTO places (name, coordinates) 
VALUES (
    'Empire State Building', 
    ST_GeomFromText('POINT(40.7484 -73.9857)', 4326)
);

INSERT INTO places (name, coordinates) 
VALUES (
    'Central Park', 
    ST_GeomFromText('POINT(40.7829 -73.9654)', 4326)
);

4. Querying and Using GIS Functions

MySQL provides a robust library of ST_ (Spatial-Temporal) functions to analyze and query spatial data.

Calculate Distance Between Points

To calculate the distance between two geographical points on the Earth’s surface, use ST_Distance_Sphere. This returns the distance in meters.

-- Find the distance in meters between the Empire State Building and Central Park
SELECT ST_Distance_Sphere(
    (SELECT coordinates FROM places WHERE name = 'Empire State Building'),
    (SELECT coordinates FROM places WHERE name = 'Central Park')
) AS distance_meters;

Find Locations Within a Radius

You can search for locations within a specific distance from a given point. The following query finds all places within 5,000 meters (5 km) of a specific coordinate:

SELECT name, 
       ST_Distance_Sphere(coordinates, ST_GeomFromText('POINT(40.7500 -73.9900)', 4326)) AS distance 
FROM places
WHERE ST_Distance_Sphere(coordinates, ST_GeomFromText('POINT(40.7500 -73.9900)', 4326)) <= 5000;

Check if a Point lies Within a Polygon

To check if a location falls within a specific bounding area (like a delivery zone), use ST_Contains or ST_Within.

-- Define a polygon covering a portion of Manhattan
SET @manhattan_zone = ST_GeomFromText('POLYGON((40.70 -74.02, 40.80 -74.02, 40.80 -73.90, 40.70 -73.90, 40.70 -74.02))', 4326);

-- Find if "Central Park" is within this polygon
SELECT name, ST_Within(coordinates, @manhattan_zone) AS is_inside
FROM places
WHERE name = 'Central Park';

5. Optimizing Queries with Spatial Indexes

For large datasets, querying spatial data using functions like ST_Distance_Sphere on every row leads to slow table scans. A SPATIAL INDEX uses R-tree structures to rapidly narrow down search results.

To make use of a spatial index, use minimum bounding box functions like ST_Within or MBRContains to filter candidate rows first, and then apply precise calculations if necessary.