The Flight Paths Tracker: How to Monitor and Analyse Air Traffic Above Individual Locations
By Julius Troeger, reporter and data journalist at Berliner Morgenpost.
A flight from one Berlin airport to another takes 15 minutes. Hundreds of private jets and airliners fly over the German capital at low altitudes without any passengers on board. This is only one of the stories that were uncovered by using the database of our interactive application Flight Paths Tracker, published in April 2013.
Flight traffic above one location in Berlin
The database is updated on a daily basis and contains detailed information on almost 600.000 flights in the Berlin region. Millions of coordinates of flights and noise levels allow us to provide our users with customised data about air traffic above a certain location in an intuitive and friendly way. In addition, this resource is essential to our reporters in their investigations on one of the main discussion topics in Berlin.
How the idea came about
Everything started with a refusal. We asked the German Air Traffic Control (Deutsche Flugsicherung) to provide raw data in order to match flight paths with certain locations in Berlin. We wanted to show how increased air traffic affects different parts of the city. Air traffic is different every day and depends on weather, wind and the number of airplanes in the sky. Predetermined flight paths can’t be analysed like railway tracks or highways. Pilots sometimes have to leave their originally planned routes. This is the reason why some areas where there should be no planes at all are still affected by noise pollution.
But the German Air Traffic Control refused to provide the radar data we needed. Since we also needed historical flight data for statistical calculations, scraping the data (Java-Plugin), was not an option and nor was using a receiver to get ADS-B transponder data. After several months of research we started to cooperate with Deutscher Fluglärmdienst (DFLD), an NGO that monitors and records air traffic information. They provide historical and recent flight data, including noise calculations.
How we made the app
The Flight Paths Tracker is a Ruby on Rails app running with a PostgreSQL database with a PostGIS extension to do spatial queries. The prototype was built in New York with developers and reporters at ProPublica. In November 2012 I was chosen to be the first P5 (Pair Programming) fellow at ProPublica. Back in Germany, the developers of Kreuzwerker GmbH helped us to finalize the interactive application based on that prototype.
At the same time we got about three gigabytes of CSV files (96% of all the flights over Berlin since January 2011) from the DFLD. We analysed them using mainly SQL queries with pgAdmin. We exported the data, visualized it with Datawrapper, QGIS, Google Earth and Excel and double-checked for consistency using ProPublica’s data bullet-proofing guide.
Flight traffic for all districts in the Berlin area
How we presented the data
We had two objectives concerning the frontend. Firstly, we wanted to display flight traffic above one’s location in 3D. This way the user is able to distinguish single flights as they are expanded horizontally. The rendering is done directly in the browser with CSS and SVG - without any use of plugins. Secondly, we wanted to visualise a long-term analysis of how air traffic evolves over Berlin. We implemented that by using a simple choropleth map.
It was also very important that the news app would be working on mobile devices. Therefore we used responsive design. We used deep links to make sure that users are able to easily share the results of the Flight Paths Tracker wherever they are in the app.
The implementation of the 3D map was particularly tricky. Even though it is the key feature of the application, it’s based on experimental features. We had trouble (especially with Chrome and iOS) displaying a large number of flight paths, so we had to limit them. The 3D map doesn’t work with Internet Explorer yet, because the browser does not support the "preserve-3d" property.
Due to tight deadlines it was impossible to include features and ideas we developed while working on the application. The lesson learned is that we have to work in a more agile fashion.
Impact and next steps
The Flight Paths Tracker is work in progress. The data is updated on a daily basis and automatic analysis is provided on a monthly basis. Every day several hundred people use our interactive application. Right now we are working on additional articles and last week we established a Twitter account (@flugroutenradar) that automatically tweets interesting data about flight traffic above Berlin from the API of the news app.
We generated front-page news in print and lead stories online. Even Berlin's politicians reacted to the results. People affected by flight-related noise pollution sent countless letters thanking us for the new source of information. The news app has also been mentioned as best practice example in blog posts and at conferences. Furthermore, we are honoured to be included on the shortlist of the Data Journalism Awards 2013.
Note: A how-to post in German is available on my blog, digitalerwandel.de.