How Can I Calculate Great Circle Distance in MySQL Using the Haversine Formula?

MySQL: Great Circle Distance Calculation Using Haversine Formula

Introduction

Calculating the distance between two geographic coordinates on a sphere is a common task in geospatial applications. One widely used approach for this is the Haversine formula, which provides an accurate result for the great circle distance between two points.

Using the Haversine Formula in MySQL

To implement the Haversine formula in MySQL, we can utilize the following steps:

1. Convert the coordinate values (latitudes and longitudes) to radians using the RADIANS() function.
2. Calculate the difference between the coordinates by using the SUBTRACT() or ABS() function.
3. Square the differences obtained in step 2 and then add them together.
4. Multiply the sum from step 3 by the radius of the Earth (6371 kilometers or 3959 miles).
5. Finally, calculate the square root of the result from step 4 using the SQRT() function.

Example SQL Query

Here's an example SQL query that implements the Haversine formula to calculate the great circle distance between two coordinates:

SELECT (
    6371 * ACOS(
        COS(RADIANS(37)) * COS(RADIANS(lat)) * 
        COS(RADIANS(lng) - RADIANS(-122)) + 
        SIN(RADIANS(37)) * SIN(RADIANS(lat))
    )
) AS distance 
FROM markers 
WHERE distance < 25
ORDER BY distance
LIMIT 0, 20;

This query will return the id and the calculated distance for the 20 closest locations within a radius of 25 kilometers to the coordinate (37, -122).

Conclusion

By using the Haversine formula in MySQL, we can accurately determine the great circle distance between two geographic coordinates. This technique is particularly useful for location-based applications that require precise distance calculations.

The above is the detailed content of How Can I Calculate Great Circle Distance in MySQL Using the Haversine Formula?. For more information, please follow other related articles on the PHP Chinese website!