Use Google Maps to optimize your commutes

In October 2017 I started a new job. For more than a year I had to commute by car every week from the north of Bavaria to Hamburg (Germany), because we decided that the family will stay at the old house for about year. Each trip is about 530 km, so 1060 km every week. Sometimes traffic was low, so I drive very fast, sometimes traffic was extremely bad and it took like forever.

I asked myself, how can I optimize my traveltime, so that I avoid traveling on days and time of days where the traffic is very high? So, I decided to ask Google how I could optimize my commute. Google’s prediction for travel duration was at that point very poor. On average it was a five hours drive. And no matter which day of the week and time of day I always got something between 4-6 hours from Google.

So, I decided to log the data by my own. I wrote a script that asked Google for the travel duration. The result will be written into a MariaDB database and after some weeks we should have enough data to predict the best travel time.

First thing I had to do is create a table in my MariaDB database. Use this SQL statement to create the database:

CREATE TABLE `gmaps_distance` (
  `gmaps_id` int(11) NOT NULL,
  `traveltime` datetime NOT NULL,
  `distance` int(11) NOT NULL,
  `duration` int(11) NOT NULL,
  `duration_in_time` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The ‘gmaps_id’ can be used if you want to measure more than just one route.

Next step was to write a Python script that makes a request to the Google Maps API to retrieve the data. The scripts ask for the distance duration for both directions . This is how it looks like:

import urllib, json
import MySQLdb

distances = []

distance = []
distance.append ("1")
distance.append ("Konradsreuther%20Strasse%2018d,%20hof,germany")
distance.append ("regerstrasse%2030,hamburg,germany")
distances.append(distance)

distance = []
distance.append ("2")
distance.append ("regerstrasse%2030,hamburg,germany")
distance.append ("Konradsreuther%20Strasse%2018d,%20hof,germany")
distances.append(distance)

for distance in distances:

    url =  "https://maps.googleapis.com/maps/api/distancematrix/json?units=metric&departure_time=now&mode=driving&"
    url += "origins=" + distance[1]
    url += "&destinations=" + distance[2]
    url += "&key=<HERECOMESYOURGOOGLEAPIKEY>"
    response = urllib.urlopen(url)
    data = json.loads(response.read())

    distance_in_meter = data['rows'][0]['elements'][0]['distance']['value']
    duration = data['rows'][0]['elements'][0]['duration']['value']
    duration_in_traffic = data['rows'][0]['elements'][0]['duration_in_traffic']['value']

    conn = MySQLdb.connect(host= "DBHOSTNAME",
                           user="USERNAME",
                           passwd="PASSWORD",
                           db="DATABASENAME")

    x = conn.cursor()
    sql = "INSERT INTO gmaps_distance VALUES (" + distance[0]  + ",now(),%i,%i,%i)"
    x.execute (sql % (int(distance_in_meter), int(duration), int(duration_in_traffic)))
    conn.commit()
    conn.close()

Note: You have to get your own Google API Key. Follow these instructions to get your API Key: https://developers.google.com/maps/documentation/embed/get-api-key

That’s bascially it. Now you can query the data like you want. I personally use Metabase to display charts on the web.

The above chart was generated with LibreOffice Spreadsheet. The data was from 2018-01-19 until 2020-11-25, aggregated with this SQL query:

select AVG(`duration_in_time`), DAYOFWEEK(traveltime) as day_of_week
from gmaps_distance
group by day_of_week