Introduction
Sometimes it is necessary routes and travel times between 2 geocoordinaten within a database to calculate. In many cases, computation of the air-line distance enough but there are a more accurate solution. Determined and tested the formula I have (taking into account the curvature of the earth) by computation the distance and travel time for 100.000 routes (with air-line distances between 10 and 500 km) in SQL and MapPoint. With a deviation of 9700 meters or 6 minutes from the real route are the estimates very accurate.
The formula
Air-Line:
ph = PI / 180.
airline = 6378388 * acos(
sin(@StartYLat*[ph]) * sin(@TargetYLat*[ph])
+ cos(@StartYLat*[ph]) * cos(@TargetYLat*[ph])
* cos(@TargetXLong*[ph] - @StartXLong*[ph]))
Route distance:
Linear Regressions
y = a + b * x
a = 4.554256731286333E+03
b = 1.310433802265474E+00
x = airline in meter
Standard Error = 9669 meter
route distance = 4.554256731286333E+03 + (1.310433802265474E+00 * airline)
Sampel-Data air-line to real-world route distance:
airLine distance (meters) | route distance (meters) |
10760 | 14879 |
75738 | 105097 |
228838 | 304915 |
302902 | 405027 |
531430 | 695480 |
776296 | 1011800 |
Travel time:
Linear Regressions
y = a + b * x
a = 2.127211344607187E+01
b = 6.320240118189298E-04
x = airline in meter
Standard Error = 5,964 minutes
travel time = 2.127211344607187E+01 + (6.320240118189298E-04 * airline)
Sampel-Data air-line to real-world route travel time:
airLine distance (meters) | trave time (minutes) |
5516 | 12 |
125154 | 101 |
284804 | 205 |
454953 | 301 |
595006 | 400 |
743737 | 504 |
The formulas as sql functions
[fGeoDistanceGeo] takes as a parameters geography objects
[fGeoDistanceCoord] takes float values as parameters and contains the actual calculation
CREATE
FUNCTION [bl].[fGeoDistanceGeo](@LocStart geography, @LocTarget geography)
RETURNS TABLE
AS RETURN
SELECT t.*
FROM bl.fGeoDistanceCoord(@LocStart.Lat, @LocStart.Long, @LocTarget.Lat, @LocTarget.Long) t
GO
CREATE FUNCTION [bl].[fGeoDistanceCoord](@StartYLat FLOAT, @StartXLong FLOAT, @TargetYLat FLOAT, @TargetXLong FLOAT)
RETURNS TABLE
AS RETURN
SELECT
0 [airLine], 0 [routeDistance], 0 [travelTime]
WHERE @StartYLat = @TargetYLat and @StartXLong = @TargetXLong
UNION ALL
SELECT
CEILING(T.[airline]) [airLine],
CEILING(T2.[routeDistance]) [routeDistance],
CEILING(T2.[travelTime]) [travelTime]
FROM (
SELECT 6378388 * acos(
sin(@StartYLat*[ph]) * sin(@TargetYLat*[ph])
+ cos(@StartYLat*[ph]) * cos(@TargetYLat*[ph])
* cos(@TargetXLong*[ph] - @StartXLong*[ph])
) airline
FROM (SELECT PI() / 180. [ph]) ph
) T
OUTER APPLY (
SELECT
(4.554256731286333E+03 + ([airline] * 1.310433802265474E+00)) [routeDistance],
(2.127211344607187E+01 + ([airline] * 6.320240118189298E-04)) [travelTime]
) T2
WHERE (@StartYLat <> @TargetYLat or @StartXLong <> @TargetXLong)
GO
how to use
SELECT * from [bl].[fGeoDistanceCoord] (56.111, 8.111, 56.222, 8.222)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
airLine routeDistance travelTime
14144 23088 31
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Points of Interest
The advantage of this solution is because she relies on pure sql, she is very fast and by the small deviation has in most cases an acceptable accuracy.
History
05.02.2014: Article first published
05.02.2014: Change formating, add real-world sampel data