# Stored function for haversine distance computation

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
```