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

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

Take a look at this post. http://www.plumislandmedia.net/mysql/stored-function-haversine-distance-computation/. It does what you ask using a different stored function with the same formal parameters.