MySQL function to calculate the bearing between two points

bearing.sql

DELIMITER $$
CREATE FUNCTION `bearing` (lat1 DECIMAL(8,6), lng1 DECIMAL(9,6), lat2 DECIMAL(8,6), lng2 DECIMAL(9,6)) RETURNS DECIMAL(9,6)
BEGIN
    DECLARE dLng DECIMAL(30,15);
    DECLARE y DECIMAL(30,15);
    DECLARE x DECIMAL(30,15);
    DECLARE bearing DECIMAL(30,15);

    SET dLng = RADIANS( lng2 ) - RADIANS( lng1 );

    SET y = SIN( dLng ) * COS( RADIANS( lat2 ) );
    SET x = ( COS( RADIANS( lat1 ) ) * SIN ( RADIANS( lat2 ) ) ) - ( SIN( RADIANS( lat1 ) ) * COS( RADIANS( lat2 ) ) * COS( dLng ) );
    SET bearing = DEGREES( ATAN2( y, x ) );

RETURN bearing;
END$$
 
DELIMITER ;

This function returns a bearing which can be between -180 and +180 degrees. The return type should therefore accommodate for 3 digits before the decimal point, hence DECIMAL(9,6). In its present form recent versions of MariaDB/MySQL chuck an error for higher angles.

Thanks @Frank071 , I'll update it now. To be honest, I had forgotten this was even here.

Well... Google did not forget and it helped me a lot. So thanks for sharing and leaving it on the web ;-)

Small observation: you changed (the definition of) lat1, but that will be between -90 and 90, so (8,6) is fine. The longitude can be between -180 and 180 as can the bearing. So the proper create should be:

CREATE FUNCTION bearing (lat1 DECIMAL(8,6), lng1 DECIMAL(9,6), lat2 DECIMAL(8,6), lng2 DECIMAL(9,6)) RETURNS DECIMAL(9,6)

While at it, this is a version for when you use POINT datatypes. It also contains (disabled) statements to have a bearing between 0 and 360 degrees.

DELIMITER $$
CREATE FUNCTION `bearing` (p1 POINT, p2 POINT) RETURNS DECIMAL(10,6)
BEGIN
    DECLARE dLng DECIMAL(30,15);
    DECLARE y DECIMAL(30,15);
    DECLARE x DECIMAL(30,15);
    DECLARE bearing DECIMAL(30,15);
    # DECLARE bearing360 DECIMAL(30,15);

    SET dLng = RADIANS( x(p2) ) - RADIANS( x(p1) );

    SET y = SIN( dLng ) * COS( RADIANS( y(p2) ) );
    SET x = ( COS( RADIANS( y(p1) ) ) * SIN ( RADIANS( y(p2) ) ) ) - ( SIN( RADIANS( y(p1) ) ) * COS( RADIANS( y(p2) ) ) * COS( dLng ) );
    SET bearing = DEGREES( ATAN2( y, X ) );
    # SET bearing360 = IF( bearing < 0, bearing + 360 , bearing);

RETURN bearing;
# RETURN bearing360;
END$$
 
DELIMITER ;

Fixed, thanks again & thank you for the alternative 👍 😄