Waking up in Geelong

Last night a new exposure site was added to the Victorian Government coronavirus website – the route 82 tram from Footscray to Edgewater on 10/07/2021 between 7:51pm – 8:14pm. But which tram was involved – turns out my Tram Detective website has the answer.

So what is Tram Detective?

Tram Detective is a tool I built that collects data from the TramTracker API every 10 minutes for every tram in Melbourne.

It then slices and dices the data to give insights into how the fleet is deployed, when new trams enter service, old trams are retired, and give gunzels a ‘heads up’ for unusual tram movements.

So lets go digging

The Victorian Government coronavirus website has a list of exposure sites, with route 82 tram from Footscray to Edgewater on 10/07/2021 between 7:51pm – 8:14pm one of them.

Finding out which trams were running on route 82 on July 10 was easy – Tram Detective has a page that does just that already.

A total of 12 trams ran a route 82 service that day – Z3.118, 124, 125, 130, 147, 154, 165, 179, 181, 186, 212, 218 and 228.

Tram Detective also lets you drill down to specific trams and see what route they were running on a given day, as well as at a specific time – but it doesn’t give you a way to see their exact location.

For that I had to run a query on the backend database that powers the site.

SELECT tramid, sighting, destination, direction FROM trams_history WHERE routeNo = 82 AND sighting > '2021-07-10 19:40' AND sighting < '2021-07-10 20:30' ORDER BY tramid, id;

And after waiting for my poor database server to filter through years worth of data, it gave me an answer.

During the exposure period there were six trams running on route 82 - Z3.124, 125, 165, 181, 218 and 228. Taking a closer look at the data, one can determine in which direction they are headed.

• Z3.124 was headed for Footscray during the entire exposure period.
• Z3.218 and Z3.228 were headed for Moonee Ponds during the entire exposure period.
• Z3.165 was headed for Footscray, arriving at the terminus sometime between 7:54pm and 8:04pm.
• Z3.125 was headed for Footscray, arriving at the terminus sometime between 8:12pm and 8:22pm.

Which leaves tram Z3.181 - it was was headed for Footscray at 7:40pm then headed back towards Moonee Ponds at 7:50pm - right in the middle of the exposure period.

July 20 - a correction

My original post flagged Z3.186 as the affected tram, based on the following data:

Turns out I messed up the database query that I was running!

My server saves datetime data in the server timezone, and application code converting the value back to Melbourne time when you view the page. By leaving this timezone conversion out of my SQL queries, I was looking at the wrong slice of time, and came to an incorrect conclusion.

Liked it? Take a second to support Marcus Wong on Patreon!