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.