Is it possible to get travel distance (not crow files) between two lat & lng in sql

Question

I have been using crow file distance function in sql to get the distance in KM. I am wondering if it is possible to get the travel distance not crow file distance in sql server 2008 r2?

Answer 1

Not directly, as SQL Server itself has no knowledge of roads or routing. You would need to call to some external API (such as Google Maps or Bing Maps as in your question tags), passing the two end points and asking for the shortest route, then determine the distance based on their response.

Let's consider this incredibly crude drawing.

The user is at Point A, and they want to reach Point B. The direct route (purple) goes over a mountain. So we need to go around the mountain.

But there are other considerations. Maybe there is a bridge out along Route 1. Maybe the road itself is washboard dirt, whereas Route 2 is a superhighway.

Point is, there are a huge number of nonfunctional requirements that make up routing decisions, that SQL Server just can't decide for you.

Answer 2

You could create an SQL function in .NET that calls the Bing Maps REST routing service and retrieves the distance/time info as needed. Here is a blog post that shows how to create such a function using the geocoding service: https://alastaira.wordpress.com/2012/05/04/geocoding-in-sql-server-with-the-bing-maps-locations-api/

You can find documentation on the routing service and tips on using it in .NET here:

https://msdn.microsoft.com/en-us/library/ff701717.aspx

https://msdn.microsoft.com/en-us/library/jj819168.aspx

https://msdn.microsoft.com/en-us/library/jj870778.aspx

Now, this could potentially generate a ton of requests. It might be wise to create a function that takes in all the locations you need to calculate distances to and then optimize the calculation by merging locations into a single multi-point route. Bing Maps allows up to 25 locations to be specified in a single route request. If you need distances from A to B, C, D you could calculate a route from A-B-A-C-A-D. The response would return a route leg for each two locations. The odd indexed route legs would be your distance from A to the other locations. This would allow you to merge up to 12 locations into a single request. This would make things much faster and also reduce the number of requests/transactions you make which would also reduce the licensing costs that would be involved.