With the proliferation of mobile devices being at an all-time high, there has never been more demand for “Find near me” type apps. As a result, just when you thought you were finally free of trigonometric functions like sine and cosine, you find out that they were useful after all! In fact, it turns out that they can be instrumental in calculating the distance between two points. We’ll be using them today to fetch a list of nearby restaurants sorted by distance using nothing but the power of MySQL!
The Haversine Formula
The Greek mathematician Pythagoras developed his theorem for calculating the shortest distance between points way back in 5th-century B.C.
distance = sqrt((X2 - X1)^2 + (Y2 - Y1)^2)
That formula would work perfectly for distance calculations as well, if only the Earth were flat (some say it is)! Due to the curvature of the Earth, Pythagoras’s Theorem does not lend itself so well to geolocation calculations.

A more precise result may be obtained using the Haversine Formula. It takes the radius of the Earth into account. It’s the R variable below:
dlon = lon2 - lon1
dlat = lat2 - lat1
a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
c = 2 * atan2( sqrt(a), sqrt(1-a) )
distance = R * c (where R is the radius of the Earth)

R = 6367 km OR 3956 mi
Since the earth is not a perfect sphere, using a fixed value for the Radius is still not ideal, but good enough for most applications. The Earth is actually an oblate spheroid and not perfectly spherical (it bulges at the equator), so the above formula will tend to overestimate trans-polar distances and underestimate trans-equatorial distances.

Applying the Haversine Formula to MySQL
Calculating the distance between two locations using the Haversine Formula in MySQL requires us to call upon several of MySQL’s built-in Math functions, including cos(), sin(), acos(), and radians(). In case you were wondering like I was, the radians() function converts the value of a number from degrees to radians where pi radians equals 180 degrees.
3959 * acos( cos( radians(lat1) )
* cos( radians(lat2) )
* cos( radians(lon2) - radians(lon1)) + sin(radians(lat1))
* sin( radians(lat2) )

Incorporating the Haversine Formula into a Stored Procedure
For ease of use, I would recommend that you place the above formula in a stored procedure (a.k.a. a proc). Our sample proc searches a WordPress database for restaurants that are located within a given distance from the user. For the latitude and longitude parameters, I chose to use the Decimal type with six digits of precision. You may choose to adjust this for your needs. In addition to the latitude and longitude values, this proc accepts input parameters for the units of measurement (miles or kilometers), the maximum distance to search, and the maximum number of results to return.
CREATE DEFINER=`root`@`localhost` PROCEDURE `closest_restaurants`
(IN units varchar(5), IN lat Decimal(9,6), IN lon Decimal(9,6),
IN max_distance SMALLINT, IN limit_rows MEDIUMINT)
BEGIN
SELECT pm1.post_id,
p.post_title,
ROUND((CASE units WHEN 'miles'
THEN 3959
ELSE 6371
END * acos( cos( radians(lat) )
* cos( radians(pm1.meta_value) )
* cos( radians(pm2.meta_value) - radians(lon)) + sin(radians(lat))
* sin( radians(pm1.meta_value)))
), 3) AS distance
FROM goodfood_wp_md20m_postmeta AS pm1,
goodfood_wp_md20m_postmeta AS pm2,
goodfood_wp_md20m_posts AS p
WHERE pm1.meta_key = 'latitude' AND pm2.meta_key = 'longitude'
AND pm1.post_id = pm2.post_id
AND pm1.post_id = p.id
AND p.post_status = 'publish'
HAVING distance <= max_distance
ORDER BY distance ASC
LIMIT limit_rows;
END

Calling the Proc

Here is a search for the ten closest restaurants to the center of London, UK, measured in miles, within a hundred mile radius:
CALL `restaurants`.`closest_restaurants`('miles', 51.5112139, -0.119824, 100, 10);

Full article...