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

MySQL Great Circle Distance Using Haversine Formula

Calculating great circle distance, also known as Haversine distance, in MySQL can be achieved through the following steps:

1. Obtain Longitude and Latitude Values

First, retrieve the longitude and latitude coordinates from the relevant data source (e.g., a PHP script).

2. Calculate Distance using Haversine Formula

The Haversine formula is used to calculate the great circle distance between two points on a sphere. In MySQL, the following SQL statement implements the formula:

(
    3959 * acos(
        cos( radians(origin_lat) ) * cos( radians(lat) ) * cos( radians(lon) - radians(origin_lon) ) +
        sin( radians(origin_lat) ) * sin( radians(lat) )
    )
)

• origin_lat: Origin latitude
• origin_lon: Origin longitude
• lat: Target latitude
• lon: Target longitude

3. Query Within Distance Range

To find locations within a specified distance range, use the following SQL statement:

SELECT id,
    (
        3959 * acos(
            cos( radians(origin_lat) ) * cos( radians(lat) ) * cos( radians(lon) - radians(origin_lon) ) +
            sin( radians(origin_lat) ) * sin( radians(lat) )
        )
    ) AS distance
FROM locations
HAVING distance < distance_range
ORDER BY distance
LIMIT num_results;

• locations: Table containing latitude and longitude columns
• distance_range: Maximum distance in miles (or kilometers if using 6371 constant)
• num_results: Number of results to return

4. Example SQL Query

Here's an example SQL query that can be used to find the closest 20 locations within 25 miles to the coordinates (37, -122):

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

By following these steps, you can perform great circle distance calculations entirely within MySQL using the Haversine formula.

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