How to Calculate the Distance Between Two Points Using Latitude and Longitude in MySQL?

Calculating Distance Between Two Points Using Latitude and Longitude in MySQL

To determine the distance between two points given their latitude and longitude, MySQL provides a powerful formula. In this scenario, one user is located in city 3 and seeks to find the distance to users in other cities, such as city 7.

The solution lies in utilizing the following query:

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 employs the spherical cosine law formula, which calculates the distance in kilometers between two points on a sphere. It joins the table to itself, allowing the retrieval of two coordinate pairs for computation.

The result of this query will provide the distance between city 3 and city 7 in kilometers. To obtain the distance in statute miles, simply replace the constant 111.1111 with 69.0.

For efficiency in locating nearby points, consider using the ST_Distance_Sphere function:

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

Alternatively, bounding box computation can be employed to further optimize the search.

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