Stored function for haversine distance computation

by

In another article I described the process of using MySQL to compute great-circle distances between various points on the earth then their latitudes and longitudes are known.  To do this requires the formula commonly called the haversine formula. It’s actually the spherical cosine law formula, and is shown here. There’s a more numerically stable formula — better when points are near each other on the earth’s surface — called the Vincenty formula.

A good MySQL Stored Function for the so-called Haversine formula is this:

DELIMITER $$
DROP FUNCTION IF EXISTS haversine$$

CREATE FUNCTION haversine(
        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
     ) RETURNS FLOAT
    NO SQL DETERMINISTIC
    COMMENT 'Returns the distance in degrees on the Earth
             between two known points of latitude and longitude'
BEGIN
    RETURN DEGREES(ACOS(
              COS(RADIANS(lat1)) *
              COS(RADIANS(lat2)) *
              COS(RADIANS(lon2) - RADIANS(lon1)) +
              SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
            ));
END$$

DELIMITER ;

To use this function successfully, you need to know the following things:

the latitude and longitude of a starting point
the latitude and longitude of an ending point
the number of kilometers (111.045), statute miles (69), or nautical miles (60) in a degree of latitude.

For example, you can use this stored haversine function to look in a zip code table and find the fifteen closest points to a particular point.

SELECT zip, primary_city, latitude, longitude,
       111.045*haversine(latitude,longitude,latpoint, longpoint) AS distance_in_km
 FROM zip
 JOIN (
     SELECT  42.81  AS latpoint,  -70.81 AS longpoint
   ) AS p
 ORDER BY distance_in_km
 LIMIT 15

Notice that the value of the haversine function is returned in degrees of longitude. Multiplying that value by 111.045 converts it to kilometers.

If one of your points is stored in a MySQL geospatial object, you can use this function.

DELIMITER $$
DROP FUNCTION IF EXISTS haversinePt$$

CREATE FUNCTION haversinePt(
        point1 GEOMETRY,
        lat2 FLOAT, lon2 FLOAT
     ) RETURNS FLOAT
    NO SQL DETERMINISTIC
    COMMENT 'Returns the distance in degrees on the Earth
             between two known points of latitude and longitude
             where the first point is a geospatial object and
             the second is lat/long'
BEGIN
    RETURN DEGREES(ACOS(
              COS(RADIANS(X(point1))) *
              COS(RADIANS(lat2)) *
              COS(RADIANS(lon2) - RADIANS(Y(point1))) +
              SIN(RADIANS(X(point1))) * SIN(RADIANS(lat2))
            ));
END$$

DELIMITER ;

Stored Function in T-SQL (Microsoft SQL Server)

Here’s the function for Microsoft SQL server.

IF OBJECT_ID (N'dbo.Haversine', N'FN') IS NOT NULL
    DROP FUNCTION Haversine;
GO
CREATE FUNCTION dbo.Haversine(
  @lat1 float, @lon1 float,
  @lat2 float, @lon2 float )
RETURNS float 
AS 
-- Returns distance in degrees
BEGIN
   RETURN
     DEGREES(ACOS(COS(RADIANS(@lat1))
                * COS(RADIANS(@lat2))
                * COS(RADIANS(@lon1 - @lon2))
                + SIN(RADIANS(@lat1))
                * SIN(RADIANS(@lat2))))
END;
GO

Leave a Comment