Today I’m going to explain how I solved the Berlin quest from Til Man. But before I go any further I want to mention that @nixintel solved the quest already on November 16th (https://nixintel.info/osint/using-flight-tracking-for-geolocation-quiztime-30th-october-2019/).
The Quest
A very simple question, a not so easy solution..
Where is the photo taken?
Strategy
To solve this quest I a’m going to use QGIS…. again but also a few other tools. The strategy or steps to follow are:
1) Identify the approximately time and orientation on the photo
2) Identify the flight
3) Identify the city Til Man stays
4) Download the specific OSM data
5) Extract all the possible hotels
6) Eliminate hotels by query
7) Finding the corect hotel
Step 1
We know that this flight is flying from London to Hong Kong. So the aircraft is flying towards east. And we are likely facing in some sort of angle towards the west (South-West). If this is the case, then it is possible to estimate the time the photo could be taken.
To understand the orientation of the photo I created a sketch from the photo. I started drawing the buildings, next the flightpath and W to E directions. By now it is clear that the shadow of the sun goes from SE to NW and the hotel is located north of the flightpath. By using SunCalc it is possible to determine the time… around 8am CEST or 7am UTC.
Step 2
Identifying the flight on the photo. To know the direction/orientation of the photo The first thing to do is identifying the flight. Til Man told us that the aircraft is of the type 747–8F from London to HongKong. When searching on Google and reading on the Wikipedia page of the 747 aircraft. We learn that 747–8f is a designator for cargo aircraft. So that narrows a few airplanes and their flight on the list. When searching on flightradar I was only able to search back for a few days and was not able to identify the flight properly.
Luckily Tobi Bluhm already identified the flight. It is possible to download the KML/CSV file with the flightpath of flight 5X4 from UPS. In order to download the file, you need to create a trial account for 7 days (WARNING!! credit card needed so dont forget to unsubcribe).
Step 3
Determining the city. Using the pro account from flightradar we select flight 5X4 on 30 october 2019. While in play modus, it is possible to use a time slider and view the position of the aircraft at a specific time. We can clearly see that flight 5X4 was in the vicinity of Berlin around 7am UTC.
Step 4
Downloading and converting the specific OSM data for Berlin from geofabrik:
http://download.geofabrik.de/europe/germany.html and select the Berlin region in the osm.pbf format.
Now we need to convert the osm.pbf file to a sqlite data format. When you have the complete (advanced) version QGIS you also should have the command line tool “ogr2ogr.exe”. This tool is available in the OSGeo4W Shell which is installed on the desktop during the advanced installation of QGIS. In the OSGeo4W Shell I’m able to call the ogr2ogr tool. The command to convert the data is: ogr2ogr -f "SQLite" -dsco SPATIALITE=YES target_filename.db source.osm.pbf
When finished the terminal will show us a message.
Step 5
Extract all the possible hotels from the OSM data is pretty straight forwarded. I loaded the openstreetmap server as a background, the kml flightline from flight 5X4 and all the points in the berlin dataset. In OSM hotel icons are points so I’m searching point data.
As we can see there are a lot of points. So we need to extract all the hotels in The Berlin area. Using the ‘select by expression’ feature in QGIS and using the following query:
"other_tags" like '%tourism"=>"hotel"%'
By now we have almost 500 hotels selectec all over berlin. As you can see in the image below, there are a few hotels south of the flightpath. They can easily be deleted by selecting those by hand and delete the in QGIS.
Step 6
Eliminating rest of the hotels until we find the correct hotel. We already know that we are facing the flightpath in an angle. To eliminate hotels and narrowing the list further down I assume that we are not within 2500m of the flightpath. So I’m creating buffer zones starting from the flightpath to the north with spacings of 2500m. Why? Just a hunch and this way the hotels are almost equally divided into the 2 zones (Zone A and Zone B).
To do this we need to create 2 buffers around the (partial) flightpath. It is not very efficient when you create a buffer for the whole flightpath. So in stead we extract first the vertex’es from the flightpath. Vertex’es are the invisible points that make the line. To do this Vector>> Geometry Tools>> Extract nodes. Select as input the flightpath and output a name such as ‘FlightpathPoints’.
Now select and extract the points around berlin. Use the select feature tool and drag a selection box around the flightpath from west to east Berlin.
Click rightmouse button on the created layer (FlightpathPoints) and select Export>> Save Selected Features As... Name it something like buffer zone.
To create a lined buffer zone we need a line and not points, so we need to recreate a line from those points. Thats easy: 1) Processing
2) Toolbox
3) Search for 'Points to path'
Choose the partial points layer as input and choose a appropriate name.
It is now possible to create 2 zones/buffers on the partial flightpath. The create buffers tool from the MMQGIS plugin will help us with creating buffers. The first buffer is 2500m the second is from 2500–5000m.
In order to create the second buffer we need to extract the 2 upper points from the buffer and create a line from those two points. Just like we did with the flightpath. 1) Select Vector
2) Geometry Tools
3) Extract Vertices
4) Select the 'select feature tool'
5) Select the upper points of the buffer zone
6) Select and run the 'Points to path' tool
After creating the second buffer with the MMQGIS plugin you have 2 buffer zones that contain 340 hotels divided into 2 zones. At this point I choose to start with the upper zone. Selecting and extracting the hotels in the upper zone can be done by the Vector>> Research tools>> Select by location. In the tool choose the intersect, touch, overlap, are within and cross options. When done we have almost 100 hotels selected. If this is right buffer with the hotel in it we are done in 100 hotels otherwise we already eliminated 100 hotels.
We know that the view direction is towards the south-west or 220 degrees. We an tell QGIS to create wedge buffers for 100m each and from 220–230 degrees. When buildings from the type ‘apartment’ or ‘resident’ are touching those wedge buffers there is a possibility that it is our hotel and building.
In the processing toolbox search and select the ‘Create wedge buffers’. Use as input the Berlin hotels layer from the upper zone. Use 225 (thats between 220 and 230 degrees) as degrees, and as wedge width 20 degress (a bigger range for more hits, we can always narrow it further) the outer range is also in degrees. I converted for the Berlin area a range of 75m to degrees. That is 0.0006.
After the tool is finished we have wedge buffers on each hotel. The buffer zone must touch a building of the type ‘apartment’ or ‘residence’. If we run a selecting query based on location we can narrow the amountof hotels down to 59 hotels.
Those 59 hotels are ok to bruteforce with the google maps plugin. Some hotels like the one in the upper left corner of the above image can be eliminated instantly because of the road. In our photo we don’t have a road visible.
Step 7
Finding the correct hotel in Berlin. By scrolling every 59 hotels and interpreting the map in QGIS we can eliminate a few hotels just by translating the photo to the map. So are the hotels (1) in the left corner not possible. After a while (15 entry’s in QGIS) hotel H Mitte pops up. Examining this location in google maps (earth) gives me joy.
Conclusion:
To be honest, i dont know if QGIS was the best solution after reading the post of nixintel. He used google earth for the flight path and the construction of the borders. Where I used ‘dumb’ buffers and selected hotels.
In a future quest I’m going to look how i can integrate several tools into 1 verification project. Perhaps that it will work a bit more smoother.
But in the end the theory still works:
- Identify the orientation of the photo
- Find information about the aircraft
- Combine the two to identify the global location of til man
- Using OSM data to extract hotels and residential buildings
- Query the data in QGIS and bruteforce the last bit of possible hotels
Thank you for reading and see you next time!
Bearhunt
