How to Calculate the Distance Between Two Geographic Coordinates in MySQL?

Determining Distance Between Geographic Coordinates in MySQL Using Latitude and Longitude

Your query utilizes the spherical cosine law formula to calculate the distance between one point and all others. To find the distance between two specific points, modify your query as follows:

SELECT a.city AS from_city, b.city AS to_city,
111.111 *
DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.Latitude))
* COS(RADIANS(b.Latitude))
* COS(RADIANS(a.Longitude - b.Longitude))
+ SIN(RADIANS(a.Latitude))
* SIN(RADIANS(b.Latitude))))) AS distance_in_km
FROM city AS a
JOIN city AS b ON a.id <> b.id
WHERE a.city = 3 AND b.city = 7;

This query retrieves coordinate pairs for two cities (3 and 7) and applies the formula, yielding the distance in kilometers.

For nearby points, avoid using distance-based filters as they can be slow. Instead, employ a bounding box computation technique, as described here: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/.

Modern Solution (2022)

To keep up with the latest technology, consider using the following shortened code:

SELECT ST_Distance_Sphere(
point(-87.6770458, 41.9631174),
point(-73.9898293, 40.7628267))

This query employs the ST_Distance_Sphere() function to calculate the distance between two points.

The above is the detailed content of How to Calculate the Distance Between Two Geographic Coordinates in MySQL?. For more information, please follow other related articles on the PHP Chinese website!