Coding Education STEM

Using Directions & Travel APIs with Excel, Python. 1/2

In this blog, I’ll share tips on how to consume RESTful APIs for maps. Specifically, getting a location point info, getting distance and travel times between two points, and even turn-by-turn driving directions. With the techniques, we can also get walking and transit mode of travel info.

I’ll be leveraging Bing Maps APIs as they’re free. I used Google Maps APIs as well, but unfortunately, they now charge for consuming their service. However, the concept is exactly the same. Only the webservice URLs are different and returned payload is slightly differently formatted.

First, let’s start with Python. First, we’ll need a get an API from Bing from here. Once you have the API, next we’ll need to import urllib.request
and json libraries into our code. The urllib gives us the methods to call APIs and get the results back. jason gives us the ability to parse the results. By default, the payload is in JSON format. However, it also offers XML. In Python, I’ll use JSON, and for Excel method, I’ll use XML.

Here’s my code:

For simplicity, the origin and destination information are hard-coded. We can easily make it user-selectable/inputs with a few more lines of code. Notice that the API requires the starting point (origin) info as latitude and longitude as a comma-separated string. However, the values we have in the code are float. So, how do we do get the latitude and longitude of a point? We can easily get that using the browser or searching online, or better yet, we can also get that by making another API call by only supplying the conventional address of a location. I’ll demonstrate that part in the Excel section. The bingAPIKey is the key you’re provided by Bing.

The URL we need for driving directions is in the following format:,longitude&wp.1=destination_string&key=bingAPIKey

That’s our request or route url that we use with urllib.request.Request() call. And pass its return value to actually get the response with urllib.request.urlopen(). The response from the service we get will either have an error message or the desired information. In this case, we get a return in JSON that looks something like this:

For brevity, I’ve cut it short, the actual returned result is much longer. However, it contains a lot of information for each turn along with the distance and time for each. We have to first understand the structure of this before we can begin to parse this to extract the information in a user-friendly format. We see that it’s in dictionary objects with lots of arrays of dictionary. We also need to find how many turns there are, so we can parse each line and show. result["resourceSets"][0]["resources"][0]["routeLegs"][0]["itineraryItems"] gives us the number of items we need. Then we can iterate through that using for eachitem in itineraryItems:
and extract item by pointing to eachitem["instruction"]["text"] which contains the human-readable instruction for each turn. However, this doesn’t give us the total time and distance information for the itinerary. To get that, I get to result["resourceSets"][0]["resources"][0]["routeLegs"][0]["travelDistance"] to get the distance. (It’s returned as kilometers so, I convert it to miles in the code), and result["resourceSets"][0]["resources"][0]["routeLegs"][0]["travelDuration"] to get the duration. (It turns out this is given in seconds, so I convert it to minutes in the code). And if you’ve done this much, you’re done and can extend this to many more fancier things!

The output of this exact code (which is from my town to Microsoft HQ in this example) is below:

There you have it. Since this article would get a bit too long, I’m putting the Excel part of using these APIs in the next post. Be sure to check that out here.

Interested in creating programmable, cool electronic gadgets? Give my newest book on Arduino a try: Hello Arduino!

One thought on “Using Directions & Travel APIs with Excel, Python. 1/2

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top