During lockdown I’ve spent a lot of my evenings trying to improve my coding and data science skills with Datacamp. As I’ve tried to apply my new found knowledge to real world projects I’ve come to learn that about 80% of working with data is cleaning it up and making it tidy before you can actually do anything else with it. This also proved to be the case with this Wednesday’s Quiztime challenge from Tilman Wagner.
I had to spend a little time tidying up the video into a neat series of still images to make it possible to solve the quiz. Without doing this first it would have been much harder. Here’s the original tweet:
Tilman asks us to identify the train and the plane, but we can’t do either of those things until we know where he is, and instead of an image we get nine seconds of video footage to work with.
So how do we find out where he was? Working with video is harder than working with still images and if you tried to solve this by pausing and playing the video repeatedly then I admire your patience – but there are easier ways to get the information we need.
First I used YouTube-dl to download the video. Despite its name YouTube-dl can grab video content from pretty much any website, including Twitter. I’ve written a previous blog post about how to set up and use it, which you can read here. Once installed, the command to download the video and save it is as follows:
$ youtube-dl https://twitter.com/twone2/status/1275870812017438726 -o trainvideo.mp4
Now we have our own copy of the video to work with. Next we can use FFMPEG to break up the video into lots of different still images which we can work with individually. I’ve also written about FFMPEG in the past here.
To turn the video into still images run the following command:
$ ffmpeg -i trainvideo.mp4 img%06d.png
The default settings of FFMPEG mean that our 9 second video clip is turned into 298 still images that we can look at it more detail. No need to keep clicking pause to try and catch a glimpse of the station name.
Identifying Features For Geolocation
I admit the first thing I did was to try and see if the station name was captured in one of the stills. I was disappointed:
So there was going to be no immediate solution to this, but even so there were enough clues in the images to find where Tilman was. Finding Tilman is not always easy because he travels a lot so you cannot just assume he is in any given location at any particular time.
Once we identify some information about the location from the images, we can apply them as a set of conditions to check possible locations and see if they match. If a location does not match all the conditions, we can reject it as a possibility. If it does match all the conditions, then we have found the right location.
The first condition is obvious from watching the video: Tilman is on a train. The aeroplane we have to identify is travelling parallel to the railway line. But what is the plane doing? It is very low, so it must either be taking off or landing. It does not appear to be climbing, so it is more probable that it is landing. If it is landing, then it must be flying towards a runway. This means the runway it is landing on must be parallel with the train line that Tilman is travelling on. This gives us the first “true” condition:
1.The location will be close to an airport and the train line will be roughly parallel with the alignment of the runway.
We can get some other conditions from the images. It is easy to see that the landscape is quite flat farmland:
So condition #2 is easy:
2. The correct location will be in a flat area that is rural or semi-rural.
Thirdly we can see that a large number of houses have solar panels on the roof. Solar panels have to face the sun, so in Europe this means they generally face south or south-east. Several of the houses have solar panels so this helps with orientation:
If these buildings are facing roughly south, then Tilman must be facing approximately north as he films them. This means that both Tilman and the plane are travelling roughly from east to west. We do not need to know the precise compass bearing, the general direction of travel is enough. This brings the third condition:
3. The location will be a train station on a railway line that runs roughly east-west and not north-south.
Lastly there is the architecture. I don’t like to rely on these clues as much because architecture varies so much but the general style of the buildings reflects the Netherlands, Belgium, or parts of Germany. This could not be Austria, or the UK for example. This is our fourth and last condition:
4. The location is quite possibly in Germany, the Netherlands, or Belgium.
Condition #4 is not as strong as the others, but at least it gives an area to start our search.
Putting The Conditions To The Test
So we now have four conditions that we know to be true of the location where Tilman is. They are:
1. Close to an airport, train line parallel with runway.
2. Flat, rural/semi-rural location.
3. Station on a line that is roughly east-west.
4. Possibly in Belgium, the Netherlands or Germany.
To check for locations that match these conditions, we can use Google Earth Pro. To make it simple we can disable all the layers in the map except for place names, railways, and airports, like this:
This leaves our target area looking like the image below. The railways are black lines, the airports are marked with the plane symbol:
There are only a few airports, so it will not take long to check them. We can strictly apply our true conditions and immediately reject a lot of the possible candidates if they do not match all four conditions. Düsseldorf airport is next to a railway, but this line runs north-south and not east-west so it cannot be right. The same goes for Eindhoven, Cologne, Antwerp, and Rotterdam so we immediately reject them. Münster-Osnabrück is not near a railway at all, so we can reject that too. Amsterdam Schiphol has a railway line nearby that is parallel to one of the runways, could that be it?
Nope. On closer inspection the orientation is impossible, Tilman could not look north from this train line and see a plane that is landing at Schiphol. A landing plane would be flying the opposite way to the one in the video. Schiphol might just meet conditions 2,3 and 4, but the angles aren’t right for condition 1, and so we can reject it. The most obvious remaining candidate is the airport at Brussels:
It meets all the conditions:
1. The railway line (the one going through Kortenberg) runs close to the airport, and is approximately parallel to the runway.
2. Flat rural/semi rural location.
3. The line runs along an east-west axis, so Tilman would have seen a landing plane to the north as he looked out.
4. It is in Belgium.
So is this right? It is certainly not wrong but now we need to zoom in and do a little verification to confirm.
Verifying The Location
There are only three stations that Tilman could have passed through on this bit of line as he took the video: Kortenberg, Erps-Kwerps, and Veltem. We already know that Tilman was looking roughly north from the railway line because of the solar panels, so we should able to check if the location on Google Earth really matches the one in the video. We know that as Tilman approaches the station, he had a clear view across fields towards some distant trees.
This cannot be the case on the approach to Kortenberg because we cannot see the houses in this picture:
For the same reason it cannot be Veltem either:
Erps-Kwerps looks more promising though:
Now we need to focus on some details to see if what we’ve found at Erps-Kwerps matches with what we can see in the video. I’ve picked out some identifying features to check: a detached house with solar panels on the roof, three lamp posts, and a road:
We can compare them to Google Earth and find matches in the right place:
Looks good! We can do one more check just to be certain. This house has a distinctive solar panel layout:
It matches perfectly with this house right next to the station:
Perfect! By being methodical from the outset we have used features in the original video to determine what conditions must be true of the correct location. We have then identified a location that matched all of our four conditions, and we have tested our hypothesis by comparing stills from the video to aerial photographs which confirm our hypothesis. The correct location is the railway station at Erps-Kwerps in Belgium.
Finding The Train
We’ve found the correct location, but now we have to find the train. When deciding what to do next in an investigation it is often useful to conduct a little gap analysis to review what we have learned and decide what we need to focus on next. So what do we know?
- This photo was taken on a train heading towards Brussels at 09:22 on 17th October 2019.
- The train was passing through Erps-Kwerps station, which is about 12km from central Brussels.
What does this mean?
If we can work out which trains arrive in Brussels after 09:22, we can work out which train Tilman was on. Given the travel time, the train should arrive sometime between 09:30 – 09:45.
What do we need to know?
Which trains arrive in Brussels between 09:30 – 09:45?
How do we find out?
Finding a train timetable which will show arrivals at the relevant period.
I couldn’t easily find a collection of historic Belgian train timetables, but I reasoned that since Tilman travelled on a Thursday, the train timetable for any other Thursday would be fairly similar. This was a slight risk, but train timetables are usually fairly consistent. This was the arrival schedule for Thursday 25th June at the relevant time period:
There are several possibilities, but the one that really stands out is the train from Frankfurt(Main)Hbf. The train service number is ICE 18. Tilman lives in Germany and his Twitter history suggests he is familiar with Bonn or Cologne, so I reasoned it was probable that he travelled to Brussels from that direction.
To be more certain it would be helpful to know a little bit more about the train timetables from October 2019. Fortunately there is a searchable archive of historic German train timetables that we can check (I recommend adding this to your bookmarks if you take part in Quiztime regularly).
This is the best fit for what we know about Tilman and what we have learned about the train journey in the video. The train is ICE 18 from Frankfurt to Brussels.
Finding The Plane
In theory the last part should be easy enough because we have already done the hard work. Unfortunately in practice it was harder to source the information than I had anticipated. We know the airport was Brussels, and we know the plane landed there at or about 09:22 on 17th October 2019. Easy right? All we need to do is visit a site like FlightStats, FlightRadar24, FlightAware or Airportia and get a list of all the flights that arrived in Brussels at that time and identify the correct plane. The big issue with this is that accessing flight data that is more than a few days old gets really expensive. For example if I wanted to get data from FlightAware a single year’s worth of data would cost me about $400. As much as I love Quiztime, $400 to answer one quiz question makes it an expensive hobby.
It wasn’t really possible to work back from today’s schedule and see if it matched with last October’s schedule in the way I had done for the train timetable either. Airline schedules are less predictable, and the impact of COVID has meant that flight schedules look completely different to how they did a few months ago. Instead I signed up for a trial of FlightRadar24 Gold, which allows access to 12 months’ worth of historic information. After no success with trying to match current flights with historic ones I was grateful for the input of Philipp Bock who pointed out that with a gold account it is possible recreate historic views of the sky from any point in the last 12 months using the air traffic map. He correctly identified that the flight at that time must have been RO371 from Bucharest to Brussels.
Here we can see what the sky looked like at exactly 09:22 on 17th October 2019 and where the plane was relative to Tilman’s position at Erp-Kwerps at the time he made the video:
If you found this post useful, you can read the rest of my Quiztime posts here.