Travel distance calculation with Bing maps API and Python

For my company, Rijbewijskeuring Holland, several doctors work throughout the Netherlands. They carry out the driver's license examination that we offer at different locations. In addition to a fee for the work performed, they also receive a mileage allowance.

Usually, a doctor works at fixed location(s), but they can also substitute for another doctor. So, theoretically, each doctor can work at any examination location. Currently, we have 8 doctors working for us at 29 locations. So, I have to determine 8x29=232 distances to calculate the mileage allowance for all doctors.

Doing this manually is far too much work. Therefore, I want to use the Google Maps API, but that costs money. So, I looked into the Bing Maps API instead and managed to use it.

After getting the API to work, I incorporated it into a function that I can call to calculate the distance between two points. I stored the locations (postcodes) of the doctors (rows) and examination locations (columns) in a .CSV file called "distances". The script reads the data from the .csv file into a dataframe. Then, using loops, it retrieves the location of a doctor and an examination location. After that, the function determines the distance between those two locations. The calculated distance is then stored in the dataframe. Finally, everything from the dataframe is stored back into the .csv file.

Getting access to the Bing maps API

To start working with the bing maps API, you need to following the following steps:

1. Go to the Bing Maps Dev Center website and sign in with your Microsoft account.

2. After that navigate to "my account" - "my keys"

3. Enter the required informatation to make a key. Please note, hold this API-key secret and do not share it publicly

Now you can acces the bing maps API. Please note that only a limited API calls are for free. When I started using the API, the limit was set at 125000 calls. Please consult the Bing maps API documentation to find out what the limit will be for you.

The .CSV file

The .CSV file contains the start point and end point of the distance calculations. Bing Maps can interpret many forms of location specifications (like GPS coordinates, postal code or complete addresses). However, to increase the chance of a successful distance calculation, it is recommend to specify the locations as clearly as possible.

For example, in my situation I needed to calculate the distance between two Dutch postal codes. Dutch postal codes comprise 4 numbers followed by two letters (eg: 1000 AA). However, sometimes Bing maps was unable to calculate the distance because apparently, other countries might use a similar system. Therefore, I have specified als postal codes in the following format: "the Netherlands, 1000AA. This way I stated explicitly that the locations is in the Netherlands.

The .CSV itself contains the destinations in the first column and the origins in the first row as given in the figure below:

The Python script

To automate the entire process, I have made a python script. Here, I will detail how the script works. You could also find my script HERE on Deepnote. First, I incorporated it into a function that I can call to calculate the distance between two points. I stored the locations (postcodes) of the doctors (rows) and examination locations (columns) in a .CSV file called "distances". The script reads the data from the .csv file into a dataframe. Then, using loops, it retrieves the location of a doctor and an examination location. After that, the function determines the distance between those two locations. The calculated distance is then stored in the dataframe. Finally, everything from the dataframe is stored back into the .csv file.

Import the required libraries

In this block, we import the requests and pandas libraries that we need to make API calls and work with data in tabular format.

Define the function to get the distance using Bing maps

In this block, we define a function extract_distance() that takes two arguments, origin and destination, and retrieves the driving distance between them by making an API call to Bing Maps. Furthermore, if no distance could be calculated the function returns 0.

Read the .CSV file with pandas

We define the file name of the CSV file and read the file using pandas' read_csv() function, saving the result into a DataFrame named df.

Extract the origin and destination columns

We extract the origin and destination columns from the DataFrame and store them in separate variables.

Calculate the distances and store them in the DataFrame

In this block, we use nested loops to iterate through each origin and destination and calculate the distance between them using the extract_distance() function that we defined earlier. We store the distances in the DataFrame df.

Save the results to the CSV file.

Finally, we save all the obtained results back to the .CSV file