Posted inMySQL

MySQL Spatial Data: The Ultimate Guide to Handling GPS Coordinates and Finding Nearby Locations

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

The Nightmare of Manual Distance Calculation in Map Apps

When I first started my career, I was assigned a seemingly simple task: find convenience stores within a 5km radius for a delivery app. At the time, I naively created two latitude and longitude columns using the DECIMAL type. I then stuffed the entire Haversine formula, complete with a messy pile of Sin and Cos functions, into a SQL statement to calculate the distance.

The results were catastrophic. With a dataset of a few hundred records, everything was fine. But when the database hit 1 million points and the size exceeded 50GB, the system began to “choke” on default settings. Every time a user clicked “find nearby,” the server’s CPU spiked to 90%, and queries took 5-7 seconds to return results. It was a costly lesson in choosing the right data types for geographical data.

Two Common Ways to Store Coordinates: Performance or Simplicity?

When you need to store map locations, you’re usually faced with two choices:

Option 1: Storing Latitude and Longitude Separately (Numeric Type)

This is the most instinctive approach, using DECIMAL(10, 8).

  • Pros: Easy to read, easy to insert data without worrying about complex formats.
  • Cons: MySQL is forced to perform a Full Table Scan to calculate values for every single row. With a table of 1 million rows, it executes 1 million Sin/Cos calculations every time you search. The system will hit a bottleneck immediately.

Option 2: Using Spatial Data Types

MySQL provides specialized data types like POINT, LINESTRING, and POLYGON.

  • Pros: Supports SPATIAL INDEX, an accelerator that makes spatial searches incredibly fast. You also have the ST_Distance_Sphere function to calculate distances on a sphere with minimal error.
  • Cons: You need to understand SRID (coordinate systems) and slightly more specific SQL syntax.

Why Spatial Data is a Game Changer

If you’re only storing coordinates to display an address on a website, DECIMAL is enough. But if you’re building features like “Find the nearest delivery driver” or “Suggest nearby restaurants,” Spatial Data is mandatory. In a project I worked on, switching to a Spatial Index dropped latency from 2000ms to less than 10ms—a massive improvement in user experience.

Best Practices for Implementation on MySQL 8.0

Below is the most optimized way to set up a location storage table today.

1. Creating a Table with the POINT Data Type

The key lies in the location column. We use POINT along with the SRID 4326 identifier (the WGS 84 coordinate system, which is the global GPS standard).

CREATE TABLE stores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location POINT NOT NULL SRID 4326,
    SPATIAL INDEX(location)
) ENGINE=InnoDB;

Note: A Spatial Index only works if the column is marked as NOT NULL.

2. Inserting Coordinate Data

Use the ST_GeomFromText function to insert data. In MySQL 8.0 with SRID 4326, the default order is POINT(Latitude Longitude).

-- Add Landmark 81 (Lat: 10.7946, Long: 106.7218)
INSERT INTO stores (name, location)
VALUES ('Landmark 81 Store', ST_GeomFromText('POINT(10.7946 106.7218)', 4326));

-- Add Ben Thanh Market (Lat: 10.7719, Long: 106.6983)
INSERT INTO stores (name, location)
VALUES ('Ben Thanh Market Store', ST_GeomFromText('POINT(10.7719 106.6983)', 4326));

3. Finding Locations Within an X km Radius

This is the query I use most often to filter points within a 2km area. MySQL will automatically use the Index to exclude points that are too far away before performing detailed calculations. For even more control over query execution, you can explore MySQL optimizer hints.

-- User location at Bitexco (10.7715, 106.7042)
SET @user_loc = ST_GeomFromText('POINT(10.7715 106.7042)', 4326);

SELECT name, 
       ST_Distance_Sphere(location, @user_loc) AS distance
FROM stores
WHERE ST_Distance_Sphere(location, @user_loc) <= 2000
ORDER BY distance ASC;

Checking if a Location is Within a Delivery Zone (Polygon)

If you have a complex delivery zone, use ST_Contains. For example, check if a delivery driver is within a predefined District 1 area.

-- Define delivery zone using a Polygon
SET @delivery_zone = ST_GeomFromText('POLYGON((10.7 106.6, 10.8 106.6, 10.8 106.8, 10.7 106.8, 10.7 106.6))', 4326);

SELECT name FROM stores 
WHERE ST_Contains(@delivery_zone, location);

“Battle-Tested” Tips for Performance Optimization

Working with spatial data on large systems can be tricky, and you may need to diagnose and optimize MySQL to maintain performance. Here are 3 tips to help you succeed:

  1. Don’t forget the SRID: Without SRID 4326, MySQL might calculate distance in “degrees” instead of meters. The results will be completely inaccurate compared to reality.
  2. The Lat/Long order is a trap: Leaflet uses (Lat, Long), but standard GeoJSON uses (Long, Lat). Standardize this from the start to avoid a store in Ho Chi Minh City showing up in… Africa.
  3. Use a Bounding Box for massive datasets: For tables with tens of millions of rows, use ST_MakeEnvelope to create a rectangle around the location. This helps filter raw data even faster before applying ST_Distance_Sphere.

Mastering Spatial Data allows you to handle thousands of requests per second while handling thousands of database connections smoothly. I hope these insights help you feel more confident when building map-related features.

Share:
Tags: