ST_Distance_Sphere() is a built in function in MySQL which calculates the distance between two points on a sphere, taking as parameters two `POINT`

objects.

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.