Spatial Types and Indexes, SRIDs, Geometry Functions, and GIS Workloads

Learning outcomes

ServiceHub now wants to assign the closest available field technician and test whether a work site falls inside a service polygon. Storing latitude and longitude as unrelated numeric columns can support simple arithmetic, but it loses geometry type, spatial predicates and R-tree access paths. MariaDB’s geometry types and SPATIAL indexes provide a richer model—but its SRID handling must not be confused with a full coordinate-reference-system engine.

Current MariaDB documentation is explicit: ST_SRID() returns the integer attached to a geometry, but ordinary geometry calculations are performed with Euclidean/planar assumptions. ST_DISTANCE_SPHERE() is a separate function that calculates spherical distance for point/multipoint geometries. Therefore an SRID value is metadata you must validate; it does not automatically make every spatial predicate geodesic or transform coordinates.

1. Build geometry as geometry, not two unrelated numbers

USE servicehub_index_lab;CREATE TABLE technician_locations (  technician_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,  display_name VARCHAR(80) NOT NULL,  location POINT NOT NULL,  SPATIAL INDEX sp_location(location)) ENGINE=InnoDB;INSERT INTO technician_locations VALUES (1,'Ava Chen',ST_GeomFromText('POINT(49.8671 40.4093)',4326)), (2,'Mina Patel',ST_GeomFromText('POINT(49.8200 40.3777)',4326)), (3,'Noah Smith',ST_GeomFromText('POINT(49.9500 40.4500)',4326));CREATE TABLE service_zones (  zone_id INT NOT NULL PRIMARY KEY,  zone_name VARCHAR(80) NOT NULL,  boundary POLYGON NOT NULL,  SPATIAL INDEX sp_boundary(boundary)) ENGINE=InnoDB;INSERT INTO service_zones VALUES (1,'Central',ST_GeomFromText(   'POLYGON((49.78 40.35,49.93 40.35,49.93 40.46,49.78 40.46,49.78 40.35))',4326));

The POINT text above follows the conventional X/Y order. In this course we interpret X as longitude and Y as latitude because that is how the application contract is documented. MariaDB does not rescue a row whose application accidentally swaps those values. Coordinate order, units and SRID are ingestion validation responsibilities.

2. Verify SRID and geometry validity explicitly

SELECT technician_id,display_name,       ST_AsText(location) AS wkt,       ST_SRID(location) AS srid,       ST_IsValid(location) AS is_validFROM technician_locationsORDER BY technician_id;SELECT zone_id,zone_name,ST_SRID(boundary),ST_IsValid(boundary)FROM service_zones;

MariaDB 11.8 added additional geometry-validation functions such as ST_IsValid/ST_Validate. On the 12.3 baseline they are available and useful at ingestion or migration boundaries. A geometry being syntactically valid does not prove its coordinates are in the intended CRS or region; combine validity checks with application-level bounds and SRID requirements.

3. SPATIAL INDEX is an R-tree candidate path

SHOW INDEX FROM technician_locations;SHOW INDEX FROM service_zones;EXPLAINSELECT t.technician_id,t.display_nameFROM technician_locations AS tJOIN service_zones AS z ON z.zone_id=1WHERE ST_WITHIN(t.location,z.boundary);

MariaDB creates an R-tree for a SPATIAL INDEX, and indexed geometry columns must be NOT NULL. R-trees organize minimum bounding rectangles (MBRs). They are excellent for reducing a spatial search to plausible candidates, but the exact geometry relationship is still defined by the shape-aware ST_* predicate. Current MariaDB documentation distinguishes ST_WITHIN()/ST_CONTAINS(), which use object shapes, from legacy WITHIN()/CONTAINS() behavior based on bounding rectangles.

4. Deliberately wrong: assume SRID 4326 makes ST_DISTANCE return meters

SET @p1=ST_GeomFromText('POINT(49.8671 40.4093)',4326);SET @p2=ST_GeomFromText('POINT(49.8200 40.3777)',4326);SELECT ST_SRID(@p1) AS srid,       ST_DISTANCE(@p1,@p2) AS planar_coordinate_distance,       ST_DISTANCE_SPHERE(@p1,@p2) AS spherical_meters;

The SRID value is attached to the geometry, but MariaDB documentation states that geometry calculations are otherwise Euclidean/planar. With longitude/latitude degrees, ST_DISTANCE therefore produces a value in coordinate units, not an automatic geodesic meter distance. ST_DISTANCE_SPHERE explicitly models a sphere and returns meters for point/multipoint inputs. The repair is to choose a function and coordinate model whose units match the business question, and to document approximation limits.

5. Bounding boxes, exact predicates, and false positives

A bounding rectangle can overlap another rectangle even when the detailed shapes do not satisfy the exact relationship you care about. Spatial engines commonly use the index to identify candidate objects by envelopes, then evaluate an exact ST_INTERSECTS, ST_WITHIN or ST_CONTAINS predicate. This two-phase mental model explains why an index can accelerate geometry search without making the index itself the final truth test.

SELECT t.technician_id,t.display_name,       ST_WITHIN(t.location,z.boundary) AS exact_within,       WITHIN(t.location,z.boundary) AS mbr_withinFROM technician_locations AS tJOIN service_zones AS z ON z.zone_id=1ORDER BY t.technician_id;

For simple points and rectangles the answers can coincide, which is why the semantic distinction is easy to miss. Test with concave polygons, holes and edge cases before concluding the two predicates are interchangeable. Use the shape-aware ST_* functions when the business rule is geometric containment/intersection rather than envelope overlap.

6. GIS production checklist

For advanced cartography, reprojection, geography-specific operations or rich GIS tooling, evaluate whether MariaDB should remain the system of record while a dedicated GIS component performs specialized calculations. That is an architectural boundary decision, not a criticism of MariaDB spatial support.

7. Verification, cleanup, and bridge

1. Create the point/polygon tables and confirm SPATIAL index definitions.
2. Inspect WKT, SRID and validity for all rows.
3. Run exact ST_WITHIN queries and inspect the plan.
4. Compare ST_DISTANCE with ST_DISTANCE_SPHERE and write down the units/assumptions.
5. Test at least one intentionally swapped coordinate and reject it at the application/ingestion boundary.
6. Keep the database for the vector lesson.

The final lesson moves from low-dimensional geometry to high-dimensional embeddings. MariaDB vector indexes are approximate-nearest-neighbor access paths with an explicit distance metric and recall/latency tradeoff; they complement relational filtering rather than replace it.