Mar 252014
 

The Raspberry Pi is an amazing piece of kit. There’s little it cannot do with sufficient tweaking. I have a passing interest in planes, and love FlightRadar24 (iPad app and website). 

fr24

FlightRadar24 screenshot

I started to wonder how they got this data. A quick rummage around their website reveals they mostly rely on a world wide network of volunteers to collect and then feed them data. You need ‘line of sight’ to an aircraft to be able to query the information, and no one entity can afford to do this globally. So, a network of volunteers run ‘feeder radar stations’ (ok, it isn’t really ‘radar’ but, more the Next Generation version of). 

Hardware I Use

I use a ‘stock’ Raspberry Pi model B, connected via ethernet to my home network ordered via RS Components (fwiw, they’re slow as heck… order one from somewhere else…!). My Pi is strapped up under my desk out of the way. It has a 4 port (unpowered) USB hub connected to it (the black blob underneath) but otherwise it is entirely unremarkable. I’m even still using the original RS Components 4Gb SD card. 

Excuse my camera charger lurking underneath it all – it’s entirely unrelated!

raspberry

My Raspberry Pi, mounted under my desk

Hardware wise, to receive the ADS-B broadcasts from planes overhead, I use a DVB-T (R820T) Stick with an external aerial attached to it. I ordered mine from 1090mhz.com (based in Germany) and it arrived in the UK about 4 days later in perfect condition, and even had an adaptor and external aerial included in the package – thanks guys! This is the ‘best’ stick to use – apparently – as it is based on the R820T chipset.

Software I Use

I use a modified version of dump1090, originally installed using instructions from David Taylor’s SatSignal.eu website. dump1090 is fantastic. Using the sdr-rtl drivers (also documented on David’s site) to re-tune my DVT-B Stick  from digital TV to the 1090MHz used by ADS-B, allows it to receive the ‘next gen’ radar data right from the planes in the sky themselves. 

dump1090 then takes this feed and decodes the data into something human rather than machine readable. Using the –interactive mode, you can see the output as the planes fly by.

interactive

dump1090 –interactive

 

Perhaps even more exciting than that though, is the –net option, which enables not only all the data sockets so that FlightRadar24’s software can pull the information out of dump1090 (setup instructions here), but also enables a built-in web server so you can run and view your own miniature version of FR24:

dump1090

Screenshot of my own ‘dump1090’ FlightRadar24-style output (–net option)

 

MySQL Support

You may remember I said I use a modified version of dump1090. That is because as well as everything above, I also wanted to store a local copy of all the data I receive in a MySQL database so I can do my own manipulations and calculations. While there is a MySQL’d branch of dump1090 on github, it is dozens of commits behind the main branch and missing out on a lot of the hard work Malcolm Robb has put in to his master branch and fork of antirez’ original work. 

So, rather than forego either mysql, or using the latest version of dump1090, I hacked them together and re-introduced mysql support into the latest version of dump1090. 

To keep updates to future versions easy, there are very minimal changes to all other source code/header files of the official dump1090 branch. 95% of mysql support code is contained within mysql.h and mysql.c with pretty much the only main branch changes being the inclusion of mysql headers and a new struct in dump1090.h, the –mysql switch handler in dump1090.c, and a call to modesFeedMySQL() in mode_s.c (that could even be moved to mysql.h I suppose to separate it even more .. but I just put it with all the other structs for consistency). 
 
This should make it relatively simple for me/you to upgrade each time a new version comes out. 
 
MySQL authentication credentials are now in mysql.h rather than buried deep in the codebase. If it’s something lots of people show an interest in, the database credentials could even be supplied on the command line for even greater simplicity and portability. We’ll see… 
 
If you’d like the latest version (1.08.1003.14) of dump1090 with mysql support, you can get it here
 
Happy flying!

  14 Responses to “ADS-B (dump1090) with MySQL Support on the Raspberry Pi using a DVB-T (R820T) USB Stick”

  1. very interesting with your articles, how to install and using stored data on mysql to display on screen? does mysql need to be installed on local raspi or on networked mysql installation?

  2. Do you have also written a webbased frontend for the database?

    • I haven’t got around to that (yet). There are already awesome front-end projects out there though. I’m not sure there’s really a need for another.

  3. can you give a tutorial how to setup raspberry pi with dump1090 mysql logging

    • Aside from setting up the MySQL database, it’s exactly the same as the setup link in the article. You can google ‘how to set up mysql’ to find a tutorial to guide you through the mysql step if required. It’s all fairly generic, and the database schema is included in the download.

  4. Hello,

    I just installed dump1090 with mysql support and connected it with a mysql database. What a useful add-on! Thanks for providing this software.

    What I understand is that there is only a single row per icao number allocated to the flights table – regardless whether it is a different flight using the same aircraft or even still the same flight (e.g. after an dropout). So there is no real flight history.
    Is there any possibility to keep the history of a flight or an aircraft? Maybe after 60 seconds idleness?

    Also when the signal strength (and thus the amount and plausibility of information) is weak during the capture of a flight, especially at end of range, the last information written into the table might contain less data or even errors (wrong altitude, speed etc.) compared to what was transmitted just before.
    Maybe it is possible to freeze somehow “good” data?

    I had a look into the mysql.c file. I think there is the relevant code for this problem. But I’m sorry. I am not familiar with it. Maybe you can give a hint what to do or even include it in a future version?

    Many thanks
    Fred

    • Hi Fred, thanks for your comments!

      I must admit that since I did this, I have found many of the issues raised above, but lacked the time to fix them appropriately. I also moved on to using Virtual Radar Server (fed by the ‘stock’ dump1090) to provide history, flight data view and so forth.

      Maybe one day I can put it all right again… the project isn’t dead! 🙂 It’s just asleep….

      Lee

  5. Thanks for the code!

    I’m running dump-mutability and just integrated your modifications. After some pain with the Makefile, dump1090-mutability compiled without errors and it is running now. I will watch that for a while and then integrate Fred’s suggestion from above.

  6. I’m also having trouble with makefile. Would did you do to fix it?

  7. Hi, i set my vars in mysql.h and compiled with no errors, i used your .sql file in /tools to setup db tables…./dump1090 runs as expected, even webserver(port 8080) but mysql isn’t updating any data, my mysql.log isn’t showing any errors…any ideas?

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)