## Sunday, October 21, 2012

### Geo-Distance vlg Haversine as a DB2 UDF

In a hack I'm currently working on (to be published soon) I needed to calculate the distance between two locations (on the surface of our planet). Admittedly, those are close addresses (all in Vienna), so I could have assumed them all to be on a plane (not an airplane, a flat surface...), but I felt like some real math.

This is a workaround for all of you who do not have spatial extender available or installed.

To calculate the distance of two points on (a perfectly spherical earth) you apply the haversine formula.
This is the representation of it as a DB2 user defined function (UDF)

CREATE FUNCTION HAVERSINE( lat1 decimal(10,8), lon1 decimal(10,8), lat2 decimal(10,8), lon2 decimal(10,8))
RETURNS DECIMAL(15,8)
F1: BEGIN ATOMIC
declare dlat, dlon, a, d double;
set a = sin(dlat/2) * sin(dlat/2) + cos(radians(lat1)) * cos(radians(lat2)) * sin(dlon/2) * sin(dlon/2);
set d = 2*6367*asin(sqrt(a));
return d;
END

The two points are given in latitude and longitude (in degrees) as lat1/lon1 and lat2/lon2. It returns the distance in kilometers. If you want to change this, replace 6367 (which happens to be the Earth's radius im km in Vienna, for which I needed this) with your radius and units,e.g. 3956 if you want miles in London.
Check it simply at Wolfram Alpha with "earth radius in london".

I guess it is not perfect, but it does work well. I should probably make it DETERMINISTIC (because it is) to improve performance.