MySQL Great Circle Distance (Haversine formula)

The Haversine formula is a mathematical formula that can be used to calculate the great-circle distance between two points on a sphere, such as the Earth. In MySQL, you can use the Haversine formula to calculate the distance between two points in latitude and longitude by using the trigonometric functions available in MySQL (e.g. SIN, COS, RADIANS) and the built-in ROUND() function to round the final result to a specified number of decimal places. An example query that calculates the distance between two points in kilometers might look like this:

An example query that calculates the distance between two points in kilometers in MySQL

SELECT ROUND(6371 * 2 * ASIN(SQRT(POWER(SIN(RADIANS(lat1 - lat2) / 2), 2) + COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * POWER(SIN(RADIANS(lon1 - lon2) / 2), 2))), 2) AS distance_km FROM locations

<div class="alert alert-info flex not-prose"> <span class="hidden md:block">Watch a video course </span> Learn object oriented PHP</div>

In this example, lat1, lat2, lon1, and lon2 are the latitude and longitude coordinates of the two points, and 6371 is the radius of the Earth in kilometers. The result of the query will be a single column named distance_km that contains the distance between the two points in kilometers, rounded to 2 decimal places. To filter nearby locations, you can add a WHERE clause, for example: WHERE distance_km < 50.

Note: The Haversine formula gives an approximate result; Vincenty's formula is considered more accurate for small distances but it's a bit more complex to implement.