Calculate distance using longitude and latitude on MariaDB

MariaDBI’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 a Dynamics GP Consultant (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";

What should we write about next?





Your Name (required) –
Your Email (required) –

1 thought on “Calculate distance using longitude and latitude on MariaDB

Leave a Reply

Your email address will not be published. Required fields are marked *