NTSB investigations: American AA-5

67 cases on aircraft the registry lists as American AA-5. Aircraft are matched to the registry by N-number and event date, checked against the reported serial number, make and model, so older matches can be wrong.

Defining event

Finding

Phase of flight

Aircraft model

Cases, newest first

Query with SQL

SELECT c."ntsbNumber", c."eventDate", c."eventType", c."eventCity", c."eventStateOrRegion", a.n_number, a."aircraftMake", a."aircraftModel", c."highestInjuryLevel", c."totalFatal", c."completionStatus", c.report_url FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c."mKey" AND a.position = 1 WHERE c."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('0631410', '0630711')) ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

duckdb -csv -c "SELECT c.\"ntsbNumber\", c.\"eventDate\", c.\"eventType\", c.\"eventCity\", c.\"eventStateOrRegion\", a.n_number, a.\"aircraftMake\", a.\"aircraftModel\", c.\"highestInjuryLevel\", c.\"totalFatal\", c.\"completionStatus\", c.report_url
  FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c.\"mKey\" AND a.position = 1
  WHERE c.\"mKey\" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('0631410', '0630711'))
  ORDER BY c.\"eventDate\" DESC, c.\"ntsbNumber\" DESC"

Prints the whole list as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

SELECT c."ntsbNumber", c."eventDate", c."eventType", c."eventCity", c."eventStateOrRegion", a.n_number, a."aircraftMake", a."aircraftModel", c."highestInjuryLevel", c."totalFatal", c."completionStatus", c.report_url
FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c."mKey" AND a.position = 1
WHERE c."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('0631410', '0630711'))
ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

Run it at the DuckDB prompt, or in any tool that reads Parquet over HTTPS. More ways to query the release are on Use the data.

NTSB NYC74AN097Accident · 3 fatalClosedPiper PA-28R · Tewksbury, MAMay 20, 1974 · N4MB · 2 aircraft · Destroyed NTSB MIA74DKG78Accident · No injuriesClosedAmer Avco AA5 · Jonesboro, GAMay 19, 1974 · N6041L · Substantial damage NTSB LAX74AL070Accident · 2 fatalClosedAmer Avco AA5 · Rimrock, AZApr 12, 1974 · N5841L · Destroyed NTSB NYC74FGM19Accident · No injuriesClosedAmer Avco AA-5 · Cooperstown, PAMar 12, 1974 · N6019L · Substantial damage NTSB LAX74DUJ42Accident · Serious injuriesClosedAmer Avco AA5 · Williams, AZFeb 17, 1974 · N9504L · Destroyed NTSB IAD74FLQ26Accident · No injuriesClosedAmer Avco AA-5 · Fulton, KYFeb 11, 1974 · N71PP · Substantial damage NTSB LAX74FUJ31Accident · No injuriesClosedAmer Avco AA5 · Phoenix, AZNov 21, 1973 · N9434L · Substantial damage NTSB SEA74AS002Accident · 4 fatalClosedAmer Avco AA5 · Shelley, IDOct 27, 1973 · N9267L · Destroyed NTSB IAD74DKM20Accident · No injuriesClosedAmer Avco AA-5 · Taylorsville, NCSep 29, 1973 · N6023L · Destroyed NTSB CHI74DEM13Accident · No injuriesClosedAmer Avco AA5 · Gaylord, MIAug 3, 1973 · N5839L · Substantial damage NTSB CHI73FEG21Accident · No injuriesClosedAmer Avco AA-5 · Trotwood, OHMar 19, 1973 · N6030L · Substantial damage NTSB LAX73DUJ60Accident · Minor injuriesClosedAmer Avco AA-5 · Marana, AZMar 3, 1973 · N6062L · Substantial damage NTSB NYC73DFJ12Accident · Serious injuriesClosedAmer Avco AA-5 · Montour Falls, NYOct 8, 1972 · N5813L · Substantial damage NTSB CHI73DEA10Accident · No injuriesClosedAmer Avco AA-5 · Bedford, INAug 5, 1972 · N5839L · Substantial damage NTSB LAX73DUJ13Accident · No injuriesClosedAmerican AA-5 · Phoenix, AZJul 23, 1972 · N5865L · Substantial damage NTSB CHI72FEG39Accident · No injuriesClosedAmerican AA-5 · Harrison, OHJun 10, 1972 · N5802L · Substantial damage NTSB NYC72FNA24Accident · Serious injuriesClosedAmer Avco AA5 · Tewksbury, MAJan 2, 1972 · N5803L · Destroyed