How to Perform Geospatial Queries in MySQL Database

Abdul Wahab Junaid

3 years ago

How to Perform Geospatial Queries in MySQL Database

I got into geospatial queries the practical way — a client needed a “find stores near me” feature, and my first instinct was to calculate distances in application code by looping over every row. It worked fine with 200 stores. It fell apart completely once the table grew to 50,000 locations. That’s when I actually sat down and learned MySQL’s spatial data types and spatial indexes properly, and the difference was night and day — the same query went from several seconds of application-side math to a few milliseconds of database-side computation.

Here’s everything I’ve learned about doing geospatial work properly inside MySQL.

Why Geospatial Queries Need Special Handling

Latitude and longitude look like plain numbers, but distance between two points on a sphere isn’t simple subtraction — it requires trigonometric formulas (like the Haversine formula) or, better, native spatial reasoning. Doing this in application code, row by row, doesn’t scale. MySQL has built-in spatial data types and a spatial indexing system (based on R-trees) specifically to make this fast at the database level.

MySQL’s Spatial Data Types

MySQL implements the OpenGIS spatial data standard through the GEOMETRY type family:

Type	Represents
`POINT`	A single coordinate (e.g., a store location)
`LINESTRING`	A sequence of points forming a line (e.g., a delivery route)
`POLYGON`	A closed shape (e.g., a delivery zone or city boundary)
`MULTIPOINT` / `MULTILINESTRING` / `MULTIPOLYGON`	Collections of the above
`GEOMETRY`	A generic type that can hold any of the above
`GEOMETRYCOLLECTION`	A mixed collection of different geometry types

Architecture: How Spatial Indexing Works

graph TD
    A[Query: Find points within 5km] --> B[Query Optimizer]
    B --> C{Spatial Index Available?}
    C -->|Yes| D[R-Tree Spatial Index]
    D --> E[Bounding Box Filtering]
    E --> F[Exact Distance Calculation]
    C -->|No| G[Full Table Scan + Row-by-Row Calculation]
    F --> H[Result Set]
    G --> H

An R-tree groups nearby geometries into bounding boxes, then nested bounding boxes, forming a tree. This lets MySQL discard huge sections of the table instantly by checking bounding boxes before doing exact geometric calculations — very similar in spirit to how a B-tree index avoids scanning a whole table for a normal WHERE clause.

Creating a Table with Spatial Data

CREATE TABLE stores (
    store_id INT AUTO_INCREMENT PRIMARY KEY,
    store_name VARCHAR(150) NOT NULL,
    location POINT NOT NULL SRID 4326,
    SPATIAL INDEX idx_location (location)
);

SRID 4326 specifies the spatial reference system — in this case, standard WGS 84, the coordinate system used by GPS and most mapping tools. Since MySQL 8.0, spatial indexes require an SRID to be specified on the column, which is a stricter and more correct behavior than earlier versions.

Inserting Geospatial Data

INSERT INTO stores (store_name, location)
VALUES 
  ('Downtown Store', ST_GeomFromText('POINT(67.0011 24.8607)', 4326)),
  ('Airport Branch', ST_GeomFromText('POINT(67.1600 24.9008)', 4326));

Note the coordinate order convention: in POINT(X Y) using SRID 4326 with MySQL’s default axis order, it’s longitude then latitude — this trips people up constantly, so always double check with a known reference point.

Basic Geospatial Queries

Find a store’s coordinates as readable text:

SELECT store_name, ST_AsText(location) AS coordinates
FROM stores;

Output:

+------------------+---------------------------+
| store_name       | coordinates               |
+------------------+---------------------------+
| Downtown Store   | POINT(67.0011 24.8607)    |
| Airport Branch   | POINT(67.16 24.9008)      |
+------------------+---------------------------+

Find distance between two points (in meters), using a geography calculation that accounts for the Earth’s curvature:

SELECT ST_Distance_Sphere(
  ST_GeomFromText('POINT(67.0011 24.8607)', 4326),
  ST_GeomFromText('POINT(67.1600 24.9008)', 4326)
) AS distance_meters;

+------------------+
| distance_meters  |
+------------------+
| 16893.42         |
+------------------+

“Find Nearby” Queries — The Real-World Use Case

SELECT store_name, 
       ST_Distance_Sphere(location, ST_GeomFromText('POINT(67.03 24.87)', 4326)) AS distance_m
FROM stores
WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(67.03 24.87)', 4326)) <= 5000
ORDER BY distance_m
LIMIT 10;

This works, but on a large table it still evaluates the distance function for every row unless combined properly with a spatial index using a bounding-box pre-filter:

SELECT store_name,
       ST_Distance_Sphere(location, ST_GeomFromText('POINT(67.03 24.87)', 4326)) AS distance_m
FROM stores
WHERE MBRContains(
        ST_Buffer(ST_GeomFromText('POINT(67.03 24.87)', 4326), 0.05),
        location
      )
ORDER BY distance_m
LIMIT 10;

MBRContains uses the Minimum Bounding Rectangle check, which the spatial index can actually use to prune rows quickly, and then the exact ST_Distance_Sphere calculation runs only on the smaller candidate set.

Working with Polygons: Delivery Zones

CREATE TABLE delivery_zones (
    zone_id INT AUTO_INCREMENT PRIMARY KEY,
    zone_name VARCHAR(100),
    boundary POLYGON NOT NULL SRID 4326,
    SPATIAL INDEX idx_boundary (boundary)
);

