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 UNK65X0507Accident · No injuriesClosedStinson 108-3 · Plympton, MANov 1, 1964 · N4145C · Substantial damageNTSB UNK65X0483Accident · No injuriesClosedBeechcraft 35 · Smith Center, KSOct 30, 1964 · N4540V · Substantial damageNTSB UNK65X0467Accident · 1 fatalClosedMaule M-4 · Townsend, MTOct 28, 1964 · N4725T · DestroyedNTSB UNK65X0448Accident · No injuriesClosedBeechcraft 35 · Sunland Par, NMOct 27, 1964 · N3051V · DestroyedNTSB UNK65X0428Accident · Minor injuriesClosedCessna 170A · Freemason Is, LAOct 25, 1964 · N5402C · DestroyedNTSB UNK65X0425Accident · Minor injuriesClosedNavion A · Bloomington, INOct 25, 1964 · N91650 · Substantial damageNTSB UNK65X0412Accident · 2 fatalClosedCessna 150 · Crestview, FLOct 24, 1964 · N6067T · DestroyedNTSB UNK65X0411Accident · 2 fatalClosedPiper PA-22 · Westplains, MOOct 24, 1964 · N7548D · DestroyedNTSB UNK65X0386Accident · No injuriesClosedErcoupe 415C · Santa Monic, CAOct 23, 1964 · N2317H · Substantial damageNTSB UNK65X0384Accident · No injuriesClosedPiper PA16 · Ft Bragg, NCOct 23, 1964 · N5342H · DestroyedNTSB UNK65X0367Accident · No injuriesClosedBeech F35 · Ocean Shores, WAOct 22, 1964 · N4218B · Substantial damageNTSB UNK65X0362Accident · No injuriesClosedMooney M20E · Idaho Falls, IDOct 21, 1964 · N1984Y · Substantial damageNTSB UNK65X0361Accident · Minor injuriesClosedCessna 172 · Covington, LAOct 21, 1964 · N3719S · Substantial damageNTSB UNK65X0338Accident · No injuriesClosedCallair A5 · Wellington, TXOct 19, 1964 · N9954C · DestroyedNTSB UNK65X0330Accident · Minor injuriesClosedBeechcraft J35 · Cape Girardeau, MOOct 18, 1964 · N3048C · DestroyedNTSB UNK65X0298Accident · 1 fatalClosedCessna 185A · Fish Lake, IDOct 16, 1964 · N1687Z · DestroyedNTSB UNK65X0283Accident · No injuriesClosedWaco CSO · Beaver Lake, NYOct 15, 1964 · N656N · Substantial damageNTSB UNK65X0278Accident · No injuriesClosedBeechcraft 23 · Cleveland, OHOct 14, 1964 · N2386J · Substantial damageNTSB UNK65X0269Accident · No injuriesClosedCesna 172 · Amarillo, TXOct 13, 1964 · N4114F · Substantial damageNTSB UNK65X0254Accident · No injuriesClosedPiper J3 · Livingston Mar, NYOct 12, 1964 · N33590 · Substantial damageNTSB UNK65X0251Accident · No injuriesClosedBoeing A75N1 · Holtville, CAOct 12, 1964 · N63409 · Substantial damageNTSB UNK65X0245Accident · No injuriesClosedMooney M21 · Mitchelville, MDOct 11, 1964 · N1233X · Substantial damageNTSB UNK65X0226Accident · No injuriesClosedCurtiss C-46 · Charleston, SCOct 10, 1964 · N7923C · Substantial damageNTSB UNK65X0200Accident · Serious injuriesClosedBell 47J2A · Bodega Bay, CAOct 8, 1964 · N1141W · Substantial damageNTSB UNK65X0188Accident · No injuriesClosedCessna 120 · Yomitan Okinawa, JAOct 8, 1964 · N76157 · DestroyedNTSB UNK65X0153Accident · No injuriesClosedAeronca 7AC · Strathroy, CanadaOct 5, 1964 · N3590E · Substantial damageNTSB UNK65X0140Accident · No injuriesClosedCessna 140 · Saugus, CAOct 4, 1964 · N72351 · DestroyedNTSB UNK65X0137Accident · No injuriesClosedPiper PA28 · Hobart, INOct 4, 1964 · N7731W · Substantial damageNTSB UNK65X0116Accident · No injuriesClosedCessna 172 · Alder Point, CAOct 2, 1964 · N6184E · Substantial damageNTSB UNK64X4218Accident · No injuriesClosedBoeing B75N1 · Pocahontas, ARSep 30, 1964 · N9751H · DestroyedNTSB UNK64X4213Accident · Minor injuriesClosedPiper PA25 · Aspermont, TXSep 30, 1964 · N6134Z · DestroyedNTSB UNK64X4210Accident · No injuriesClosedChampion 7GCB · Dillingham, AKSep 29, 1964 · N98754 · DestroyedNTSB UNK64X4197Accident · Minor injuriesClosedPiper PA22 · Lynden, WASep 28, 1964 · N2334A · Substantial damageNTSB UNK64X4187Accident · No injuriesClosedStinson 108 · Grand Portage, MNSep 27, 1964 · N6630M · Substantial damageNTSB UNK64X4183Accident · No injuriesClosedCessna 140 · Ames, IASep 27, 1964 · N77075 · DestroyedNTSB UNK64X4154Accident · Minor injuriesClosedAeronca 7AC · Newberry, SCSep 26, 1964 · N81939 · DestroyedNTSB UNK64X4128Accident · No injuriesClosedErcoupe E · Vieques, PRSep 24, 1964 · N94822 · Substantial damageNTSB UNK64X4126Accident · Minor injuriesClosedBeechcraft 95B55 · Evanston, WYSep 24, 1964 · N258EA · DestroyedNTSB UNK64X4122Accident · No injuriesClosedBoeing A75N1 · Kingsburg, CASep 24, 1964 · N49957 · Substantial damageNTSB UNK64X4107Accident · No injuriesClosedBell 47G2 · Tyone, AKSep 22, 1964 · N7888B · Substantial damageNTSB UNK64X4104Accident · No injuriesClosedDouglas DC-3 · San Juan, PRSep 22, 1964 · N1549V · Substantial damageNTSB UNK64X4097Accident · No injuriesClosedCessna 172E · Festus, MOSep 22, 1964 · N5347T · Substantial damageNTSB UNK64X4069Accident · No injuriesClosedPiper PA18 · Ltl Delta Rv, AKSep 20, 1964 · N7889D · Substantial damageNTSB UNK64X4058Accident · Minor injuriesClosedChampion 7FC · Humacao, PRSep 20, 1964 · N8915R · Substantial damageNTSB UNK64X4056Accident · 3 fatalClosedCulver LCA · Northfield, VASep 19, 1964 · N32487 · DestroyedNTSB UNK64X4044Accident · Minor injuriesClosedPiper PA-22 · Zaragoza, MexicoSep 19, 1964 · N1646A · DestroyedNTSB UNK64X4043Incident · No injuriesClosedConvair CV-340 · San Juan, PRSep 19, 1964 · N3410 · No damageNTSB UNK64X4033Accident · No injuriesClosedCallair A9 · Mill Haven, GASep 18, 1964 · N3579G · Substantial damageNTSB UNK64X4028Accident · 2 fatalClosedBeechcraft K35 · Cibola, AZSep 17, 1964 · N348B · Substantial damageNTSB UNK64X4027Accident · Minor injuriesClosedPiper PA28 · Parguera, PRSep 17, 1964 · N7038W · Destroyed