Chapter 21 · Specialized MySQL Capabilities: NDB, Document Store, Spatial, and Search
Spatial Types, SRIDs, Spatial Functions, R-Tree Indexing, and GIS Workloads
Model spatial data deliberately with geometry types and SRIDs, separate geographic calculations from indexable Cartesian bounding-box searches, and verify spatial access paths with evidence.
Learning outcomes
ServiceHub needs to answer location questions: which service site is nearest to an incident, which assets fall inside a maintenance zone, and whether a point lies within a permitted region. Storing latitude and longitude as two unrelated decimals can work for simple display, but it does not encode a spatial reference system or unlock MySQL spatial operators and indexes. This lesson introduces just enough geographic information system (GIS) reasoning to use MySQL spatial features without pretending MySQL is a full GIS platform.
Distinguish geometry type, coordinate values, spatial reference system (SRS), and spatial reference identifier (SRID).
Use geographic SRID 4326 safely for location/distance values and make axis-order handling explicit.
Build a separate Cartesian spatial-index lab to demonstrate R-tree/MBR access-path behavior without making unsupported geographic-index assumptions.
Contrast minimum-bounding-rectangle predicates with exact object-shape predicates.
Use SHOW INDEX and EXPLAIN evidence before claiming a spatial index helps.
Geometry is coordinates plus a reference system
A geometry is a spatial value such as a POINT, LINESTRING, or POLYGON. Its SRS defines what coordinates mean. The numeric SRID identifies that SRS. SRID 4326 is WGS 84, commonly used for GPS-style longitude/latitude positions. SRID 0 is MySQL’s unitless Cartesian plane. Treating these as interchangeable is a correctness bug: coordinates can have the same numbers while representing different spaces.
USE servicehub_special_lab;SELECT SRS_ID,SRS_NAME,ORGANIZATION,ORGANIZATION_COORDSYS_IDFROM information_schema.ST_SPATIAL_REFERENCE_SYSTEMSWHERE SRS_ID IN (0,4326);DROP TABLE IF EXISTS service_sites_geo;CREATE TABLE service_sites_geo ( site_id BIGINT PRIMARY KEY, site_code VARCHAR(32) NOT NULL UNIQUE, location POINT NOT NULL SRID 4326) ENGINE=InnoDB;-- The option makes our input text explicitly longitude-latitude.INSERT INTO service_sites_geo VALUES(1,'BAKU-NORTH', ST_GeomFromText('POINT(49.8671 40.4093)',4326,'axis-order=long-lat')),(2,'BAKU-HARBOR', ST_GeomFromText('POINT(49.9180 40.3670)',4326,'axis-order=long-lat')),(3,'GANJA-SVC', ST_GeomFromText('POINT(46.3606 40.6828)',4326,'axis-order=long-lat'));SELECT site_code, ST_Longitude(location) AS longitude_deg, ST_Latitude(location) AS latitude_deg, ST_SRID(location) AS sridFROM service_sites_geo ORDER BY site_id;WGS 84 has an axis order defined by the SRS. MySQL geometry import/export functions permit an explicit axis-order=long-lat option, which prevents the common application mistake of assuming textual X/Y order without documenting it. For geographic points, ST_Longitude() and ST_Latitude() communicate intent more clearly than raw X/Y access.
Compute a distance and verify the units
SELECT a.site_code AS from_site, b.site_code AS to_site, ROUND(ST_Distance_Sphere(a.location,b.location)) AS distance_mFROM service_sites_geo AS aJOIN service_sites_geo AS b ON a.site_code='BAKU-NORTH' AND b.site_code='BAKU-HARBOR';-- Sanity-check that the result is positive and comfortably below 20 km.SELECT ST_Distance_Sphere(a.location,b.location) > 0 AS positive, ST_Distance_Sphere(a.location,b.location) < 20000 AS plausible_local_distanceFROM service_sites_geo AS aJOIN service_sites_geo AS b ON a.site_code='BAKU-NORTH' AND b.site_code='BAKU-HARBOR';The exact numeric result depends on the coordinates and spherical model; the acceptance test avoids publishing a fake benchmark or hand-computed “truth.” It proves basic magnitude and unit expectations. For high-precision surveying or domain-specific geodesy, validate the spatial model and functions against the required standard.
Failure case: relabel coordinates instead of transforming them
An SRID is not a decorative tag. A dangerous shortcut is to take coordinates created in one reference system and merely assign a different SRID. The two-argument form of ST_SRID() changes the geometry's SRID metadata; it does not mathematically transform the coordinates. When a real coordinate transformation is required, use ST_Transform() between supported spatial reference systems—or, better, ingest the geometry in the correct SRS from the start.
-- Create a disposable Cartesian point.SET @cart = ST_GeomFromText('POINT(49.8671 40.4093)', 0);SELECT ST_SRID(@cart), ST_AsText(@cart);-- WRONG mental model: this changes only the SRID label.SET @relabeled = ST_SRID(@cart, 4326);SELECT ST_SRID(@relabeled), ST_AsText(@relabeled);-- For supported real SRS conversions, ST_Transform changes coordinates too.-- Prefer creating WGS84 input correctly in the first place:SET @baku = ST_GeomFromText( 'POINT(49.8671 40.4093)', 4326, 'axis-order=long-lat');SELECT ST_SRID(@baku), ST_Longitude(@baku), ST_Latitude(@baku);This example intentionally does not claim that arbitrary SRID 0 coordinates can be meaningfully transformed into WGS84; SRID 0 is unitless Cartesian space and lacks the geographic reference needed for such a conversion. The repair is to know the source SRS, then either construct the geometry correctly or transform from a defined source SRS. Also make axis order explicit at ingest boundaries so longitude/latitude swaps are caught as data-quality failures rather than becoming plausible-looking wrong locations.
R-tree indexing is easiest to teach with a Cartesian region lab
In MySQL 8.4, a SPATIAL INDEX on InnoDB uses an R-tree based on minimum bounding rectangles (MBRs). To make index semantics unambiguous, this lab uses SRID 0 Cartesian service-yard coordinates in meters. The geographic site table remains available for real-world distance examples.
DROP TABLE IF EXISTS yard_objects;CREATE TABLE yard_objects ( object_id BIGINT PRIMARY KEY, object_name VARCHAR(80) NOT NULL, footprint POLYGON NOT NULL SRID 0, SPATIAL INDEX sx_footprint (footprint)) ENGINE=InnoDB;INSERT INTO yard_objects VALUES(1,'Pump Bay',ST_GeomFromText('POLYGON((0 0,0 20,30 20,30 0,0 0))',0)),(2,'Battery Store',ST_GeomFromText('POLYGON((40 0,40 15,55 15,55 0,40 0))',0)),(3,'Robot Cell',ST_GeomFromText('POLYGON((10 30,10 50,35 50,35 30,10 30))',0));SHOW CREATE TABLE yard_objects\GSHOW INDEX FROM yard_objects;SET @zone = ST_GeomFromText( 'POLYGON((5 -5,5 25,35 25,35 -5,5 -5))',0);EXPLAIN FORMAT=TREESELECT object_id,object_nameFROM yard_objectsWHERE MBRIntersects(footprint,@zone);SELECT object_id,object_nameFROM yard_objectsWHERE MBRIntersects(footprint,@zone)ORDER BY object_id;The result should include the Pump Bay and exclude objects whose bounding rectangles do not meet the zone. With only three rows, the optimizer may rationally choose a table scan; that does not mean R-trees are ineffective. Small-table plan choices are not production performance evidence.
MBR predicates and exact shape predicates answer different questions
An MBR predicate asks about bounding rectangles. An exact spatial predicate such as ST_Intersects() asks about the actual object shapes. For complex polygons, an MBR can overlap even when exact shapes do not. A common pattern is an index-friendly bounding-box prefilter followed by an exact predicate when exact geometry correctness is required.
SET @point_inside = ST_GeomFromText('POINT(12 10)',0);SET @point_outside = ST_GeomFromText('POINT(80 80)',0);SELECT object_name, MBRContains(footprint,@point_inside) AS mbr_contains, ST_Contains(footprint,@point_inside) AS exact_containsFROM yard_objectsWHERE object_id=1;SELECT object_name, MBRContains(footprint,@point_outside) AS mbr_contains, ST_Contains(footprint,@point_outside) AS exact_containsFROM yard_objectsWHERE object_id=1;For a rectangle these results align. The distinction becomes important for irregular shapes. Do not substitute MBR logic for exact legal, safety, or geofencing boundaries without proving that the approximation is acceptable.
Tempting but ineffective tuning: add an ordinary B-tree on coordinate text
A learner may serialize a point to text, index the text, and expect spatial region searches to improve. A lexical B-tree over 'POINT(...)' does not encode geometric proximity or overlap. Another weak approach is to add a spatial index but keep predicates that cannot use it. The correction is to choose the spatial type, SRID restriction, index, and MBR predicate that match the access pattern, then inspect the plan.
SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,CARDINALITYFROM information_schema.STATISTICSWHERE TABLE_SCHEMA='servicehub_special_lab' AND TABLE_NAME='yard_objects';EXPLAIN FORMAT=TREESELECT object_id,object_nameFROM yard_objectsWHERE MBRIntersects(footprint,@zone);-- For a representative large staging dataset, compare before/after-- plans and EXPLAIN ANALYZE evidence under the same conditions.-- Do not publish timings from this three-row teaching table.Production judgment
| Need | MySQL spatial fit | Boundary warning |
|---|---|---|
| store validated locations | strong | standardize SRID/axis conventions at ingestion |
| nearest/simple distance | useful | define accuracy and units explicitly |
| region/containment OLTP queries | useful with correct spatial design | MBR and exact predicates are not interchangeable |
| rich cartography/routing/network analysis | limited relative to GIS platforms | dedicated GIS services may be justified |
| massive geospatial analytics | evaluate carefully | specialized spatial/analytical engines may scale/operate better |
Knowledge check
- What does SRID identify?
- Why use axis-order=long-lat in the WGS84 inserts?
- What structure does MySQL use for SPATIAL indexes on InnoDB?
- Does MBRIntersects prove exact polygon intersection?
- Why can a tiny table ignore a spatial index?
Reveal answers
- The spatial reference system in which geometry coordinates are defined.
- To make the application input convention explicit instead of depending silently on SRS axis order.
- An R-tree over minimum bounding rectangles.
- No. It compares bounding rectangles; exact predicates test object shapes.
- The optimizer may estimate a scan is cheaper; a three-row lab is not a performance benchmark.