MySQL 8: Calculate the distance between two locations (latitude, longitude)

Introduction

Understanding the physical distance between two geographic points, characterized by their latitude and longitude coordinates, has numerous practical applications in fields such as logistics, travel, and geospatial analysis. MySQL 8 introduces functions that facilitate these calculations directly within your database. This tutorial explores different approaches to calculate distances between two locations in MySQL 8, concentrating on both the simplicity and complexity of geospatial queries.

Understanding Geospatial Data in MySQL

The first step towards calculating distances is understanding the representation of geospatial data in MySQL. With the release of MySQL 8, the database management system offers comprehensive support for spatial data types and functions compliant with the Open Geospatial Consortium (OGC) standards.

MySQL stores spatial data using different types of geometry data types such as POINT, LINESTRING, and POLYGON. The POINT data type is particularly relevant for location data since it represents a single geographic position.

To store a geographical location, you can use the POINT data type combined with the SRID attribute, which refers to the Spatial Reference System Identifier:

CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    coordinates POINT NOT NULL SRID 4326
);

Once your table is set up, you can insert geospatial data into the locations table as follows:

INSERT INTO locations (name, coordinates) 
VALUES ('Location A', ST_SRID(Point(-73.935242, 40.730610), 4326));

INSERT INTO locations (name, coordinates) 
VALUES ('Location B', ST_SRID(Point(-74.0060, 40.7128), 4326));

Basic Distance Calculation

To calculate the distance between two points, you can use the ST_Distance_Sphere function, which returns the minimum spherical distance between two points on a sphere in meters.

SELECT
    ST_Distance_Sphere(
        pointA.coordinates, 
        pointB.coordinates
    ) as distance_in_meters
FROM
    (SELECT coordinates FROM locations WHERE name='Location A') as pointA,
    (SELECT coordinates FROM locations WHERE name='Location B') as pointB;

An example of the output might look like this:

+-------------------+
| distance_in_meters|
+-------------------+
| 8436.911314275211|
+-------------------+

This straightforward query gives you the distance between ‘Location A’ and ‘Location B’.

Advanced Distance Calculations

Sometimes you may need to perform more complex distance computations, such as finding all points within a certain radius or the nearest point to a given location. MySQL provides the ST_Distance_Sphere function to support these scenarios as well.

To find all locations within a 10-kilometer radius of a given point:

SET @given_point = ST_SRID(Point(-73.935242, 40.730610), 4326);
SET @radius = 10000; -- 10 kilometers in meters

SELECT 
    name,
    ST_Distance_Sphere(coordinates, @given_point) as distance
FROM
    locations
HAVING
    distance < @radius;

If you wish to find the nearest location to a given point, you can simply order the results by distance and limit the query to the first result:

SET @given_point = ST_SRID(Point(-73.935242, 40.730610), 4326);

SELECT 
    name,
    ST_Distance_Sphere(coordinates, @given_point) as distance
FROM
    locations
ORDER BY 
    distance
LIMIT 1;

Next, you may also be interested in integrating spatial functions in a more elaborate query, such as joining multiple tables based on geographical proximity. For example:

SELECT
    a.name as location_a,
    b.name as location_b,
    ST_Distance_Sphere(a.coordinates, b.coordinates) as distance
FROM
    locations a
JOIN
    locations b ON ST_Distance_Sphere(a.coordinates, b.coordinates) < 10000
WHERE
    a.name <> b.name;

It is worth noting that geospatial functions became much more optimized with MySQL 8, with improved performance for spatial calculations.

Handling Geographic Coordinate Systems

It’s essential to handle coordinates in a Geographic Coordinate System (GCS) correctly since the Earth is not a perfect sphere. Calculating distances using spherical functions will result in approximations. MySQL 8 provides functions that consider the ellipsoidal shape of the Earth, offering more accurate calculations.

For computations on an ellipsoid, MySQL uses ST_Distance function, which can compute the distance using the geography type introduced in MySQL 8. However, keep in mind that handling Geographic Coordinate Systems is quite an advanced topic, and the calculations can be considerably more complex and resource-intensive compared to spherical calculations.

Imagine we have two geographical locations, each defined by its latitude and longitude. We want to calculate the distance between these two points.

For our example, let’s use:

• Point 1: Latitude 40.7128° N (New York City), Longitude -74.0060° W
• Point 2: Latitude 51.5074° N (London), Longitude -0.1278° W

SQL Query:

-- Define the two points using the ST_GeomFromText function
SET @point1 = ST_GeomFromText('POINT(-74.0060 40.7128)');
SET @point2 = ST_GeomFromText('POINT(-0.1278 51.5074)');

-- Calculate the distance between the two points on an ellipsoid (Earth)
SELECT ST_Distance(@point1, @point2, 'ellipsoid') AS distance_meters;

Here:

• Point Definition: We use the ST_GeomFromText function to define the two points. The POINT data type is used with longitude and latitude values. The format for the POINT is POINT(longitude latitude).
• Calculating Distance: The ST_Distance function is used to calculate the distance between these two points. In MySQL 8.0, this function can consider the Earth’s ellipsoidal shape when given the 'ellipsoid' argument. This results in a more accurate distance measurement compared to spherical models.
• Result: The distance_meters value returned by the query is the distance between the two points in meters, considering the Earth’s ellipsoidal shape.

Conclusion

Whether you need a simple point-to-point distance or complex geospatial analysis, MySQL 8 readily provides the tools necessary to make those calculations with ease. Utilizing geospatial data types and functions, you’re empowered to implement sophisticated mapping features and location-based services directly through SQL queries.