12,896,108 members (46,698 online)
Tip/Trick
alternative version

#### Stats

3.4K views
3 bookmarked
Posted 5 Feb 2014

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

, 5 Feb 2014 CPOL
 Rate this:
a simple way to estimate as accurately as possible route-distances and traveling times without exact route calculation in sql

## 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
/*
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

## Share

 Software Developer (Senior) Germany
No Biography provided

 Pro