NTSB investigations

20,559 cases where the phase of flight was Initial climb.

Cases, newest first

Query with SQL
duckdb -csv -c "WITH phase_names(main, name) AS (VALUES (100, 'Prior to flight'), (150, 'Standing'), (200, 'Pushback/towing'), (250, 'Taxi'), (300, 'Takeoff'), (350, 'Initial climb'), (400, 'Enroute'), (450, 'Maneuvering'), (500, 'Approach'), (550, 'Landing'), (600, 'Emergency descent'), (650, 'Uncontrolled descent'), (700, 'Post-impact'), (750, 'After landing'), (800, 'Other'), (990, 'Unknown')),
    crosswalk(code, main, name) AS (VALUES (500, 150, 'Standing'), (501, 150, 'Standing - pre-flight'), (502, 150, 'Standing - starting engine(s)'), (503, 150, 'Standing - engine(s) operating'), (504, 150, 'Standing - engine(s) not operating'), (505, 150, 'Standing - idling rotors'), (510, 250, 'Taxi'), (511, 200, 'Taxi - pushback/tow'), (512, 250, 'Taxi - to takeoff'), (513, 250, 'Taxi - from landing'), (514, 250, 'Taxi - aerial'), (520, 300, 'Takeoff'), (521, 300, 'Takeoff - roll/run'), (522, 350, 'Takeoff - initial climb'), (523, 300, 'Takeoff - aborted'), (530, 400, 'Climb'), (531, 400, 'Climb - to cruise'), (540, 400, 'Cruise'), (541, 400, 'Cruise - normal'), (542, 400, 'Maneuvering - holding (IFR)'), (550, 400, 'Descent'), (551, 400, 'Descent - normal'), (552, 600, 'Descent - emergency'), (553, 650, 'Descent - uncontrolled'), (560, 500, 'Approach'), (561, 500, 'Approach - VFR pattern - downwind'), (562, 500, 'Approach - VFR pattern - base turn'), (563, 500, 'Approach - VFR pattern - base leg/base to final'), (564, 500, 'Approach - VFR pattern - final approach'), (565, 500, 'Go-around (VFR)'), (566, 500, 'Approach - IAF to FAF/outer marker (IFR)'), (567, 500, 'Approach - FAF/outer marker to threshold (IFR)'), (568, 500, 'Circling (IFR)'), (569, 500, 'Missed approach (IFR)'), (570, 550, 'Landing'), (571, 550, 'Landing - flare/touchdown'), (572, 550, 'Landing - roll'), (573, 550, 'Landing - aborted'), (574, 550, 'Emergency landing'), (575, 550, 'Emergency landing after takeoff'), (576, 600, 'Emergency descent/landing'), (580, 450, 'Maneuvering'), (581, 450, 'Maneuvering - aerial application'), (582, 450, 'Maneuvering - turn to reverse direction'), (583, 450, 'Maneuvering - turn to landing area (emergency)'), (590, 450, 'Hover'), (591, 450, 'Hover - in ground effect'), (592, 450, 'Hover - out of ground effect'), (600, 800, 'Other'), (610, 990, 'Unknown')),
    current_codes AS (
      SELECT e.case_mkey, TRY_CAST(e.\"tier1Number\" AS INTEGER) AS code,
        row_number() OVER (PARTITION BY e.case_mkey ORDER BY coalesce(e.\"isDefiningEvent\", false) DESC, e.position) AS n
      FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e
      WHERE e.aircrafts_position = 1 AND e.\"tier1Number\" GLOB '[1-9][0-9]*'
        AND (e.\"isDefiningEvent\" OR e.case_mkey IN (SELECT \"mKey\" FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' WHERE \"isPre1982\"))
    ),
    phases AS (
      SELECT case_mkey, 'current' AS scheme, code, CASE WHEN code >= 990 THEN 990 ELSE code // 50 * 50 END AS main
      FROM current_codes WHERE n = 1
      UNION ALL
      SELECT o.case_mkey, 'older', o.\"phaseOfFlightCode\", w.main
      FROM 'https://openairdocs.org/data/latest/ntsb_occurrences.parquet' o JOIN crosswalk w ON w.code = o.\"phaseOfFlightCode\"
      WHERE o.aircrafts_position = 1 AND o.\"occurrenceNumber\" = 1 AND o.case_mkey NOT IN (SELECT case_mkey FROM current_codes)
    ),
    case_phases AS (
      SELECT p.*, c.\"eventDate\", c.\"highestInjuryLevel\" = 'Fatal' AS fatal, a.aircraft_model_code
      FROM phases p JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c.\"mKey\" = p.case_mkey
      LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = p.case_mkey AND a.position = 1
      WHERE c.\"eventDate\" >= DATE '1962-01-01' AND p.main IN (SELECT main FROM phase_names)
    )
  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 case_mkey FROM case_phases WHERE main = 350)
  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.

NTSB SEA67A0014Accident · 3 fatalClosedCessna 320-1 · Fairview, ORAug 15, 1966 · N5760X · DestroyedNTSB NYC67F0188Accident · Minor injuriesClosedCessna 140 · Princess Anne, MDAug 15, 1966 · N76942 · Substantial damageNTSB NYC67D0159Accident · No injuriesClosedCessna 120 · Mineral Point, PAAug 15, 1966 · N1767N · Substantial damageNTSB NYC67F0304Accident · Minor injuriesClosedPiper PA-30 · Chesapeake, VAAug 14, 1966 · N7456Y · DestroyedNTSB NYC67F0178Accident · Minor injuriesClosedCessna 150F · Dillwyn, VAAug 14, 1966 · N8147S · Substantial damageNTSB FTW67D0177Accident · Serious injuriesClosedPiper J-3 · Matagorda, TXAug 13, 1966 · N70337 · Substantial damageNTSB DEN67F0074Accident · No injuriesClosedRyan ST-3KR · Fort Collins, COAug 13, 1966 · N48606 · Substantial damageNTSB NYC67D0232Accident · No injuriesClosedPiper J3C-65 · Nahmakanta Lak, MEAug 12, 1966 · N20246 · Substantial damageNTSB OAK67A0023Accident · No injuriesClosedBell 47G3B1 · Bridgeport, CAAug 11, 1966 · N337RH · Substantial damageNTSB MKC67D0159Accident · No injuriesClosedPiper PA-28 · Fairmont, MNAug 11, 1966 · N6675W · Substantial damageNTSB FTW67D0290Accident · No injuriesClosedBoeing E75 · Ganado, TXAug 11, 1966 · N97987 · Substantial damageNTSB DEN67F0076Accident · No injuriesClosedBeech A35 · Denver, COAug 11, 1966 · N587B · Substantial damageNTSB ANC67A0017Accident · No injuriesClosedPiper PA-18 · Port Heiden, AKAug 11, 1966 · N2580P · Substantial damageNTSB MIA67F0169Accident · Serious injuriesClosedBeech 35 · Plant City, FLAug 10, 1966 · N2703V · DestroyedNTSB CHI67F0280Accident · No injuriesClosedBeech A23 · Canton, OHAug 10, 1966 · N5989S · Substantial damageNTSB OAK67A0022Accident · Serious injuriesClosedBell 47G3B1 · Bishop, CAAug 9, 1966 · N8528F · DestroyedNTSB SEA67F0077Accident · No injuriesClosedPiper PA-24 · Laurel, MTAug 7, 1966 · N6593P · Substantial damageNTSB CHI67F0119Accident · Minor injuriesClosedCessna 172 · Shelbyville, KYAug 7, 1966 · N8590B · Substantial damageNTSB CHI67A0020Accident · Minor injuriesClosedBrantly B-2 · Jacob, ILAug 7, 1966 · N2121U · DestroyedNTSB NYC67F0220Accident · No injuriesClosedNavion A · Carlstadt, NJAug 6, 1966 · N5205K · Substantial damageNTSB NYC67D0173Accident · Minor injuriesClosedPiper PA-22 · Mayfield, NYAug 6, 1966 · N8818D · DestroyedNTSB LAX67A0017Accident · 2 fatalClosedPiper PA-28 · Santa Monica, CAAug 6, 1966 · N133SF · DestroyedNTSB FTW67F0170Accident · Serious injuriesClosedCessna T50 · Pocahontas, ARAug 6, 1966 · N5584N · DestroyedNTSB DEN67A0014Accident · 5 fatalClosedPiper PA-28 · Vernal, UTAug 6, 1966 · N8300W · DestroyedNTSB CHI67D0192Accident · No injuriesClosedPiper PA-24 · Elgin, ILAug 5, 1966 · N8217P · Substantial damageNTSB MIA67A0019Accident · Minor injuriesClosedDehavilland 114 · San Juan, PRAug 3, 1966 · N4789C · Substantial damageNTSB LAX67A0015Accident · No injuriesClosedHiller UH-12E · Kernville, CAAug 3, 1966 · N5336V · Substantial damageNTSB FTW67D0130Accident · No injuriesClosedCessna 172 · Bull Shoals, ARAug 3, 1966 · N8340U · Substantial damageNTSB MKC67D0163Accident · No injuriesClosedCessna 150 · El Dorado, KSAug 2, 1966 · N8101F · Substantial damageNTSB FTW67A0021Accident · No injuriesClosedHughes 269A · Wylie, TXAug 2, 1966 · N8933F · Substantial damageNTSB MKC67A0007Accident · 4 fatalClosedPiper PA-32 · Chillicothe, MOAug 1, 1966 · N3413W · DestroyedNTSB ANC67F0053Accident · Minor injuriesClosedGrumman G44 · Anchorage, AKAug 1, 1966 · N133H · Substantial damageNTSB NYC67D0164Accident · No injuriesClosedLuscombe 8A · Franklinville, NYJul 31, 1966 · N1936B · Substantial damageNTSB NYC67D0128Accident · Minor injuriesClosedPiper J3C-65 · Cheshire, MAJul 31, 1966 · N88179 · Substantial damageNTSB MKC67F0128Accident · No injuriesClosedCessna 172 · Gilby, NDJul 31, 1966 · N4124F · Substantial damageNTSB NYC67D0127Accident · No injuriesClosedFryklund Aero S · Broadbrook, CTJul 30, 1966 · N10132 · DestroyedNTSB MKC67F0153Accident · No injuriesClosedBeech A23 · Wichita, KSJul 30, 1966 · N2319W · Substantial damageNTSB MKC67D0119Accident · No injuriesClosedPiper PA-22 · Blomkest, MNJul 30, 1966 · N8102C · Substantial damageNTSB FTW67F0118Accident · No injuriesClosedBeech F35 · Denver City, TXJul 30, 1966 · N5067B · Substantial damageNTSB FTW67F0121Accident · No injuriesClosedBoeing A75 · Brinkley, ARJul 29, 1966 · N75641 · DestroyedNTSB CHI67D0252Accident · No injuriesClosedPiper PA-28 · Warren, OHJul 29, 1966 · N5107W · Substantial damageNTSB CHI67D0107Accident · Minor injuriesClosedPiper PA-22 · Osseo, WIJul 29, 1966 · N8803C · Substantial damageNTSB NYC67A0019Accident · Serious injuriesClosedCurtiss-Wrt C-46F · Port Elizabeth, NJJul 28, 1966 · N9905F · DestroyedNTSB MKC67F0174Accident · Minor injuriesClosedPiper PA-22 · Jefferson City, MOJul 28, 1966 · N1248C · Substantial damageNTSB NYC67D0135Accident · No injuriesClosedPiper PA-23 · Hyannis, MAJul 26, 1966 · N4330Y · Substantial damageNTSB OAK67D0050Accident · No injuriesClosedCurtiss-Wrt Robin · Strathmore, CAJul 25, 1966 · N780M · Substantial damageNTSB MIA67F0146Accident · No injuriesClosedCallair A-5T · Oliver, GAJul 25, 1966 · N2965G · Substantial damageNTSB FTW67F0128Accident · Minor injuriesClosedPiper PA-28 · Berryville, ARJul 24, 1966 · N8038W · Substantial damageNTSB CHI67F0322Accident · No injuriesClosedCessna 182C · Ottawa, ILJul 24, 1966 · N8573T · DestroyedNTSB CHI67F0200Accident · No injuriesClosedPiper PA-16 · South Bend, INJul 24, 1966 · N5677H · Substantial damage