Click here to Skip to main content
11,483,983 members (67,338 online)
Click here to Skip to main content

Tagged as

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

, 5 Feb 2014 CPOL 2.3K 3
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Peter BCKR
Software Developer (Senior)
Germany Germany
No Biography provided
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150520.1 | Last Updated 5 Feb 2014
Article Copyright 2014 by Peter BCKR
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid