×

Using Haversines with SQL To Calculate Accurate Distances

Sometimes it becomes desirable to know the distance between two geographical places. This could come about as a desire to know where a customer base is regarding a store, where the nearest fire station is to a house, or wanting to know how many people would be affected by a location closing or opening.

There are a lot of services out there that will take your data and geocode it for distances between a single location and a data set. But if you need to have a lot of distances calculated, or you aren't sure of where your one of your points are, this can become onerous and costly.

Programming your own solution can be difficult, though. Using standard three-dimensional math doesn't take into account the curve of the earth. Looking into how to calculate the distance between two points on a sphere will get you a lot of equations, but these are often intricate and difficult to debug.

This article will show you how to calculate the distances between points in a database, taking into account the curvature of the earth.

Why You Can't Use 3D Geometry

There's no built in way in a database to calculate the distance between two places. Most people tackle this by using the regular geometry distance formula:

Approaching the problem in this way, though, is like tunneling under the earth's crust. The further apart the points, the less accurate this calculation. This standard distance formula doesn't take into account the shape of the earth, which is mostly spherish.

Points on a Sphere

In order to calculate a better distance, it is necessary to use analytic geometry to calculate the distance between two points on the surface of a sphere. This wouldn't be bad if there was somewhere to quickly grab a formula. Unfortunately, most found formulas are intricate, long and very difficult to translate into a programming language.

This is where we can rely on a mathematical function called a haversine. It's an application of a more general trigonometric law for relating points on a spherical surface.

The haversine formula, generalized, is:

Distance Using Haversines

The standard distance between two latitude/longitude pairs can be expressed as

where:
R is the radius of the earth (pick your favorite one), and

and your data points are in latitude/longitude pairs of

Easy peasy, right?

Setting Up Your Database

In order to make this calculation work, you will have to have latitude and longitude for your data points. Latitude and longitude should be in decimal degrees, and I'm sure you remember from trigonometry that we will need to convert values to radians to make trig work right. We'll take care of the conversion to radians inside the function.

Here are the table structures:

CREATE TABLE dbo.SiteOne ( SiteOnePK INT IDENTITY PRIMARY KEY , SiteName VARCHAR(50) , LatitudeDegree NUMERIC(30, 15) , LongitudeDegree NUMERIC(30, 15) ) CREATE TABLE dbo.SiteTwo ( SiteOnePK INT IDENTITY PRIMARY KEY , SiteName VARCHAR(50) , LatitudeDegree NUMERIC(30, 15) , LongitudeDegree NUMERIC(30, 15) );

 

The Data

Here is the sample data: 

INSERT INTO dbo.SiteOne (SiteName,LatitudeDegree,LongitudeDegree) VALUES ('Great Pyramid at Giza',29.97925,31.134222) , ('Statue of Liberty',40.68925,-74.044444) , ('Eiffel Tower',48.858361,2.294444) , ('Uluru',-25.343667,131.036889); INSERT INTO dbo.SiteTwo (SiteName,LatitudeDegree,LongitudeDegree) VALUES ('Mt. Kilimanjaro',-3.067389,37.355583) , ('Taj Mahal',27.175056,78.042111) , ('Golden Gate Bridge',37.820111,-122.478278) , ('Machu Picchu',-13.162889,-72.545028) , ('Brandenburg Gate',52.51625,13.377667);

As you can see from the sample data, we will be finding the distance between some of the world's notable places.

 

The Function

The function, using the haversines, is fairly straightforward: 

