🔧 Fix Aircraft registrations Joins & relationships Notebook By · Updated

Fixing FAA Registry County Codes That No Longer Exist

On this page

Problem statement

The FAA registry gives each owner's county as a three-digit code, which with the state's two-digit FIPS code makes the usual five-digit county GEOID. Join it to current county boundaries and about one record in a hundred finds nothing. The codes are not wrong so much as old: the registry still uses county codes that the Census Bureau has since retired, so those aircraft drop off any county map without an error. This page finds the retired codes in the 16 October 2026 file and puts the records back by placing each owner's ZIP code in a 2024 county.

Quick answer

Build the GEOID and look for codes the 2024 boundary file does not have:

us = reg[(reg.COUNTRY == "US") & reg.STATE.isin(STATEFP.index)].copy()
us["GEOID"] = us.STATE.map(STATEFP) + us.COUNTY
miss = us[~us.GEOID.isin(counties.GEOID)]
print(f"US records: {len(us):,}; county code not in the 2024 boundaries: {len(miss):,} ({len(miss) / len(us):.1%})")
print(miss.GEOID.value_counts().head(8).to_string())
US records: 311,085; county code not in the 2024 boundaries: 3,624 (1.2%)
GEOID
12025    1651
09001     410
09003     334
09009     289
02261     224
09011     178
09005     159
09007      96

3,624 US records, 1.2%, have a county code that does not exist in the 2024 boundaries. Three groups account for nearly all of them: Florida's 12025, Connecticut's eight 09001 to 09015 codes, and Alaska's 02261.

Horizontal bars of the retired county codes in the registry: 1,651 records under 12025, Dade County, Florida, the eight old Connecticut county codes, and 224 under 02261, Valdez-Cordova, Alaska. Horizontal bars of the retired county codes in the registry: 1,651 records under 12025, Dade County, Florida, the eight old Connecticut county codes, and 224 under 02261, Valdez-Cordova, Alaska.
Retired county codes still in the FAA registry.

Three retired codes and a better key

1. Who the missing codes are

for g in ["12025", "02261"]:
    print(g, miss[miss.GEOID == g].CITY.value_counts().head(3).to_dict())
print("Connecticut:", miss[miss.STATE == "CT"].COUNTY.value_counts().to_dict())
12025 {'MIAMI': 767, 'SARASOTA': 148, 'CORAL GABLES': 90}
02261 {'CORDOVA': 64, 'GLENNALLEN': 57, 'COPPER CENTER': 41}
Connecticut: {'001': 410, '003': 334, '009': 289, '011': 178, '005': 159, '007': 96, '013': 81, '015': 57}

12025 was Dade County, Florida, renumbered 12086 when it became Miami-Dade in 1997; the registry still uses the old code for 1,651 records, mostly in Miami and Coral Gables, though 148 give Sarasota as the city. 02261 was Alaska's Valdez-Cordova Census Area, split in 2019 into Chugach and Copper River (02063 and 02066). Connecticut's eight codes are the counties the Census Bureau replaced with nine planning regions in 2022.

2. Place each ZIP code instead

gaz = pd.read_csv(zipfile.ZipFile(Z.open("2024_Gaz_zcta_national.zip")).open("2024_Gaz_zcta_national.txt"), sep="\t", dtype={"GEOID": str})
gaz.columns = gaz.columns.str.strip()
pts = gpd.GeoDataFrame(gaz[["GEOID"]].rename(columns={"GEOID": "zcta"}), geometry=gpd.points_from_xy(gaz.INTPTLONG, gaz.INTPTLAT), crs=4326)
zcta_county = gpd.sjoin(pts, counties[["GEOID", "geometry"]].to_crs(4326), predicate="within").set_index("zcta").GEOID
us["zip_county"] = us["ZIP CODE"].map(zcta_county)
print(f"ZCTAs placed in a county: {len(zcta_county):,}; US records with a ZIP county: {us.zip_county.notna().mean():.1%}")
ZCTAs placed in a county: 33,736; US records with a ZIP county: 97.1%

The 2024 ZCTA gazetteer gives each ZIP code area an internal point. A spatial join puts 33,736 of those points in a 2024 county, and 97.1% of US records have a ZIP code that can be placed this way.

Two bar charts of where records with retired codes land when placed by ZIP code: 12025 records mostly in Miami-Dade 12086 and 119 in Sarasota 12115; 02261 records split between Chugach 02063 and Copper River 02066. Two bar charts of where records with retired codes land when placed by ZIP code: 12025 records mostly in Miami-Dade 12086 and 119 in Sarasota 12115; 02261 records split between Chugach 02063 and Copper River 02066.
Placed by ZIP code, the retired codes' records land in current counties.

3. How often the two agree

both = us[us.GEOID.isin(counties.GEOID) & us.zip_county.notna()]
print(f"records with both: {len(both):,}; same county: {(both.GEOID == both.zip_county).mean():.1%}")
fixed = miss.assign(zip_county=us.zip_county)
print(fixed.groupby("GEOID").zip_county.agg(lambda s: s.value_counts().head(2).to_dict()).loc[["12025", "02261"]].to_string())
records with both: 298,495; same county: 93.6%
GEOID
12025    {'12086': 1393, '12115': 119}
02261       {'02063': 89, '02066': 73}

