Skip to content

NTSB investigations: Piper PA-23-150

79 cases on aircraft the registry lists as Piper PA-23-150. 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 ('7102303')) 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 ('7102303'))
  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 ('7102303'))
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 DEN69D0234Accident · No injuriesClosedPiper PA-23 · Greeley, COJan 10, 1969 · N1034P · Substantial damage NTSB DEN68F0202Accident · No injuriesClosedPiper PA-23 · Hobbs, NMFeb 2, 1968 · N1111P · Substantial damage NTSB LAX68F0252Accident · No injuriesClosedPiper PA-23 · Van Nuys, CAJan 5, 1968 · N1276P · Substantial damage NTSB CHI68F0397Accident · No injuriesClosedPiper PA-23 · Aberdeen, OHNov 1, 1967 · N1258P · Substantial damage NTSB MIA68D0009Accident · No injuriesClosedPiper PA-23 · Jackson, TNJun 16, 1967 · N3237P · Substantial damage NTSB LAX68D0005Accident · No injuriesClosedPiper PA-23 · El Monte, CAMay 17, 1967 · N3116P · Substantial damage NTSB MIA67F0517Accident · No injuriesClosedPiper PA-23 · Birmingham, ALMay 16, 1967 · N1303P · Substantial damage NTSB MIA67F0428Accident · No injuriesClosedPiper PA-23 · Atlanta, GAFeb 3, 1967 · N3050P · Substantial damage NTSB CHI67D0429Accident · No injuriesClosedPiper PA-23 · Madison, WIDec 15, 1966 · N3139P · Substantial damage NTSB DEN67F0135Accident · No injuriesClosedPiper PA-23 · Sterling, COOct 24, 1966 · N1094P · Substantial damage NTSB MIA67F0275Accident · No injuriesClosedPiper PA-23 · Atlanta, GAOct 20, 1966 · N3050P · Substantial damage NTSB CHI66F0776Accident · No injuriesClosedPiper PA23 · Louisville, KYJun 13, 1966 · N1258P · Substantial damage NTSB CHI66F0726Accident · No injuriesClosedPiper PA-23 · West Chicago, ILMay 5, 1966 · N1247P · Substantial damage NTSB OAK66A0037Accident · 3 fatalClosedPiper PA-23 · El Granada, CAMar 19, 1966 · N2204P · Destroyed NTSB CHI66A0060Accident · 3 fatalClosedPiper PA-23 · Knox, INDec 19, 1965 · N1100P · 2 aircraft · Destroyed NTSB CHI66F0478Accident · No injuriesClosedPiper PA-23 · East St Louis, ILNov 22, 1965 · N3205P · Substantial damage NTSB CHI66F0143Accident · No injuriesClosedPiper PA-23 · South Bend, INAug 1, 1965 · N1211P · Substantial damage NTSB SEA66F0012Accident · No injuriesClosedPiper PA-23 · Bremerton, WAMay 29, 1965 · N1479P · Substantial damage NTSB UNK65X1329Accident · No injuriesClosedPiper PA23 · Geneva, NYJan 22, 1965 · N1313P · Substantial damage NTSB CHI65F0038Accident · No injuriesClosedPiper PA-23 · Des Plaines, ILJan 14, 1965 · N3135P · Substantial damage NTSB UNK65X0938Accident · 1 fatalClosedPiper PA-23 · Bimini Bwi, WNDec 6, 1964 · N1420P · Substantial damage NTSB UNK65X0511Accident · No injuriesClosedPiper PA23 · Cynthiana, KYNov 1, 1964 · N1143P · Substantial damage NTSB UNK64X2533Accident · No injuriesClosedPiper PA23 · Ft Worth, TXJun 28, 1964 · N2154P · Substantial damage NTSB UNK64X1583Accident · No injuriesClosedPiper PA23 · Denver, COMay 3, 1964 · N1239P · Substantial damage NTSB UNK64X1035Accident · No injuriesClosedPiper PA23 · Reno, NVMar 25, 1964 · N1211P · Destroyed NTSB UNK64X0977Accident · No injuriesClosedPiper PA-23 · Miramar, FLMar 19, 1964 · N31A · Substantial damage NTSB UNK64X0691Accident · 3 fatalClosedPiper PA-23 · Valparaiso, INFeb 25, 1964 · N8690E · Destroyed NTSB UNK64X0600Accident · No injuriesClosedPiper PA23 · Lockport, ILFeb 17, 1964 · N1329P · Substantial damage NTSB UNK64X0188Accident · No injuriesClosedPiper PA23 · Vandalia, OHJan 8, 1964 · N1450P · Substantial damage