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";

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

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 *