Where a record has both a valid FAA county and a ZIP county, they agree 93.6% of the time; ZIP areas cross county lines, so some disagreement is expected. For the retired codes the ZIP points give the answer: most 12025 records land in Miami-Dade (12086), and the 119 in Sarasota County (12115) show the old code was wrong for them anyway. 02261 divides between 02063 and 02066.

A rule for the whole file

The data used on this page

The FAA's Releasable Aircraft Database, public domain, downloaded on 16 October 2026 and saved on this site, because the FAA replaces it every day. The saved copy leaves out the owners' names and street addresses and keeps five-digit ZIP codes; every other column is as published. With it come the Census Bureau's Vintage 2025 county population estimates, 2024 county boundaries and 2024 ZCTA gazetteer.

from pathlib import Path
import zipfile

import geopandas as gpd
import numpy as np
import pandas as pd
import requests

Path("data").mkdir(exist_ok=True)
# FAA Releasable Aircraft Database, 16 October 2026: MASTER.txt without the NAME, STREET, STREET2 and OTHER NAMES
# columns and with five-digit ZIP codes (MASTER-no-names.txt), ACFTREF.txt and ENGINE.txt as published; Census
# Vintage 2025 county estimates, 2024 cartographic boundary counties and 2024 ZCTA gazetteer. Saved here unchanged
# otherwise, because the FAA replaces the file daily.
# Live: https://registry.faa.gov/database/ReleasableAircraft.zip (needs a browser User-Agent),
# https://www2.census.gov/geo/docs/maps-data/data/gazetteer/2024_Gazetteer/2024_Gaz_zcta_national.zip
ARCHIVE = "faa-aircraft-registry-2026-10-16.zip"
if not Path(f"data/{ARCHIVE}").exists():
    Path(f"data/{ARCHIVE}").write_bytes(requests.get(f"https://data.spatialworkflow.io/{ARCHIVE}", timeout=600).content)
Z = zipfile.ZipFile(f"data/{ARCHIVE}")


def faa(name):
    """One registry file: comma-separated, every field padded with spaces, a byte-order mark and a trailing comma."""
    d = pd.read_csv(Z.open(name), dtype=str, encoding="utf-8-sig", keep_default_na=False)
    d.columns = d.columns.str.strip()
    d = d.loc[:, [c for c in d.columns if c and not c.startswith("Unnamed")]]
    return d.apply(lambda c: c.str.strip())


reg = faa("MASTER-no-names.txt")
ref = faa("ACFTREF.txt")
TYPE_AIRCRAFT = {"1": "glider", "2": "balloon", "3": "blimp", "4": "fixed wing single engine", "5": "fixed wing multi engine",
                 "6": "rotorcraft", "7": "weight-shift", "8": "powered parachute", "9": "gyroplane", "H": "hybrid lift", "O": "other"}
REGISTRANT = {"1": "individual", "2": "partnership", "3": "corporation", "4": "co-owned", "5": "government", "7": "LLC",
              "8": "non-citizen corporation", "9": "non-citizen co-owned", "": "blank"}
Path("data/cb_2024_us_county_20m.zip").write_bytes(Z.read("cb_2024_us_county_20m.zip"))
counties = gpd.read_file("data/cb_2024_us_county_20m.zip")
STATEFP = counties.drop_duplicates("STUSPS").set_index("STUSPS").STATEFP
pop = pd.read_csv(Z.open("co-est2025-alldata.csv"), encoding="latin-1", dtype={"STATE": str, "COUNTY": str})
pop = pop[pop.SUMLEV == 50].assign(GEOID=lambda p: p.STATE + p.COUNTY)
print(f"FAA registry, 16 October 2026: {len(reg):,} aircraft, {len(ref):,} make-and-model codes")
FAA registry, 16 October 2026: 317,604 aircraft, 94,115 make-and-model codes

Example 1: keep the FAA code where it exists, fall back to the ZIP

us["county_fixed"] = us.GEOID.where(us.GEOID.isin(counties.GEOID), us.zip_county)
print(f"records with a 2024 county: before {us.GEOID.isin(counties.GEOID).mean():.2%}, after {us.county_fixed.notna().mean():.2%}")
print(us[us.county_fixed.isna()].STATE.value_counts().head(4).to_dict())
records with a 2024 county: before 98.84%, after 99.98%
{'FL': 54, 'CT': 19, 'AK': 4}

Keeping the FAA code where it is valid and falling back to the ZIP county raises the share of records with a 2024 county from 98.84% to 99.98%. The few left have a ZIP code with no ZCTA to place.

Example 2: Connecticut by planning region

ct = us[us.STATE == "CT"].county_fixed.value_counts()
names = counties.set_index("GEOID").NAME
print(ct.rename(index=names).to_string())
county_fixed
Capitol                           373
Western Connecticut               299
Naugatuck Valley                  216
Southeastern Connecticut          167
South Central Connecticut         153
Greater Bridgeport                118
Lower Connecticut River Valley    118
Northwest Hills                    97
Northeastern Connecticut           49
Map of Connecticut's nine planning regions with the number of registered aircraft placed in each by ZIP code, from 373 in Capitol to 49 in Northeastern Connecticut. Map of Connecticut's nine planning regions with the number of registered aircraft placed in each by ZIP code, from 373 in Capitol to 49 in Northeastern Connecticut.
Connecticut's aircraft by planning region, from ZIP codes.

Connecticut's records now fall into all nine planning regions, from 373 in Capitol to 49 in Northeastern Connecticut, so the state can be mapped with current boundaries.