Geospatial Data Handling Made Easy with MySQL 8.0

Most MySQL shops store latitude and longitude as plain DECIMAL(9,6) columns and compute distances with the Haversine formula in application code — a pattern that works until the moment it doesn't, when 50,000 location records turn into 5 million and every "find nearby" query starts doing a full table scan. MySQL 8.0 ships a complete spatial stack: proper geometry types, a SPATIAL INDEX that prunes the search space before a single row is read, and more than 70 ST_ functions covering everything from containment checks to geodesic distance calculations. The tooling has been production-ready since 8.0.4, but adoption has lagged because the surface area looks unfamiliar to engineers who have never worked with a GIS system. This guide closes that gap with real schema definitions, real queries, and a frank assessment of where MySQL spatial ends and PostGIS begins.

Spatial Data Types in MySQL 8.0

MySQL implements the OGC Simple Features specification. Every spatial value belongs to a type hierarchy rooted at GEOMETRY, which means a column declared as GEOMETRY can store any of the concrete subtypes interchangeably — useful when a single column must hold mixed shapes imported from an external source.

The Six Core Types

• POINT — A single coordinate pair. Use it for business locations, device GPS positions, delivery addresses, or any entity representable as a dot on a map.
• LINESTRING — An ordered sequence of points. Ideal for routes, road segments, pipelines, and any geometry where direction or length matters.
• POLYGON — A closed ring (or rings for holes). Use it for geofences, delivery zones, administrative boundaries, and parcel shapes. The exterior ring must close (first and last point identical) and follow a specific winding order under SRID 4326.
• MULTIPOINT, MULTILINESTRING, MULTIPOLYGON — Collections of the respective simple types. A city with multiple non-contiguous land parcels is a natural MULTIPOLYGON.
• GEOMETRYCOLLECTION — A heterogeneous collection. Used when a single entity has mixed geometry components, though this is rare in normalised schemas.

Creating Spatial Tables and SPATIAL Indexes

Step 1: Define a Table with SRID-Enforced Geometry

CREATE TABLE locations (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name        VARCHAR(255)    NOT NULL,
  category    VARCHAR(64)     NOT NULL,
  coordinates POINT           NOT NULL SRID 4326,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  SPATIAL INDEX idx_coordinates (coordinates)
) ENGINE=InnoDB;

The SRID 4326 clause binds the column to the WGS84 coordinate reference system — the same system used by GPS receivers and every major mapping API. MySQL will reject any insert that supplies a geometry with a different SRID, preventing silent unit mismatches that corrupt distance calculations.

Step 2: Insert Rows Using ST_GeomFromText

-- WGS84 POINT uses (longitude, latitude) order — note this is NOT (lat, lon)
INSERT INTO locations (name, category, coordinates) VALUES
  ('Taj Mahal Hotel',    'hotel',      ST_GeomFromText('POINT(72.8329 18.9220)',  4326)),
  ('Gateway of India',  'landmark',   ST_GeomFromText('POINT(72.8347 18.9218)',  4326)),
  ('CST Railway Station','transport',  ST_GeomFromText('POINT(72.8352 18.9398)',  4326)),
  ('Elephanta Caves',   'attraction', ST_GeomFromText('POINT(72.9315 18.9633)',  4326));

Step 3: Store Polygons for Geofence Zones

CREATE TABLE delivery_zones (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  label    VARCHAR(100) NOT NULL,
  boundary POLYGON      NOT NULL SRID 4326,
  PRIMARY KEY (id),
  SPATIAL INDEX idx_boundary (boundary)
) ENGINE=InnoDB;

INSERT INTO delivery_zones (label, boundary) VALUES (
  'South Mumbai Zone A',
  ST_GeomFromText(
    'POLYGON((72.820 18.895, 72.860 18.895, 72.860 18.950, 72.820 18.950, 72.820 18.895))',
    4326
  )
);

Polygon rings must be explicitly closed (repeat the first coordinate at the end) and under SRID 4326 the exterior ring must be counter-clockwise. MySQL 8.0.4+ enforces this automatically when strict SQL mode is active.

Core ST_ Functions

ST_Distance_Sphere: Proximity Searches at Scale

The canonical "find locations within N kilometres" query uses ST_Distance_Sphere, which computes the great-circle distance in metres between two points on a sphere approximating Earth's surface. It is fast, accurate to within 0.5% for distances under 1,000 km, and requires no client-side trigonometry.

-- Find all locations within 3 km of a given GPS coordinate
SET @origin = ST_GeomFromText('POINT(72.8347 18.9218)', 4326);
SET @radius_m = 3000; -- 3 kilometres

SELECT
  id,
  name,
  category,
  ROUND(ST_Distance_Sphere(coordinates, @origin)) AS distance_m
FROM locations
WHERE ST_Within(
        coordinates,
        ST_Buffer(@origin, @radius_m / 111320)  -- approximate degree buffer
      )
  AND ST_Distance_Sphere(coordinates, @origin) <= @radius_m
ORDER BY distance_m;

The two-stage filter matters for performance. The ST_Within predicate operates on minimum bounding rectangles and uses the SPATIAL INDEX to eliminate the majority of rows before ST_Distance_Sphere is ever evaluated. Calling ST_Distance_Sphere alone in a WHERE clause forces a full table scan regardless of the index.

