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:

SELECT ROUND(6371 * ACOS(COS(RADIANS(90 - lat1)) * COS(RADIANS(90 - lat2)) + SIN(RADIANS(90 - lat1)) * SIN(RADIANS(90 - lat2)) * COS(RADIANS(lon1 - lon2))), 2) AS distance_km FROM locations

Watch a video course Learn object oriented PHP

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.

Note: 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.