Can MySql determine 'bearing' between two records with latitude and longitude

Question

I am trying to find/store the 'bearing' of streets in a table I have. I can provide for each street the first building address latiutude and longitude and the same for the last building. Can MySql somehow process a compass bearing with this?

Answer 1

Here is a stored function that computes the initial compass heading (also known to navigators as the forward azimuth) to move from one point (lat1,lon1) to another point (lat2, lon2) on the (assumed spherical) globe.

It's based on formulae in this web page. http://www.movable-type.co.uk/scripts/latlong.html

DELIMITER $$

DROP FUNCTION IF EXISTS `initial_bearing`$$

CREATE FUNCTION `initial_bearing`(
        lat1 DOUBLE, lon1 DOUBLE, 
        lat2 DOUBLE, lon2 DOUBLE
     ) RETURNS DOUBLE
    NO SQL
    DETERMINISTIC
    COMMENT 'Returns the initial bearing, in degrees, to follow the great circle route
             from point (lat1,lon1), to point (lat2,lon2)'
    BEGIN

    RETURN (360.0 + 
      DEGREES(ATAN2(
       SIN(RADIANS(lon2-lon1))*COS(RADIANS(lat2)),
       COS(RADIANS(lat1))*SIN(RADIANS(lat2))-SIN(RADIANS(lat1))*COS(RADIANS(lat2))*
            COS(RADIANS(lon2-lon1))
      ))
     ) % 360.0;
END$$

DELIMITER ;

If you have a table containing the columns of FLOAT type called lat1,lon1,lat2, lon2,bearing, you could use this function to set the bearing column from the others with this query.

UPDATE table name SET bearing = initial_bearing(lat1,lon1,lat2,lon2)

This query, lacking a WHERE clause, will update every row of the table.