Building 3D Flight Plans with OpenSky Network and Google Earth
OpenSky Network Strikes Again
Last week, I examined how to use OpenSky Network’s REST API to build tables of flight data and learn more about particular aircraft. Today, I want to take that one step further. Using the same API, as well as Google Earth and some spreadsheet trickery, we can actually build forensic, three-dimensional reconstructions of flight plans in Google Earth.
A 3D reconstruction of a New York City Police Department helicopter’s flight.
OpenSky Network (and other flight tracking sites) are great, but the data they offer in their map or user interface is relatively limited. Usually, they’ll only show a snapshot of a flight at a point in time, a flight’s most recent flight path, or, at most, sites like FlightAware will show a 2D representation of an aircraft’s altitude while flying.
Why would visualizing more flight data be useful? Using the method below, observers can track minute changes in an aircraft’s altitude plotted over the aircraft’s exact flight path. This method could be used for detecting exactly when and where an airliner’s altitude dropped during an emergency situation, examining if police helicopters flew too low while monitoring protests, determining when a search and rescue aircraft found a sinking ship, or, as we will see below, tracking the precise locations and altitude of a US Air Force aircraft off the coast of Syria.
BEDEW76
On December 7th, several flight spotting enthusiasts noticed that a US Air Force RC-135U radar-tracking aircraft was doing laps over the eastern Mediterranean Sea:
The data contained in the tweet and screenshot tells us a few different things: its flight path, its callsign (BEDEW76), its departure airport (Chania), and its registration (64-14849). Sure, it also tells us the plane’s altitude at that very moment (35,424 feet), but doesn’t tell us what the altitude was in the past, whether it was doing any interesting maneuvers, or how the altitude matches up to features on earth. Also, in my opinion, a two-dimensional flight path just doesn’t look as cool as a three-dimensional flight path. To answer those questions and get a good looking 3D model, we need to return to the OpenSky Network API.
The API
Similar to the method described in last week’s post, OpenSky Network’s Flights by Aircraft API call can show us the exact times at which particular flights were in the air. As before, we plug our query into the example code below:
https://USERNAME:PASSWORD@opensky-network.org/api/flights/aircraft?icao24=3c675a&begin=1517184000&end=1517270400I replaced the username, password, icao24 code (ae01d5), beginning time (December 7 Unix time - 1607299200), and ending time (December 8 Unix time - 1607385600) fields with the values relevant to BEDEW76 and came up with this:
https://myusername:mypassword@opensky-network.org/api/flights/aircraft?icao24=ae01d5&begin=1607299200&end=1607385600I plugged that into a Chrome browser window and voila, BEDEW76 appears:
Note that there are two BEDEW76 flights listed - I think this was because the aircraft was out of range or not transmitting a position for a period of time. As we’ll see later, the two “flights” are actually one.
Next, we need to determine the waypoints for the flight. For the rest of the tutorial, I’ll use the second BEDEW76 listed as the example, but I’ll show the results for both at the end - after all, they’re two parts of a whole.
To determine the waypoints, we need to use a different OpenSky Network API call, this time focusing on the Track by Aircraft feature. OpenSky Network also gives us an example query for this call:
https://USERNAME:PASSWORD@opensky-network.org/api/tracks/all?icao24=3c4b26&time=0As before, I plugged in my username, password, the aircraft’s icao24 number (ae01d5), and time (1607328131 - the Unix timestamp corresponding to the “lastSeen” field in the first table). Here’s what that query looks like:
https://myusername:mypassword@opensky-network.org/api/tracks/all?icao24=ae01d5&time=1607328131After plugging that into a Chrome URL bar, we’re presented with an enormous array of data.
Each set of brackets corresponds to a single waypoint on BEDEW76’s flight. According to the OpenSky Network API documentation, the brackets contain:
The time of the waypoint
The latitude of the aircraft at that waypoint
The longitude of the aircraft at that waypoint
The altitude of the aircraft
The aircraft’s heading (on a 360 degree compass)
Whether or not the aircraft is on the ground
To build a 3D model of the flight path, we’ll use the first four data points of each waypoint.
Spreadsheet Trickery
Next, we have to get the data from the brackets into a format that can 1. Be easily read by Google Earth and 2. Be easily read by a human. To do that, first save the waypoints file as a JSON file (should be the default format).
Then use a JSON to CSV converter like this one to convert your file to CSV format. Finally, open it in Google Sheets, Excel, or a spreadsheet file editor of your choice.
The sheet should look something like this:
Can this be understood by Google Earth? Yes, but for humans, Column E is painful to read and virtually meaningless. To remedy that, insert a new column to the right of Column E and plug this formula into the cell:
=E2/(24*60*60) + DATE(1970,1,1)
Then, if you haven’t already, convert the cell’s default time display from “date” to “date time”:
If all goes well, you should have a human readable date/time in cell F2 that you can drag down the rest of the column.
Save the file as a new CSV and then we’ll be ready to move onto Google Earth, where the real fun begins.
Google Earth
Open Google Earth Pro and hit File —> Insert —> Choose your new CSV file from above. Make sure the field type is “Delimited” and the delimiter is “Comma” and hit next in the dialogue box that pops up. Then, select the columns that correspond to latitude and longitude, hit next, and then finish.
In the next box, choose the “Create new template” option and in the first three tabs of the following dialogue box, choose your name, color, and icon options. These aren’t super important - just select some options that seem good to you.
This part is important: in the rightmost tab, select the “Set height from field” option. Set the height field to whichever column contains your flight’s altitude values and ensure your mapping method is “Continuous”, rather than “Split into buckets”. Finally, manually change the minimum/maximum height fields so they equal whichever minimum/maximum values are in the flight altitude column. Your dialogue box should look something like this:
Hit ok and wait for Google Earth to take you to the region covered by the flight. Check the box for your file in the “Temporary Places” folder on the left and admire your handiwork!
I followed the same steps as above for the first BEDEW76 flight listed by OpenSky Network and added that flight path to Google Earth as well. Together, they show BEDEW76 took off from a base in Crete and did some high altitude figure eights off the coast of Syria. It then flew west to the southeast coast of Cyprus before dropping altitude and skimming the waves only a few hundred meters above sea level. Finally, it flew south to the northern coast of Egypt before looping northwest at its cruising altitude of about 10,300 meters and returned to Greece.
There it is - a 3D model of BEDEW76’s whole flight in the eastern Mediterranean on December 7th. The red pins are the first part of the flight and the white pins are the second part of the flight.
Feel free to zoom in and click on an individual waypoint to see more information about when it was recorded and the aircraft’s altitude at that point.
While looking at the overall flight plan can be interesting, breaking the flight into smaller sections is often more useful than viewing the flight in aggregate. In that spirit, let’s examine the cluster of waypoints southeast of Cyprus.
Between 9:11:03 UTC and 9:30:38 UTC, BEDEW76 dropped over 4,000 meters, at one point flying only 304 meters above sea level. Afterwards, it gained a bit of height and resumed the high altitude figure eight pattern off the coast of Syria. But what was it doing flying so low near Cyprus? Monitoring ship traffic? Getting a better reading on their radar monitoring systems? Going for a joy ride? I’m honestly not sure - but if you’re a pilot or someone with any insight reading this, please reach out and venture a guess.
Though it took a little finesse and some hopping between Chrome, Google Sheets, and Google Earth, this method is still pretty easy. Plus, the 3D map it produces could be invaluable to crash reconstruction, search and rescue operations, government analytical products, or aviation reporting.
As always, if you have feedback about this method (or want to see it used for other examples), feel free to get in touch. Drop a comment here or DM me at @LOActualControl on Twitter. Happy plane spotting!