Visualizing Flight Routes: Using the FlightXML API & CartoDB

A Brief History…

I’ve been obsessed with flying ever since my first flight at the age of 8, (which, for the record, was a Continental Airlines Airbus A300). For most of my life since then I dreamed hard about being able to identify planes as they flew overhead, and the day I discovered this flight tracking app, some time in the mid to late 2000s, my two decades long dream had become a reality. For the first time I could spot a plane, write down the time on my hand, and then check the site two hours later when the data was made available and know everything from the plane’s altitude, airspeed, airline, model, origin and destination. Aviation geek bliss.

Now, half a decade or so later, I can point my smart phone up at the sky and know instantly everything I’d ever wanted to know about the plane I was looking at and some, using apps like PlaneFinder and FlightRadar24. I can even create filters and know at any given moment the precise locations of all airborne 787 Dreamliners, (and as I type this, there are 54 of them flying world-wide; the flight from London to New York is just about 20 mins away from landing at JFK).

Around the same time that I was both starting to learn GIS and when I discovered that now seemingly archaic flight tracking up, I also began dreaming about some day mapping flights in the way that I wanted. But unfortunately, I was knee deep in learning classic desktop GIS software, and eventually found myself in a rigorous grad school program that left me no room to explore the burgeoning field of webmapping… that is, until now.

What follows is my process, from start to finish, of getting flight route data, cleaning up that data, and eventually animating a flight route. Although it’s nothing visually spectacular (yet), it is a start, and a start I’m excited about. This process is only the beginning, and I look forward to continuing this project and creating some really neat flight-related mapping visualizations in the future.

The Process:

Taking from Ben Fry’s Visualizing Data, I followed the “Seven Stages of Visualizing Data.” Here’s a quick outline of the stages and of the tools I used at each stage:

1. Acquire:’s FlightXML API / iPython Notebook
2. Parse: iPython Notebook / Excel
3. Filter: jetBlue #90 from SAN to JFK
4. Mine: CartoDB (georeferencing Lat/Long)
5. Represent: CartoDB
6. Refine: CartoDB (Torque categories)
7. Interact: CartoDB (Time slider)

Step 1: Acquire:’s FlightXML API

For quite a few years now, I’ve been mapping flights as static routes in either ArcMap or Google Earth by copying a table of latitude and longitude coordinates made available by, pasting it into Excel or in a text file, and then converting the data to XY’s. While this alone always kind of blew my mind, I always wanted to do so much more, and I knew there was a way, but I simply didn’t have the tools.

Using iPython Notebook, a “web-based interactive computational environment,” I used Python to access the FlightXML API. I had never used an API before, and my Python skills are only intermediate, but after reading through the API’s documentation, and brushing up on some Python, the API was fairly easy to use. I started out easy by focusing on just one flight, a flight I took on July 18, 2014 from San Diego to New York.

Screen Shot 2014-10-04 at 8.09.23 PMHere’s the API code from iPython Notebook that I used to access the flight route via the API:

Screen Shot 2014-10-04 at 8.16.38 PM

The most important things to point out in the API code are the methods and parameters used. First, I needed jetBlue Flight 90’s unique flight ID from 07/18/14, so I used the GetFlightID method, and then using the resulting ID I could use the GetHistoricalTrack method to get the lat/longs and other data returned back as JSON data. I did all of this using the HTTP Requests library for Python, which I had to download.

Step 2: Parse: iPython Notebook / Excel

For step 2, after getting the route data via the API, I saved the results in a text document, using Python’s file.write( ) method. The first bit of parsing occurred in iPython Notebook, by specifying comma delimited text in the output. Next, I opened the resulting text document in Excel and used text to columns to separate out the columns into a data format that could be uploaded to CartoDB, which is a super easy-to-use cloud-based mapping platform, where I’d ultimately create my flight route visualization.

Step 3. Filter: jetBlue Flight #90 from SAN to JFK
Since my raw data contained more or less exactly what I was looking for, not much filtering needed to be done. Really, the only kind of filtering that was done was beforehand, simply in choosing which route to download.

Step 4. Mine: CartoDB (georeferencing Lat/Long)
Mining, for Ben Fry, is “apply[ing] methods from statistics or data mining as a way to discern patterns or place the data in mathematical context.” In this case, again, due to the simplicity of the raw data, the only sort of mining I had to do was to georeference the XYs, which is to place the latitude and longitude values on a map, and this was done easily using CartoDB’s georeferencing wizard.

5. Represent: CartoDB
After georeferencing the data, creating a basic visualization in CartoDB was straightforward. Here is the default:

Screen Shot 2014-09-26 at 9.15.51 PM

6. Refine: CartoDB (Torque Categories & SQL)

The default visualization wasn’t what I was going for. I wanted first of all to animate my map, but also, I wanted the flight to stand out from the background. I didn’t think such a colorful and detailed basemap made sense for this visualization, so I changed that right away to something less clutered. I also began to play around with the Torque Categories feature, which would allow me to animate my map based on a time variable.

Screen Shot 2014-09-26 at 9.20.45 PM

After tweaking the settings – adjusting the marker size, removing the marker outline, experimenting with the number of trails and blend mode – I finally started to see something that resembled my vision… and best yet, my plane started moving! A culminating moment for me.

I also wanted to distinguish between ascending/descending and cruising phases of flight, so I used those variables as the torque categories. I had to do a bit of data massaging to get this to work though. Flightaware includes the plane’s altitude at all data points, so in CartoDB, I created a new column, used an SQL query to select all altitudes greater than 35,000 feet, and populated that new column with the attribute of “cruise.” I populated the rest with the attribute “A/D” for “ascent/descent.”

Screen Shot 2014-09-27 at 3.42.55 PM

7. Interact: CartoDB (time slider)

At this point, the visualization was more or less complete, but I just had to make sure it had the right interactivity I wanted before making it live. Fortunately, CartoDB makes this extremely easy for you by offering out of the box options for a slider, zoom, layer selector, search box, etc. For this visualization, I chose only the slider and zoom.

And that’s it. Here’s the animation in its final form. Watch jetBlue Flight 90 speed across the country along the actual route it took on July 18, 2014, with me on board!:

Screen Shot 2014-09-26 at 9.25.04 PM

Trouble-shooting and Problem-solving:

While this process might appear fairly straightforward (and could in fact be even more striaghtforward), there were inevitable hicups along the way. For one, Flightaware returns the flight’s date in an “epoch” time format, which is seconds since January 1st, 1970. I hadn’t realized this until I brought my data all the way into CartoDB, only to realize CartoDB couldn’t read epoch time. I ended up converting epoch time to date in Excel, but even still, CartoDB refused to recognize the date. So instead of using the real date to animate the route, I simply used the CartoDB ID, which fortunately was in the correct sequential order. This was an issue I sort of made peace with for the sake of time, but something that will definitely have to be problem-solved in the future. And in an effort to find some help on the matter, I posted my question about how to convert Epoch time on the very awesome “question and answer site” Stack Overflow.

Lastly, to give credit where it is due, a few of my instructors in the SAVI GIS & Design Certificate Program, were indispensible in helping me through some tough moments, and for giving me all of the tools (finally!) to make this happen. So a huge thanks to them.

As for me, I’m looking forward to making some more visualizations, and I’m already working on iteration #2 of this one!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s