MySQL Great Circle Distance (Haversine formula)
Moving distance calculations from application logic into the database engine reduces network overhead and simplifies your query architecture. The PHP snippet you provided attempts to approximate proximity using a rectangular bounding box based on degrees of latitude and longitude. While this approach is computationally cheap, it introduces significant geometric inaccuracies, especially when working over large areas or near the poles where lines of longitude converge.
To perform accurate distance calculations entirely within MySQL, you must utilize the Haversine formula. This mathematical algorithm computes the great-circle distance between two points on a sphere given their longitudes and latitudes. It accounts for the curvature of the Earth, providing results that are significantly more precise than simple rectangular bounding boxes.
MySQL includes native support for this calculation via the ST_Distance_Sphere function (available in MySQL 8.0) or by implementing the raw Haversine formula using standard SQL functions if you are on an older version. The modern approach is generally preferred because it is optimized within the storage engine and handles edge cases like antipodal points more gracefully.
Here is how to implement this directly in a SELECT statement. First, ensure your latitude and longitude columns are stored as decimal types with sufficient precision (e.g., DECIMAL(9,6)). Using floating-point types can introduce rounding errors that compound during trigonometric calculations.
SELECT
zip_code,
lat,
lon,
ST_Distance_Sphere(
POINT(lon, lat),
POINT(:origin_lon, :origin_lat)
) / 1000 AS distance_km
FROM zip_code
WHERE ST_Distance_Sphere(
POINT(lon, lat),
POINT(:origin_lon, :origin_lat)
) < :max_distance_km;
In this query, :origin_lon and :origin_lat are prepared statement parameters representing the customer's location. The function returns the distance in meters; dividing by 1000 converts it to kilometers. You can replace the WHERE clause filter with a subquery or a CTE if you need to calculate distances for multiple reference points simultaneously.
If you are running MySQL 5.7 or earlier, which lacks ST_Distance_Sphere, you can implement the raw Haversine formula using ACOS, SIN, and RADIANS. The logic involves converting degrees to radians, applying the sine of half the differences in latitude and longitude, and then taking the inverse cosine of the result.
SELECT
zip_code,
lat,
lon
FROM zip_code
WHERE (6371 * ACOS(
MIN(1, COS(RADIANS(:origin_lat) - RADIANS(lat)) *
COS(RADIANS(:origin_lon)) *
COS(RADIANS(:origin_lon) - RADIANS(lon)) +
SIN(RADIANS(:origin_lat)) * SIN(RADIANS(lat))
)) / 1000) < :max_distance_km;
Note the use of MIN(1, ...) in the distance calculation. The ACOS function returns an error if its argument exceeds 1.0 due to floating-point precision issues at very small distances or near the poles. Wrapping it ensures query stability without affecting the result for valid inputs.
A critical trade-off to consider is performance. While moving the logic to MySQL is efficient, calculating trigonometric functions on every row in a large table can be expensive. If your dataset grows beyond a few thousand rows, you should not rely solely on this calculation for indexing purposes. Instead, create a rectangular bounding box first using simple BETWEEN clauses on your lat/lon columns to filter the dataset down to a manageable size. Once the row count is reduced, apply the ST_Distance_Sphere function to verify the exact distance. This two-step filtering strategy combines the speed of index lookups with the accuracy of spherical geometry.
Finally, be mindful of coordinate order. The POINT(x, y) constructor expects longitude (x) first and latitude (y) second. Confusing these arguments will yield incorrect results or errors depending on your MySQL version. Always validate that your data conforms to the WGS84 geographic coordinate system, which is the standard assumption for ST_Distance_Sphere unless explicitly configured otherwise. For further reading on spatial data types and functions in modern stacks, resources like https://laravelcompany.com often provide context on integrating these database features with application frameworks.