Before obj kindly pointed out that dump1090-fa includes an aircraft registration database dump from FlightAware, I had already started working on a version of the FAA registry with ICAO type mappings. To make sure continued efforts wouldn’t be a waste of time, I loaded the GUI from dump1090-fa on my dump1090 mutability instance and found that I have some aircraft that aren’t included in dump1090-fa, so I’m going to continue working on it as I have time.
I started by grabbing the aircraft registration database .zip file from http://www.faa.gov/licenses_certificates/aircraft_certification/aircraft_registry/releasable_aircraft_download/. Next, I imported the relevant columns from ACFTREF.txt and MASTER.txt into two tables in a MariaDB database. After doing that, I started adding the ICAO types to the FAA manufacturer/model codes. This allows me to do a single SELECT statement to export aircraft registration records into a CSV file suitable for use with csv-to-json.py.
As I see an aircraft model that I haven’t done, I’m adding ICAO type mappings for all models from that manufacturer. It’s still far from complete, but I’ve currently got ICAO types for about 113K FAA registered aircraft. I plan on updating to a newer FAA snapshot a few times per year.
I’m keeping my work in this GitHub repo in case anybody is interested. The CSV file is available directly here.
a big static data export from FA’s internal data, which includes the FAA data. For the FAA data there are two conversions done:
2a) manufacturer and model to ICAO type designator (this is basically the work that you’re doing; unfortunately I can’t release the code that does this conversion)
2b) aircraft and engine data from the FAA registry to a type description
This may seem redundant but it turns out that there are lots of conversions and nonstandard setups where the individual aircraft/engine data for particular aircraft contradicts what you would imply from the type designator.
I notice that I forgot to add the input file used to build the json db for dump1090-fa to github, I’ll do that in a moment. (Here: github.com/flightaware/dump1090 … 914.csv.xz)
The map code uses the designator, description, and the description implied by the designator (in roughly that order) to try to find a marker to use.
obj, thanks for that info and for adding the input file for building the JSON DB. Does FlightAware plan to periodically update update the static data export? If so, I’ll probably look for somewhere else to focus my efforts.
Also, a big thanks is due to FlightAware for publishing this data. Doing the conversions is a massive pain because the FAA data is nowhere near as clean as one would hope, and I’ve seen some of the inconsistencies in during my own work so far.
I probably won’t continue my efforts here since they’re redundant. Is there an updated version of csv-to-json.py that hasn’t been checked in yet that can handle the extra ‘desc’ field in the FA CSV dump?
