Work in progress FAA database with ICAO type mappings

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 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

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.

FWIW the dump1090-fa datafiles contain a few things:

  1. a ICAO type designator (e.g. A388) to type description (e.g. L4J) mapping for each type designator (here: … types.json)
  2. 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: … 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 will probably regenerate the export for each x.y.0 piaware/dump1090 release. The data doesn’t change too rapidly.

I probably won’t continue my efforts here since they’re redundant. Is there an updated version of that hasn’t been checked in yet that can handle the extra ‘desc’ field in the FA CSV dump?

The existing version should handle that already? It just dumps all columns that aren’t “icao24” into the resulting json.


It does. I did something dumb the previous time when I got an error.