The US government collects all sorts of data and some of it is even useful for AvGeeks, aerophiles and others who happen to obsess a little bit about what planes they’ve flown on. Specifically, the Bureau of Transportation Statistics happens to have a record of (nearly) every domestic flight since 1995 operated within the USA including the aircraft registration, often called the N-Number or Tail Number. The data as they host it is not particularly easy to query, unfortunately. You cannot just feed a route and date and get back results. At least not in their interface. I decided to do something about that.
Welcome to the BTS Tail Number Lookup tool from WATT.
Put in your parameters and it’ll give you back all the commercial flights from that date between the airports in question according to the data maintained by the feds:
The columns are sortable (just click on the headings) and if there are more than 50 you can go to the next page using the buttons at the bottom of the table.
I’m working on updating the AA entries to map from American’s internal tail numbers (what they file with the DoT for some reason) to the N-Number painted on the tail. That should be mostly accurate for the currently active fleet; retired planes are a lot harder to figure it out on so that might not actually happen. Also, I’m missing October 2000 to May 2001 because of issues in the initial data import. I should have that resolved in the not too distant future.
There are roughly 115 million rows of data being tracked right now and I’ll keep it updated on a monthly basis as BTS publishes additional reports. One thing to keep in mind is that the data is not real-time; they publish in monthly batches ~2-3 months after the flights happen.
If anyone has any other ideas of cool things to visualize with the data let me know; I’m always interested in trying to make fun things happen with big datasets like this.
Have fun!
Never miss another post: Sign up for email alerts and get only the content you want direct to your inbox.
Two things I’d find handy-
1. A flight number search filter so I don’t have to scan the data by hand.
2. An endpoint that kicks data back in XML format to call from Excel
I’m working on an API-ish version, Brian. I’m also hoping to work with the folks at OpenFlights and FlightMemory to help integrate the data. If you have some more specifics on what you’d like to see out of such a call drop me a line. Now that I have the data putting it into a different output format should be relatively trivial.
I believe if you type a flight number in the search box at the top of the results list it will filter, but I’m not certain.
Server Error in ‘/’ Application.
The wait operation timed out
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ComponentModel.Win32Exception: The wait operation timed out
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
What search were you trying when it timed out? I’ve tweaked a lot of the DB calls to make things reasonable for querying 115mm rows but there might be changes I can still make to improve it.
Openflights integration would be my number one cool feature to add. It’d also be neat to be able to track the activity of a given tail number.
I’m working on both of those. I can do the N-number history pretty easily but there are potentially 20,000+ rows for a particular plane. That doesn’t render well on a website.
Render it as an animated map, i.e. show the movement of the aircraft over time as it zig-zags across the continent.
Thanks! Great tool!
The dual arrow button where you can flip the to/from would be helpful for searching simple direct return flights.
Good call; I can do that pretty quickly/easily.
Handy tool! But just a correction… it doesn’t include EVERY flight in the US since 1995.
I’ve had quite a few I’ve tried to pull up with no results.
I also have not been able to find all flights that have taken place since 1995, but I assume it’s an issue on the government’s end?
I would assume so. I’ve pulled the massive data dump from BTS and put it in to the system. If there is something missing I believe that BTS never had it to begin with.
Great tool! Are you planning to update the database with more recent months?
It is currently up to date through May 2014, the most current data available. I keep it updated on a monthly basis as the Feds release the data. There is a log on their end but I stay as current as I can.