How Can I Calculate Great Circle Distance Using Only MySQL?

MySQL Great Circle Distance (Haversine Formula)

This article addresses the challenge of calculating the great circle distance between two geographic coordinates using solely MySQL. The traditional PHP implementation, involving multiple SQL queries and complex calculations, can be cumbersome.

Solution

To convert the PHP code into pure MySQL, we leverage the Haversine formula, a mathematical equation that calculates the distance between two points on a sphere. This formula is supported by the MySQL ACOS() and RADIANS() functions.

Here's the corresponding MySQL query:

SELECT id, (
  3959 * ACOS(
    COS(RADIANS(37)) * COS(RADIANS(lat))
    * COS(RADIANS(lon) - RADIANS(-122)) + SIN(RADIANS(37)) * SIN(RADIANS(lat))
  )
) AS distance
FROM markers
HAVING distance < 25
ORDER BY distance
LIMIT 0, 20;

Explanation

• The query selects the id and calculated distance from the markers table.
• The ACOS() function calculates the angle between the two points, which is then multiplied by 3959 (radius of the Earth in miles or 6371 for kilometers).
• The RADIANS() function converts the coordinates to radians.
• The HAVING clause filters out results where the distance is less than 25 miles (or kilometers).
• The ORDER BY clause sorts the results by distance in ascending order.
• The LIMIT clause limits the number of results to 20.

By using this MySQL-based approach, you can efficiently calculate the great circle distance between coordinates without the need for external PHP calculations or additional SQL queries.

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