INSERT INTO delivery_zones (zone_name, boundary)
VALUES (
  'Zone A',
  ST_GeomFromText(
    'POLYGON((67.00 24.80, 67.05 24.80, 67.05 24.85, 67.00 24.85, 67.00 24.80))',
    4326
  )
);

Check whether a customer’s location falls inside a delivery zone:

SELECT zone_name
FROM delivery_zones
WHERE ST_Contains(boundary, ST_GeomFromText('POINT(67.02 24.82)', 4326));

This single query replaces what used to require complex point-in-polygon math implemented manually in application code.

EXPLAIN and Spatial Index Verification

EXPLAIN SELECT store_name FROM stores
WHERE MBRContains(
  ST_Buffer(ST_GeomFromText('POINT(67.03 24.87)', 4326), 0.05),
  location
);

Look for idx_location under possible_keys/key in the output — if it’s missing, MySQL is falling back to a full table scan, usually because the WHERE clause isn’t written in a form the optimizer recognizes as spatially indexable.

Real-World Scenario: Ride-Hailing “Nearest Driver” Lookup

A workflow I’ve implemented for on-demand delivery apps: drivers periodically update their POINT location, and the app queries for the closest available drivers to a pickup point.

UPDATE drivers 
SET current_location = ST_GeomFromText('POINT(67.031 24.865)', 4326),
    last_updated = NOW()
WHERE driver_id = 452;

SELECT driver_id, 
       ST_Distance_Sphere(current_location, ST_GeomFromText('POINT(67.030 24.860)', 4326)) AS distance_m
FROM drivers
WHERE is_available = TRUE
  AND MBRContains(ST_Buffer(ST_GeomFromText('POINT(67.030 24.860)', 4326), 0.03), current_location)
ORDER BY distance_m
LIMIT 5;

At scale (hundreds of thousands of driver location updates per minute), the spatial index is what makes this feasible in real time.

Performance and Optimization Tips

Always define an explicit SRID on spatial columns — mixing SRIDs in comparisons causes errors or incorrect results.
Use ST_Distance_Sphere (or ST_Distance_Sphere with the geography-aware calculation) rather than naive Euclidean ST_Distance when working with real-world latitude/longitude, since the Earth isn’t flat.
Combine an MBRContains/ST_Buffer bounding-box pre-filter with the exact distance function so the spatial index can actually be used before the expensive precise calculation runs.
For very high-throughput proximity search (millions of points, frequent updates), consider whether a dedicated geospatial system (like a specialized search engine) alongside MySQL is more appropriate — MySQL’s spatial support is solid but not infinitely scalable for extreme workloads.

Security Considerations

Validate any user-submitted coordinates before inserting — malformed WKT strings passed directly from user input without validation can cause application errors or unexpected query behavior.
Be mindful of location data as sensitive personal information; apply the same access controls and retention policies you’d apply to any other personally identifiable data.

Troubleshooting Common Issues

“A geometry of unsupported type was returned” errors. Usually caused by mismatched geometry types (e.g., trying spatial functions meant for polygons on a POINT column) or SRID mismatches between compared geometries.

Spatial index not being used. Confirm the WHERE clause uses a function the optimizer recognizes for spatial index usage (MBRContains, MBREquals, ST_Contains in supported forms) rather than only a raw distance function, which may not trigger the index.

Coordinates appear reversed on a map. This is almost always a longitude/latitude ordering mix-up — double check against a known landmark’s coordinates.

Frequently Asked Questions

Do I need a separate GIS-specific database instead of MySQL? Not necessarily. MySQL’s spatial support (since 5.7, improved significantly in 8.0) covers most business use cases like store locators, delivery zones, and proximity search. Dedicated GIS databases like PostGIS offer a deeper function library for advanced geographic analysis, which matters more for specialized mapping/GIS applications.

What SRID should I use? 4326 (WGS 84) is the standard for GPS coordinates and is almost always the right default unless you have a specific reason to use a different coordinate system.

Can I index multiple spatial columns in one table? Yes, each spatial column can have its own SPATIAL INDEX.

Is ST_Distance or ST_Distance_Sphere more accurate? ST_Distance_Sphere accounts for the Earth’s curvature and gives results in real-world meters, which is what you want for latitude/longitude data. Plain ST_Distance treats coordinates as flat Cartesian points, which is inaccurate over real-world distances.

Interview Questions

What is an R-tree, and how does it differ from a B-tree used for standard indexes?
Why does MySQL require an SRID on spatial columns used with spatial indexes?
Explain the difference between ST_Distance and ST_Distance_Sphere, and when you’d use each.
How would you optimize a “find nearby locations” query on a table with millions of rows?
What’s the difference between MBRContains and ST_Contains, and why does the distinction matter for index usage?
How would you design a schema to support both point locations and polygon delivery zones?

Summary and Key Takeaways

MySQL supports full OpenGIS spatial data types: POINT, LINESTRING, POLYGON, and their multi- variants.
Spatial indexes use R-trees to dramatically speed up proximity and containment queries.
Always set an explicit SRID (commonly 4326) and be careful about longitude/latitude ordering.
Combine bounding-box functions like MBRContains with exact distance functions to get both index usage and precision.
Real-world use cases — store locators, delivery zones, nearest-driver lookups — all follow the same underlying pattern: index-assisted filtering followed by exact geometric calculation.

Geospatial features in MySQL don’t get nearly the attention that indexing or replication get, but for any application dealing with real-world locations, they’re one of the highest-leverage features to learn properly.