Spatial Data (GIS)

MySQL supports OpenGIS geometry types (GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION), spatial reference systems (SRIDs, e.g., 4326 = WGS 84 GPS coordinates), ST_* functions, and R-tree SPATIAL indexes o…

Concept

MySQL supports OpenGIS geometry types (GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION), spatial reference systems (SRIDs, e.g., 4326 = WGS 84 GPS coordinates), ST_* functions, and R-tree SPATIAL indexes on InnoDB.

Why It Matters

Location data is common in automotive and IoT: test-drive GPS tracks, charging-station locations, geofences for proving grounds, fleet tracking, nearest-service-center lookups.

Syntax

col POINT NOT NULL SRID 4326,
SPATIAL INDEX sp_name (col)
ST_GeomFromText('POINT(lat lon)', 4326)        -- note axis order for SRID 4326: latitude, longitude
ST_Distance_Sphere(g1, g2)                      -- meters on a sphere
ST_Distance(g1, g2)                             -- in SRID units (meters for geographic SRS in 8.0)
ST_Contains(poly, pt), ST_Within(pt, poly), ST_Buffer, ST_Area, ST_Length, ST_AsText, ST_Latitude, ST_Longitude

Example

Expected (approximate great-circle distances):

(Distances computed on a sphere; exact decimals may differ slightly by version.) Geofence check:

Axis order: for SRID 4326, MySQL 8 expects latitude first in WKT; use ST_GeomFromText(wkt, 4326, 'axis-order=long-lat') if your data is longitude-first (GeoJSON order).

Using the spatial index for "within radius" searches requires a bounding-box predicate (e.g., MBRContains(ST_Buffer(...), location)) because ST_Distance_Sphere itself cannot use the R-tree.

CREATE TABLE service_centers (
    center_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    location   POINT NOT NULL SRID 4326,
    SPATIAL INDEX sp_centers_location (location)
);

INSERT INTO service_centers (name, location) VALUES
('Pune Hinjewadi',  ST_GeomFromText('POINT(18.5913 73.7389)', 4326)),
('Pune Hadapsar',   ST_GeomFromText('POINT(18.5089 73.9260)', 4326)),
('Mumbai Andheri',  ST_GeomFromText('POINT(19.1136 72.8697)', 4326));

-- Nearest centers to a vehicle at Pune Shivajinagar (18.5308, 73.8475)
SET @here = ST_GeomFromText('POINT(18.5308 73.8475)', 4326);
SELECT name,
       ROUND(ST_Distance_Sphere(location, @here) / 1000, 1) AS km
FROM service_centers
ORDER BY km
LIMIT 2;

+----------------+------+
| name           | km   |
+----------------+------+
| Pune Hadapsar  |  8.6 |
| Pune Hinjewadi | 13.3 |
+----------------+------+

SET @proving_ground = ST_GeomFromText(
  'POLYGON((18.50 73.80, 18.50 73.95, 18.56 73.95, 18.56 73.80, 18.50 73.80))', 4326);
SELECT name FROM service_centers WHERE ST_Contains(@proving_ground, location);   -- Pune Hadapsar

Expected Result

The nearest center to Shivajinagar is Pune Hadapsar, approximately 8–9 km away.

Common Mistakes

Swapping latitude and longitude.

Mixing SRIDs (error 3033).

Expecting ST_Distance_Sphere alone to use the spatial index.

Best Practices

Always declare SRID 4326 on GPS columns (required for spatial indexes to be used).

Store raw GPS logs in time-partitioned tables; keep POINTs for queryable locations.

Interview Relevance

Low: "How do you find the nearest location in MySQL?", "What is SRID 4326?".

Practice Questions

Store a test-drive track as a LINESTRING and compute its length.

Find all vehicles whose last GPS position lies inside a geofence polygon.