I’ve recently been doing some more work on my coppr|Distilleries to Visit website and have added Nearest Distilleries and Nearest Attractions sections where I show the nearest 20 distilleries or attractions to the one beign viewed.
To do this I loaded GPS co-ordinates to each page and then needed to work out how to calculate the distance between them.
Initially I found the ST_Distance MySQL function which seemed like it would do the job. A little more reading though suggested that it would calculate distance between two points on a flat surface, but ot on a (almost) sphere like the Earth.
ST_Distance_Sphere then seemed a good option. I wrote some code and uploaded the page to test and received an error that the function didn’t exist.
After doing some investigation, I spoke to my web host and discovered that they were not using MySQL, but rather used MariaDB which does not have that function.
After some more investigation on options, I came across an old post on using the Haversine formula to calculate distance. The post had sample code in PHP and MySQL with the latter being what I wanted.
The only real difference between the code on the blog and the below, is MySQL no longer allows the use of alias in a WHERE clause so I’ve changed it to a HAVING clause:
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
$sql =
"SELECT
T2.*
,ROUND((((acos(sin((T1.latitude * pi()/180)) * sin((T2.latitude * pi()/180)) + cos((T1.latitude * pi()/180)) * cos((T2.latitude * pi()/180)) * cos(((T1.longitude- T2.longitude) * pi()/180)))) * 180/pi()) * 60 * 1.1515),2) AS DISTANCE
FROM
{table1} AS T1
INNER JOIN
{table2} AS T2
ON
T2.ID = T1.ID
HAVING
DISTANCE <= 100";