NTSB investigations: Beech A45

61 cases on aircraft the registry lists as Beech A45. 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 ('1152006')) 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 ('1152006'))
  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 ('1152006'))
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 DEN66D0145Accident · Serious injuriesClosedBeechcraft A45 · Pinedale, WYOct 8, 1965 · N10140 · Substantial damage NTSB OAK66F0096Accident · No injuriesClosedBeech C-45H · Honolulu, HISep 22, 1965 · N134 · Substantial damage NTSB NYC66F0175Accident · No injuriesClosedBeechcraft T-34A · Turner Falls, MAAug 11, 1965 · N8701R · Substantial damage NTSB DEN65A0070Accident · 2 fatalClosedBeechcraft T-34A · Glendale, AZMay 8, 1965 · N12254 · Destroyed NTSB LAX65A0058Accident · 2 fatalClosedBeechcraft T34A · Wrightwood, CADec 31, 1964 · N5506V · Destroyed NTSB UNK65X1127Accident · No injuriesClosedBeechraft T34A · Jonesboro, LADec 25, 1964 · N1007B · Substantial damage NTSB UNK65X0647Accident · No injuriesClosedBeechcraft T34 · Westhampton, NYNov 10, 1964 · N4189A · Substantial damage NTSB UNK65X0240Accident · No injuriesClosedBeechcraft T34 · Stafford, CTOct 11, 1964 · N4113A · Substantial damage NTSB UNK64X2756Accident · 1 fatalClosedBeechcraft A45 · Anadarko, OKJul 11, 1964 · N9191R · Destroyed NTSB UNK64X1736Accident · No injuriesClosedBeechcraft T34 · Springfield, OHMay 12, 1964 · N9664H · Substantial damage NTSB UNK64X1475Accident · Minor injuriesClosedBeechcraft T34 · Midland, VAApr 26, 1964 · N7973A · Destroyed