ST_Distance_Sphere() is a built in function in MySQL which calculates the distance between two points on a sphere, taking as parameters two
Unfortunately MariaDB doesn’t include ST_Distance_Sphere(), and if you try using it you’ll see:
SQL Error (1305): FUNCTION ST_Distance_Sphere does not exist
But it’s not too difficult to create something equivalent.
The Haversine formula will calculate the distance between two points on a sphere with a given radius and looks like this:
Translating this into MariaDB, we can define our own ST_Distance_Sphere() as so:
DELIMITER $$ DROP FUNCTION IF EXISTS st_distance_sphere; CREATE FUNCTION st_distance_sphere(pt1 POINT, pt2 POINT) RETURNS FLOAT BEGIN DECLARE lat1 FLOAT; DECLARE lat2 FLOAT; DECLARE lng1 FLOAT; DECLARE lng2 FLOAT; DECLARE dlat FLOAT; DECLARE dlng FLOAT; DECLARE R FLOAT; DECLARE A FLOAT; DECLARE RADIAN FLOAT; SET R = 6371000; SET RADIAN = PI() / 180; SET lat1 = ST_X(pt1) * RADIAN; SET lat2 = ST_X(pt2) * RADIAN; SET lng1 = ST_Y(pt1) * RADIAN; SET lng2 = ST_Y(pt2) * RADIAN; SET dlat = lat2 - lat1; SET dlng = lng2 - lng1; SET A = SIN(dlat / 2) * SIN(dlat / 2) + COS(lat1) * COS(lat2) * SIN(dlng / 2) * SIN(dlng / 2); RETURN R * 2 * ASIN(SQRT(A)); END$$
The only thing to watch out for here is the hard coded R parameter, set to 6371000. This is the radius of the sphere.
I have chosen 6371000 as it is the average radius of the earth, in metres. This means that the result is also in metres.