ST_Within and ST_Contains: Point-in-Polygon Checks

-- Is a given delivery address inside any of our delivery zones?
SET @customer = ST_GeomFromText('POINT(72.835 18.920)', 4326);

SELECT label
FROM delivery_zones
WHERE ST_Within(@customer, boundary);

-- ST_Contains is the converse: does the zone contain the point?
SELECT label
FROM delivery_zones
WHERE ST_Contains(boundary, @customer);

ST_Within(A, B) returns true when geometry A lies completely inside geometry B. ST_Contains(A, B) returns true when A contains B — these are inverses. Both exploit the SPATIAL INDEX on the polygon column via MBR pre-filtering.

ST_Intersects and ST_Buffer

-- Find all delivery zones that overlap a 500m buffer around a new warehouse
SET @warehouse = ST_GeomFromText('POINT(72.840 18.930)', 4326);

SELECT dz.label
FROM delivery_zones dz
WHERE ST_Intersects(
        dz.boundary,
        ST_Buffer(@warehouse, 500 / 111320)
      );

ST_Buffer expands a geometry outward by a specified distance (in the CRS units — degrees for SRID 4326). ST_Intersects returns true when the two geometries share any point, making this pattern useful for conflict detection, coverage analysis, and impact-radius queries.

Importing Geospatial Data from GeoJSON

Most external geospatial data arrives as GeoJSON. MySQL 8.0 provides ST_GeomFromGeoJSON to parse GeoJSON geometry strings directly in SQL, eliminating the need for a separate ETL conversion step.

-- Load a GeoJSON point from an application payload
INSERT INTO locations (name, category, coordinates)
VALUES (
  'New Office',
  'office',
  ST_GeomFromGeoJSON('{"type":"Point","coordinates":[72.8777,19.0760]}')
);

-- For bulk import from a staging table containing raw GeoJSON strings
INSERT INTO locations (name, category, coordinates)
SELECT
  name,
  category,
  ST_SRID(ST_GeomFromGeoJSON(geojson_column), 4326)
FROM staging_geojson_import;

For large GeoJSON feature collections (city-wide datasets, national boundaries), load the raw JSON into a staging JSON column first, then convert in batches using a stored procedure. This avoids long-running single transactions that hold InnoDB locks during the geometry parsing phase.

Performance: How SPATIAL INDEX Works and Where It Breaks

R-Tree Storage on InnoDB

MySQL's SPATIAL INDEX is an R-Tree, a hierarchical bounding-box structure that partitions space into nested rectangles. When a query provides a spatial predicate, MySQL walks the R-Tree top-down, pruning subtrees whose minimum bounding rectangles do not intersect the query envelope. Leaf nodes reference heap row pointers, so the optimizer fetches only the rows whose MBRs overlap the query region.

Critical constraints on SPATIAL INDEX behaviour:

• Only InnoDB and MyISAM storage engines support SPATIAL INDEX. For InnoDB (the default), the indexed column must be NOT NULL.
• Only MBR-based predicates exploit the index: ST_Within, ST_Contains, ST_MBRContains, ST_MBRIntersects, ST_Overlaps, ST_Crosses, ST_Touches.
• ST_Distance and ST_Distance_Sphere alone will not trigger SPATIAL INDEX usage. They require a full scan of every row to compute exact distances.
• SPATIAL INDEX columns cannot be part of a composite (multi-column) index. Index selectivity is determined entirely by the geometry envelope.

-- Confirm the query uses the SPATIAL INDEX
EXPLAIN SELECT id, name
FROM locations
WHERE ST_Within(
        coordinates,
        ST_Buffer(ST_GeomFromText('POINT(72.835 18.920)', 4326), 0.027)
      )\G
-- key: idx_coordinates  (confirms R-Tree is in use)
-- rows: << full table count

MySQL Spatial vs PostGIS

MySQL 8.0 spatial is a credible production tool for the most common use cases. PostGIS (the PostgreSQL extension) is the industry reference implementation for serious geospatial work. Understanding the boundary helps engineering teams make the right call before investing in schema design.

The practical decision rule: if your workload is proximity search, geofencing, delivery zone assignment, or basic route storage, MySQL 8.0 spatial is sufficient and keeps your stack homogeneous. If you need network routing, terrain analysis, complex overlay operations, coordinate reprojection on the fly, or more than 75 spatial functions, migrate to PostGIS. The schema migration path is straightforward — the OGC WKT format used by ST_AsText is portable between both systems.

Working with JusDB on MySQL Geospatial

JusDB manages MySQL and PostgreSQL infrastructure for engineering teams running location-aware products — from ride-hailing and logistics platforms to real-estate search and field-service dispatch. Our DBAs handle schema design for spatial workloads, SPATIAL INDEX tuning, proximity query optimization, and the full pipeline from raw GeoJSON imports to production-ready spatial tables. If your "find nearby" queries are slower than they should be, or you are evaluating whether to stay on MySQL spatial or migrate to PostGIS, we can run a no-obligation query analysis against your actual dataset. Reach out at jusdb.com.

Explore JusDB MySQL DBA Services →  |  Talk to a DBA