sql-functions for better estimation of route-distances and traveling times in pure sql (without routing)

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:  

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:  

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
  /* 
  airLine      INT in meter
  routeDistance  INT in meter
  travelTime    INT in minutes
  */
  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