How Can I Calculate the Distance Between Two Locations Using Latitude and Longitude in MySQL?

Calculating Interpoint Distance Using Latitude and Longitude in MySQL

Often, it becomes necessary to determine the distance between two locations based on their latitude and longitude coordinates. This is a common task in many scenarios, such as finding nearby amenities or calculating travel distances. To accurately compute this distance, MySQL offers a powerful solution.

Let's consider a scenario where we have a table with city names, latitude, and longitude values:

--------------------------------------------
| id  |  city  |  Latitude  |  Longitude  |
--------------------------------------------
| 1   |   3    |   34.44444 |   84.3434   |
--------------------------------------------
| 2   |   4    | 42.4666667 | 1.4666667   |
--------------------------------------------
| 3   |   5    |  32.534167 | 66.078056   |
--------------------------------------------
| 4   |   6    |  36.948889 | 66.328611   |
--------------------------------------------
| 5   |   7    |  35.088056 | 69.046389   |
--------------------------------------------
| 6   |   8    |  36.083056 |   69.0525   |
--------------------------------------------
| 7   |   9    |  31.015833 | 61.860278   |
--------------------------------------------

Suppose we want to calculate the distance between city 3 and city 7. Using the SQL query provided, we can retrieve the distance in kilometers:

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

The query leverages the spherical cosine law to calculate the distance, which is expressed in kilometers.

For efficiency and precision, it's best practice to use a modern approach, such as the following:

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

By employing suitable MySQL functions, we can accurately determine the distance between two points on the globe, enabling us to address a wide range of geolocation-related requirements.

The above is the detailed content of How Can I Calculate the Distance Between Two Locations Using Latitude and Longitude in MySQL?. For more information, please follow other related articles on the PHP Chinese website!