Posted by: Denny Cherry
SQL, SQL Server
Something’s been bugging me ever since we lost track of MH370 (Malaysia Airlines Flight 370) last week. And that is, “How the hell can we loose an airplane?”
According to this MSN article (who got it from Reuters) that the airplane pings the maintenance system “about once an hour” and that 5 or 6 of these pings were received from the airplane after Malaysian air traffic control lost contact with the aircraft. Apparently there is an option to have the airplane feeding real time data up to whoever reads this data, but this airline doesn’t pay for that service.
So my next question becomes, “Why isn’t having this turned on mandatory?”
Gathering this much data really isn’t all that complex, even when you account for every single commercial aircraft in the world. According to FlightRadar24 there are about 6700 commercial airplanes flying at any one time (I’m looking at 6pm Pacific Time). FlightRadar24 gets its data from the FAA in the US and the ADS-B system in the rest of the world. According to the Daily Mail only about 60% of commercial airplanes have ADS-B, so we can assume that there are about 11,200 airplanes in the air at any given time. Getting real time data feeds from them with just the basic information needed to locate the aircraft in the event of an emergency like say when an aircraft disappears in the middle of an ocean really doesn’t require all that much information to be transmitted and we don’t really need it to be transmitted all that often.
We’d need the aircraft number, the flight number would be nice but isn’t really needed, the altitude, airspeed, GPS location, heading and the rate of climb (or decent). In other words a bunch of numbers need to be transmitted by the computer say, once a minute. I’d say that in a pinch every 5 minutes would be acceptable. That’s about 40 bytes of information to transmit, we’ll say 1000 bytes so that we can wrap it in XML when we send it. That data comes in to some centralized system which monitors every airplane world wide. That’s all of ~16M rows of data a day that would need to be stored and made available.
According to this article on MSN (again from Reuters) the BEA director Remy Jouty has stated that the technology to send continuous data was not yet “mature” enough. What makes them think that? It’s just data being sent over a satellite link. If we can stream data to and from every satellite in orbit and track every piece of space junk that’s in orbit I think we can track a few airplanes.
“Sending continuous data for all flights would create a huge amount of data and might get very difficult to monitor and analyse,” said Tony Tyler, director general of the International Air Transport Association…
This is my favorite part (from a data perspective) of the entire MSN article. Based on the numbers above at ~16M rows, being stored as 1000 bytes of information per row that’s all of ~15 Gigs of information per day. I can fit that on a thumb drive, or my phone, or any iPod out there. Say we keep the information for 7 days in the live system (we can archive it off and do all sorts of cool analytics on it on another system), that’s ~105 Gigs of data. Now I’ll need two thumb drives to store it (I’m only accounting for the thumb drives which are currently sitting in my laptop bag as I write this). Whoever Jouty and Tyler are talking to about putting together this kind of system apparently aren’t very good at handling data.
A system like this doesn’t need to have every bit of dynastic data that’s available in the black box. This sort of data should be used to go find the airplane, which has the black box in it. This way the data being transmitted from the airplane to the system is kept small (just in case satellite time really is expensive still).
I really don’t think that building a system like this would take all that long to put together. I’m fairly confident that me and a couple of friends could put something together in a week. The trick would be getting the airlines to all start feeding data to it in real time from the airplanes. And of course this system needs to be secure which means that the system can’t be turned off as long as the airplane is powered up.
Frankly I can’t find one good reason that a system like this shouldn’t be built and used, and I can’t for the life of me figure out why it hasn’t already been built.