# Calculate route but also calculate distance in each administrative area

### Question

• Is there anything built in into routing API or is there any common way to solve following problem..

Let's say I calculate route within continental USA. In addition to route and distances I would like to know total distance per state. NY - 100 miles, VA - 50 miles, etc.

What is the best way to approach this?

I'm kind of thining along those lines.. I will need to get geo data for each state border and then intersect polyline with those borders. Something like this. But not sure if this problem already solved.

Thursday, September 05, 2013 7:47 PM

• This question came up before in the forums although I can't seem to find it at the moment. To do this you will need to make use of the spatial library from SQL Server 2012. You don't actually need to setup a database or anything, just get the spatial library (dll) and use it in your .NET project. Once you have this then you will need the polygons/boundaries for the areas you are interested (i.e. states). you can find these available online in a lot of different formats and get them into SQL if you wanted. Alternatively there is a lot of boundary information available through Bing Maps : http://msdn.microsoft.com/en-us/library/dn306801.aspx

Once you have the boundaries you can then use the data to generate spatial geographies in the SQL library. This will allow you to perform complex calculation against the data relatively easily. Once that is done you can use the Bing Maps REST services to generate a route and to get the route path (list of coordinates). Use these coordinates to create a LineString geometry in the SQL library. Now you can do an intersection test against your boundaries and the line and then take the intersected components and use the distance method in the spatial library to get their length.

http://rbrundritt.wordpress.com

Friday, September 06, 2013 12:40 PM
• I've added a code sample of how to do this here: https://code.msdn.microsoft.com/Calculate-Route-Distances-331716cf

http://rbrundritt.wordpress.com

Monday, December 14, 2015 9:25 PM

### All replies

• This question came up before in the forums although I can't seem to find it at the moment. To do this you will need to make use of the spatial library from SQL Server 2012. You don't actually need to setup a database or anything, just get the spatial library (dll) and use it in your .NET project. Once you have this then you will need the polygons/boundaries for the areas you are interested (i.e. states). you can find these available online in a lot of different formats and get them into SQL if you wanted. Alternatively there is a lot of boundary information available through Bing Maps : http://msdn.microsoft.com/en-us/library/dn306801.aspx

Once you have the boundaries you can then use the data to generate spatial geographies in the SQL library. This will allow you to perform complex calculation against the data relatively easily. Once that is done you can use the Bing Maps REST services to generate a route and to get the route path (list of coordinates). Use these coordinates to create a LineString geometry in the SQL library. Now you can do an intersection test against your boundaries and the line and then take the intersected components and use the distance method in the spatial library to get their length.

http://rbrundritt.wordpress.com

Friday, September 06, 2013 12:40 PM
• Thank you. This is how I saw it as well. There will be questions of course on how to do it best, etc. I guess first thing I will have to import census data into SQL Server and then see.
Friday, September 06, 2013 1:54 PM
• I've added a code sample of how to do this here: https://code.msdn.microsoft.com/Calculate-Route-Distances-331716cf

http://rbrundritt.wordpress.com

Monday, December 14, 2015 9:25 PM