The Vincenty great-circle distance formula

This Vincenty formula is a more numerically stable version of the spherical cosine law formula (commonly and wrongly known as the Haversine formula) for computing great circle distances. The question of numerical stability comes up specifically when the distances between points are small. In those cases the cosine is very close to 1, so the inverse cosine function is not as precise as the inverse tangent function used in this formula.

See http://en.wikipedia.org/wiki/Great-circle_distance for more information.

DELIMITER $$
DROP FUNCTION IF EXISTS vincenty$$
CREATE FUNCTION vincenty(
        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
             using the Vincenty formula
             from http://en.wikipedia.org/wiki/Great-circle_distance'
BEGIN
    RETURN  DEGREES(
    ATAN2(
      SQRT(
        POW(COS(RADIANS(lat2))*SIN(RADIANS(lon2-lon1)),2) +
        POW(COS(RADIANS(lat1))*SIN(RADIANS(lat2)) -
             (SIN(RADIANS(lat1))*COS(RADIANS(lat2)) *
              COS(RADIANS(lon2-lon1))) ,2)),
      SIN(RADIANS(lat1))*SIN(RADIANS(lat2)) +
      COS(RADIANS(lat1))*COS(RADIANS(lat2))*COS(RADIANS(lon2-lon1))));
END$$
DELIMITER ;

Vincenty formula for Oracle PL/SQL

Here is the same formula written in the Oracle stored function language.

create or replace FUNCTION vincenty  (
  lat1 IN REAL, lon1 IN REAL, 
  lat2 IN REAL, lon2 IN REAL)
RETURN REAL
DETERMINISTIC 
IS

  DEGREES REAL := 57.2957795;
  RADIANS REAL := 0.0174532925;

BEGIN
 RETURN  DEGREES * (
    ATAN2(
      SQRT(
        POWER(COS(RADIANS * (lat2))*SIN(RADIANS * (lon2-lon1)),2) +
        POWER(COS(RADIANS * (lat1))*SIN(RADIANS * (lat2)) -
             (SIN(RADIANS * (lat1))*COS(RADIANS * (lat2)) *
              COS(RADIANS * (lon2-lon1))) ,2)),
      SIN(RADIANS * (lat1))*SIN(RADIANS * (lat2)) +
      COS(RADIANS * (lat1))*COS(RADIANS * (lat2))*COS(RADIANS * (lon2-lon1))));
END;

Vincenty formula in T-SQL for Microsoft SQL server

Here is the same formula written in the T-SQL stored function language.

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

  2 comments for “The Vincenty great-circle distance formula

  1. Andrew
    June 13, 2014 at 6:54 pm

    Can you add another sample code of how you would use this function in a SQL query please?

Leave a Reply

Your email address will not be published. Required fields are marked *