Travel distance calculation with Bing Maps API

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.

Import the required libraries

import requests import pandas as pd

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

def extract_distance(origin, destination): endpoint = "http://dev.virtualearth.net/REST/v1/Routes/Driving" params = { "o": "json", "wp.0": origin, "wp.1": destination, "key": "API-KEY" } response = requests.get(endpoint, params=params) try: result = response.json() distance = result['resourceSets'][0]['resources'][0]['travelDistance'] except: distance = 0 return distance

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

filename = "Distances.csv" df = pd.read_csv(filename, delimiter=';')

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

origins = df.columns[1:] destinations = df.iloc[:, 0].tolist()

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

for idx, destination in enumerate(destinations): for origin in origins: origin_destination = f"{origin} to {destination}" distance = extract_distance(origin, destination) df.loc[idx, origin] = round(distance, 0) print(f"Calculating distance for {origin_destination}")

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.

df.to_csv(filename, sep=';', index=False) print("Done")