How to Calculate the Distance Between Two Cities Using MySQL?

Calculate Distance Between Geographic Coordinates Using MySQL

Question:

You have a table containing city data with latitude and longitude coordinates. You need to calculate the distance between two specific cities from this table. Guide us through the process.

Answer:

To calculate the distance between two points using their latitude and longitude, we can utilize the spherical cosine law formula in MySQL. Here's a detailed solution:

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

In this query, we perform a self-join on the city table to retrieve the coordinate pairs for the two cities. We then apply the spherical cosine law formula to calculate the distance between them, which is given in kilometers.

Note:

If you prefer to have the distance in statute miles instead of kilometers, replace the constant 111.111 with 69.0.

Alternatively, for a more modern approach:

As suggested by Alexio Vay, you can use the ST_Distance_Sphere() function in MySQL, which provides a concise and efficient way to calculate the distance between two points:

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

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