Calculating the distance between to points is extremely handing, especially for business - how far away are your customers from your stores, how close is the nearest competition, and many other scenarios. Provided you have an address, and a latitude and longitude for that address, you can begin calculating distances.
For this, we will be using these sample coordinates:
Dallas (Latitude: 32.7642 / Longitude: -96.8280)
Fort Worth (Latitude: 32.7457 / Longitude: -96.3718)
New York (Latitude: 40.7642 / Longitude: -73.9407)
The examples will be provided as T-Sql user-defined functions for SQL Server 2000/2005, but can easily be converted for other programming languages.
Distances on a flat surface
The first, and perhaps easiest, approach is to calculate the distance between two coordinates on a flat surface, something you probably learned in algebra.
create function [dbo].[GetDistanceFlat] (
@Latitude1 decimal(12,6), -- Degrees Latitude, first coordinate
@Longitude1 decimal(12,6), -- Degrees Longitude, first coordinate
@Latitude2 decimal(12,6), -- Degrees Latitude, second coordinate
@Longitude2 decimal(12,6) -- Degrees Longitude, second coordinate
)
returns decimal(12,6)
as
begin
declare @Lat real, @Long real
declare @Distance decimal(12,6)
set @Lat = power(69.1 * (cast(@Latitude2 as real) - cast(@Latitude1 as real)), 2)
set @Long = power(53 * (cast(@Longitude2 as real) - cast(@Longitude1 as real)), 2)
set @Distance = sqrt(@Lat + @Long)
return @Distance
end
There are several things to point out. First, you'll notice that @Lat is modified slightly from (x2-x1)^2, and @Long is modified from (y2-y1)^2, by using 69.1 and 53, respectively. This is to convert the distance to miles. These numbers can easily be modified to calculate distance in nautical miles or kilometers. The second item is that this formula does not account for the curvature of the earth, and distances will be inaccurate, especially the farther away from the equator your coordinates are located.
Distances on a curved surface
The second approach is to account for the curvature of the earth, using the formula below.
create function [dbo].[GetDistanceSphere] (
@Latitude1 decimal(12,6), -- Degrees Latitude, first coordinate
@Longitude1 decimal(12,6), -- Degrees Longitude, first coordinate
@Latitude2 decimal(12,6), -- Degrees Latitude, second coordinate
@Longitude2 decimal(12,6) -- Degrees Longitude, second coordinate
)
returns decimal(12,6)
as
begin
declare @EarthRadius real,
@LatRadians1 real,
@LongRadians1 real,
@LatRadians2 real,
@LongRadians2 real,
@RadianRatio real,
@Sin real,
@Cos real,
@Acos real
declare @Distance decimal(12,6)
set @EarthRadius = 3963
set @RadianRatio = (cast(180 as real) / pi())
set @LatRadians1 = cast(@Latitude1 as real) / @RadianRatio
set @LatRadians2 = cast(@Latitude2 as real) / @RadianRatio
set @LongRadians1 = cast(@Longitude1 as real) / @RadianRatio
set @LongRadians2 = cast(@Longitude2 as real) / @RadianRatio
set @Sin = sin(@LatRadians1) * sin(@LatRadians2)
set @Cos = cos(@LatRadians1) * cos(@LatRadians2) * cos(@LongRadians2 - @LongRadians1)
set @Acos = acos(@Sin + @Cos)
set @Distance = cast(round(@Acos * @EarthRadius, 6) as decimal(12,6))
return @Distance
end
Here, you will notice that the degrees are converted to radians, and the distance is calculated using trigonometric functions Sine, Cosine, and angle of the Cosine, as well as using the radius of the earth, in miles. Keep in mind that since this formula is heavily mathematic in nature, this will demand more CPU - which means if you run this in batch, you can see your CPU utilization spike.
The results
We will calculate two distances with each formula - from Dallas to Fort Worth, and from Dallas to New York.
-- Dallas to Fort Worth: 24.21mi
select dbo.[GetDistanceFlat](32.7642, -96.8280, 32.7457, -96.3718)
-- Dallas to New York: 1330.55mi
select dbo.[GetDistanceFlat](32.7642, -96.8280, 40.6765, -73.9407)
-- Dallas to Fort Worth: 26.53mi
select dbo.[GetDistanceSphere](32.7642, -96.8280, 32.7457, -96.3718)
-- Dallas to New York: 1376.63mi
select dbo.[GetDistanceSphere](32.7642, -96.8280, 40.6765, -73.9407)
You can see that the distance results do vary. Depending on whether accuracy is a concern, you can decide which approach to take.