Skip to content

Instantly share code, notes, and snippets.

@aramonc
Last active March 13, 2024 11:23
Show Gist options
  • Save aramonc/6680152 to your computer and use it in GitHub Desktop.
Save aramonc/6680152 to your computer and use it in GitHub Desktop.
MySQL function to calculate the bearing between two points
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 ;
@Frank071
Copy link

Frank071 commented Mar 13, 2024

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 ;

@aramonc
Copy link
Author

aramonc commented Mar 13, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment