How Can I Calculate Great Circle Distance Using MySQL's Haversine Formula?

MySQL-Based Great Circle Distance Calculation (Haversine Formula)

To calculate the great circle distance between two geographic points on the Earth's surface, the Haversine formula can be employed. This formula requires the longitude and latitude of both points.

In this instance, you aim to perform this calculation entirely within MySQL, eliminating the need for PHP. To achieve this, consider the following SQL statement inspired by the Google Code FAQ:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance
FROM markers
HAVING distance < 25
ORDER BY distance
LIMIT 0, 20;

This statement assumes a starting point of latitude 37 and longitude -122 and retrieves the closest 20 locations within a radius of 25 miles. It utilizes the Haversine formula to calculate the distance between each point and the starting point. By modifying the latitude and longitude values accordingly, you can tailor the query to your specific requirements.

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