NTSB investigations: Boeing E75N1

60 cases on aircraft the registry lists as Boeing E75N1. 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 ('1380154')) 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 ('1380154'))
  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 ('1380154'))
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 MIA65A0126Accident · 1 fatalClosedBoeing 75N1 · Sunflower, MSJun 26, 1965 · N4936C · Substantial damage NTSB FTW66F0069Accident · Minor injuriesClosedBoeing D-75N1 · Snow Lake, ARApr 2, 1965 · N53544 · Substantial damage NTSB UNK65X0582Accident · Minor injuriesClosedBoeing PT17 · Hakalau, HINov 6, 1964 · N67999 · Destroyed NTSB UNK65X0360Accident · No injuriesClosedBoeing B75N1 · Homestead, FLOct 21, 1964 · N65900 · Substantial damage NTSB UNK64X3882Accident · No injuriesClosedBoeing A75 · Hickory Ridge, ARSep 9, 1964 · N65877 · Substantial damage NTSB UNK64X3733Accident · No injuriesClosedBoeing E75N1 · Hazen, ARSep 2, 1964 · N69858 · Substantial damage NTSB UNK64X3042Accident · No injuriesClosedBoeing E75 · Pecos, TXJul 25, 1964 · N5227N · Substantial damage NTSB UNK64X2854Accident · Minor injuriesClosedBoeing E75N1 · Hosston, LAJul 16, 1964 · N5945V · Substantial damage NTSB UNK64X1658Accident · Minor injuriesClosedStearman E75N1 · Henry County, ALMay 7, 1964 · N3724C · Destroyed NTSB UNK64X1162Accident · Minor injuriesClosedBoeing A75 · Maxwell, CAApr 4, 1964 · N1730B · Destroyed