CREATE FUNCTION dbo.ufnSphericDistance ( @LatitudeOne NUMERIC(30, 15) , @LongitudeOne NUMERIC(30, 15) , @LatitudeTwo NUMERIC(30, 15) , @LongitudeTwo NUMERIC(30, 15) ) RETURNS NUMERIC(30, 15) AS /***************************************************************************************** * Created on 03/12/2022 * Created by LMoss * * Description: Calculate the distance between two latitude/longitude pairs IN DECIMAL DEGREES * *****************************************************************************************/ BEGIN DECLARE @Result NUMERIC(30, 15) --convert the input parameters into radians DECLARE @LatitudeOneInRadians NUMERIC(30, 15) = @LatitudeOne * PI() / 180 DECLARE @LongitudeOneInRadians NUMERIC(30, 15) = @LongitudeOne * PI() / 180 DECLARE @LatitudeTwoInRadians NUMERIC(30, 15) = @LatitudeTwo * PI() / 180 DECLARE @LongitudeTwoInRadians NUMERIC(30, 15) = @LongitudeTwo * PI() / 180 --use your favorite Earth radius here. If you want kilometers, you can use that as well - your output will be in km DECLARE @EarthRadiusInMiles NUMERIC(30, 15) = 3958.939 --calculate the haversine of the difference in latitudes DECLARE @HavTheta NUMERIC(30, 15) = (1 - COS(@LatitudeOneInRadians - @LatitudeTwoInRadians)) / 2 --calculate the haversine of the difference in longitudes DECLARE @HavPhi NUMERIC(30, 15) = (1 - COS(@LongitudeOneInRadians - @LongitudeTwoInRadians)) / 2 --calculate the haversine of the alpha DECLARE @HavAlpha NUMERIC(30, 15) = @HavTheta + COS(@LatitudeOneInRadians) * COS(@LatitudeTwoInRadians) * @HavPhi --do your final calculation SET @Result = 2 * @EarthRadiusInMiles * ASIN(SQRT(@HavAlpha)) RETURN @Result END GO

 

Getting the Results

Because we want to calculate the distance from all of the points in table 1 to all the points in table 2, we are going to use a CROSS JOIN.

SELECT f.SiteName , g.SiteName , dbo.ufnSphericDistance(f.LatitudeDegree, f.LongitudeDegree, g.LatitudeDegree, g.LongitudeDegree) DistanceInMiles FROM dbo.SiteOne f CROSS JOIN dbo.SiteTwo g

 

And the results:

Site Name 1 Site Name 2 Distance in Miles
Great Pyramid at Giza Mt. Kilimanjaro 2320.1288
Great Pyramid at Giza Taj Mahal 2833.2633
Great Pyramid at Giza Golden Gate Bridge 7452.1931
Great Pyramid at Giza Machu Picchu 7480.0686
Great Pyramid at Giza Brandenburg Gate 1798.9758
Statue of Liberty Mt. Kilimanjaro 7471.3509
Statue of Liberty Taj Mahal 7417.9789
Statue of Liberty Golden Gate Bridge 2566.1218
Statue of Liberty Machu Picchu 3722.2347
Statue of Liberty Brandenburg Gate 3969.3014
Eiffel Tower Mt. Kilimanjaro 4157.3643
Eiffel Tower Taj Mahal 4200.2581
Eiffel Tower Golden Gate Bridge 5560.9655
Eiffel Tower Machu Picchu 6234.3274
Eiffel Tower Brandenburg Gate 546.2652
Uluru Mt. Kilimanjaro 6357.4330
Uluru Taj Mahal 5060.3882
Uluru Golden Gate Bridge 8133.6025
Uluru Machu Picchu 9338.9041
Uluru Brandenburg Gate 8741.4261

 

If You Want To Dive Into The Math

The background on the math is easy to find on the internet. I highly recommend that any programmer understand the math of any equation they are trying to put into use. It's really the only way to make sure that your results are accurate. A thorough and easy-to-follow explanation of math behind using haversines to calculate distance between latitude and longitude points can be found in Distance between Points on the Earth's Surface.

 

We Can Help You Make The Most of Your Data.

Marathon offers a complete portfolio of business intelligence and data analytics services, providing valuable insights and opportunities to build a roadmap to success. Let's talk about your project today.

Laura MossCore Contributor

Laura Moss is a senior software engineer with Marathon Consulting. As a data wrangler, she specializes in data warehouse architecture and moving data between systems. Her inbox is always empty.