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