Skip to content

NTSB investigations: Amateur-built UNKNOWN ENG

2,048 cases on aircraft whose registry engine model is Amateur-built UNKNOWN ENG. 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.engine_model_code IN ('99999')) 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.engine_model_code IN ('99999'))
  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.engine_model_code IN ('99999'))
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 SEA67D0352Accident · No injuriesClosedCessna 140 · Blackfoot, IDMay 21, 1967 · N76775 · Substantial damage NTSB OAK67D0290Accident · No injuriesClosedCessna 172 · San Carlos, CAMar 19, 1967 · N3926L · Substantial damage NTSB LAX67D0378Accident · Serious injuriesClosedSchweitzer 2-22 · Flagstaff, AZMar 11, 1967 · N10367 · Minor damage NTSB NYC67F0528Accident · No injuriesClosedNavion A · Schenectady, NYFeb 3, 1967 · N91140 · Substantial damage NTSB NYC67D0451Accident · No injuriesClosedCessna 310H · Pluckemin, NJJan 16, 1967 · N1037Q · Substantial damage NTSB FTW67A0067Accident · No injuriesClosedCessna P206 · Chickasha, OKDec 29, 1966 · N2588X · Substantial damage NTSB LAX67D0237Accident · No injuriesClosedSchweizer I-26 · Torrey Pine, CADec 26, 1966 · N8608R · Destroyed NTSB FTW67A0046Accident · Minor injuriesClosedDouglas DC-8 · New Orleans, LAOct 22, 1966 · N808E · Substantial damage NTSB SEA67A0027Accident · 2 fatalClosedBell 47G3B1 · W.Yellowston, MTSep 21, 1966 · N1169W · Destroyed NTSB LAX67A0031Accident · No injuriesClosedBell 47D1 · Long Beach, CASep 11, 1966 · N74119 · Substantial damage NTSB MKC67A0020Accident · 1 fatalClosedBoeing A75 · Hatton, NDAug 31, 1966 · N75664 · Destroyed NTSB FTW67F0152Accident · No injuriesClosedPiper PA-25 · Success, ARAug 4, 1966 · N7358Z · Substantial damage NTSB FTW67F0075Accident · No injuriesClosedGlobe GC-1B · Addison, TXJul 22, 1966 · N3301K · 2 aircraft · Substantial damage NTSB UNK66X0156Accident · No injuriesClosedBell 47G-5 · Episkopi, GreeceJul 12, 1966 · N8574F · Destroyed NTSB SEA67D0031Accident · No injuriesClosedLuscombe 8A · Montpelier, IDJul 10, 1966 · N20687 · Substantial damage NTSB MKC67F0190Accident · No injuriesClosedVollmer VJ-22 · Waubun, MNJul 10, 1966 · N4634T · Substantial damage NTSB NYC67D0179Accident · No injuriesClosedNavion A · Blairsville, PAJun 30, 1966 · N91140 · Substantial damage NTSB MKC67D0088Accident · No injuriesClosedPiper PA-18 · Marion, NDJun 24, 1966 · N3281Z · Substantial damage NTSB OAK66A0055Accident · Minor injuriesClosedGrumman F7F · Grass Valley, CAJun 7, 1966 · N7628C · Substantial damage NTSB MKC66I0058Incident · No injuriesClosedDouglas DC-3 · Norfolk, NEMay 11, 1966 · N15748 · Minor damage NTSB LAX66F0372Accident · Serious injuriesClosedGR Lakes 2T-1 · Baker, CAApr 9, 1966 · N872K · Destroyed NTSB OAK66D0242Accident · No injuriesClosedCessna 180 · Napa, CAApr 1, 1966 · N9232C · Substantial damage NTSB MKC66F0499Accident · No injuriesClosedPiper PA-22 · Columbia, MOFeb 26, 1966 · N7804D · Substantial damage NTSB SEA66F0230Accident · No injuriesClosedCessna 172 · Junction City, ORFeb 13, 1966 · N8454X · Substantial damage NTSB FTW66A0070Accident · Minor injuriesClosedBell 47G · Wetmore, TXFeb 10, 1966 · N586T · Substantial damage NTSB MKC66D0281Accident · Minor injuriesClosedCessna 140 · Holton, KSOct 9, 1965 · N76939 · Substantial damage NTSB FTW66F0351Accident · No injuriesClosedBeechcraft 95-A55 · Alexandria, LAOct 8, 1965 · N1532Z · Substantial damage NTSB FTW66I0019Incident · No injuriesClosedBoeing B-727 · Little Rock, ARAug 18, 1965 · N1995 · 2 aircraft · Minor damage NTSB MIA66F0052Accident · No injuriesClosedPiper PA-28 · Winston Salem, NCJul 17, 1965 · N8618W · Substantial damage NTSB MKC66F0044Accident · Minor injuriesClosedLuscombe 8E · Colwich, KSJun 23, 1965 · N1851K · Destroyed NTSB OAK65A0073Accident · 2 fatalClosedAeronca 7AC · Sacramento, CAJun 20, 1965 · N82364 · Destroyed NTSB SEA66F0045Accident · Minor injuriesClosedPiper PA-18A · Culver, ORMay 19, 1965 · N8568D · Destroyed NTSB FTW65F0054Accident · No injuriesClosedGrumman G-164 · Welsh, LAApr 21, 1965 · N10244 · Substantial damage NTSB OAK65F0010Accident · No injuriesClosedStinson 108-3 · Fresno, CAMar 27, 1965 · N6679M · Destroyed NTSB FTW65F0004Accident · No injuriesClosedCessna 182 · Addison, TXMar 21, 1965 · N3953D · Substantial damage NTSB MKC65F0056Accident · No injuriesClosedMooney 20C · Beloit, KSMar 17, 1965 · N78874 · Substantial damage NTSB DEN65F0016Accident · No injuriesClosedLuscombe 8A · Bryce Canyon, UTFeb 22, 1965 · N20687 · Substantial damage NTSB UNK65X1089Accident · No injuriesClosedBeechraft D95 · Groveland, ILDec 22, 1964 · N8613M · Substantial damage NTSB UNK65X0992Accident · 2 fatalClosedRyan ST3KR · Altus, OKDec 12, 1964 · N88717 · Destroyed NTSB UNK65X0690Accident · No injuriesClosedGlobe GC1B · Chino, CANov 13, 1964 · N78131 · Substantial damage NTSB UNK65X0397Accident · No injuriesClosedCessna 182B · Phoenix, AZOct 24, 1964 · N2701G · Substantial damage NTSB UNK65X0341Accident · No injuriesClosedNo American F51D · Corinto, NiueOct 20, 1964 · N6162U · Destroyed NTSB UNK64X2818Accident · No injuriesClosedBeechcraft 23 · Columbus, GAJul 14, 1964 · N2325J · Substantial damage NTSB UNK64X2725Accident · Minor injuriesClosedCessna 120 · Laytonsville, MDJul 9, 1964 · N2714N · Destroyed NTSB UNK64X2581Accident · No injuriesClosedPiper PA18A · Marion, NYJun 30, 1964 · N1982P · Substantial damage NTSB UNK64X2482Accident · No injuriesClosedPiper PA22 · Olpe, KSJun 25, 1964 · N7804D · Substantial damage NTSB UNK64X1572Accident · No injuriesClosedChampion 7AC · Clarksburg, CAMay 2, 1964 · N82364 · Substantial damage NTSB UNK64X0701Accident · No injuriesClosedBeechcraft 23 · Detroit, MIFeb 26, 1964 · N2354Q · Substantial damage