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 MIA70DKJ45Accident · No injuriesClosedCessna 150 · Laceys Sprngs, ALJun 22, 1970 · N6619S · Substantial damageNTSB LAX70DUJ45Accident · Minor injuriesClosedCessna 182 · Kayenta, AZJun 21, 1970 · N2393X · DestroyedNTSB ANC70FAG35Accident · Serious injuriesClosedTaylorcraft BC12-D · Dillingham, AKJun 21, 1970 · N44025 · DestroyedNTSB FTW70FRG52Accident · Serious injuriesClosedCessna 150 · Eagle Pass, TXJun 20, 1970 · N6489F · Substantial damageNTSB CHI70FGD27Accident · Serious injuriesClosedCessna 172 · Middle Bass, OHJun 19, 1970 · N4074F · Substantial damageNTSB CHI70FBA25Accident · Serious injuriesClosedPiper PA-28 · Richmond, INJun 19, 1970 · N7798N · DestroyedNTSB ANC70AA089Accident · Serious injuriesClosedCessna 185 · Glenn Allen, AKJun 19, 1970 · N4521F · Substantial damageNTSB FTW70FPG37Accident · No injuriesClosedBoeing A75 · Brinkley, ARJun 18, 1970 · N50056 · Substantial damageNTSB FTW70FRA30Accident · No injuriesClosedCessna 150 · Pearland, TXJun 16, 1970 · N8609S · Substantial damageNTSB FTW70FRA37Accident · No injuriesClosedCessna 140 · East Bernard, TXJun 15, 1970 · N89750 · Substantial damageNTSB MKC70DCJ30Accident · No injuriesClosedCessna 170B · Wolbach, NEJun 14, 1970 · N2442D · Substantial damageNTSB LAX70AL073Accident · 1 fatalClosedCessna 150F · New River, AZJun 14, 1970 · N8823G · DestroyedNTSB CHI70DBJ37Accident · No injuriesClosedBeech A23 · Chesaning, MIJun 14, 1970 · N2321Q · Substantial damageNTSB ANC70FAG31Accident · No injuriesClosedPiper PA-12 · Wasilla, AKJun 14, 1970 · N2449M · Substantial damageNTSB IAD71FLJ07Accident · No injuriesClosedCessna 150G · White Lake, NCJun 13, 1970 · N4625X · DestroyedNTSB CHI70FDJ23Accident · Serious injuriesClosedChampion 7ECA · Appleton, WIJun 13, 1970 · N11054 · Substantial damageNTSB CHI70FBD48Accident · Minor injuriesClosedCessna 177 · W.Chicago, ILJun 13, 1970 · N3441T · Substantial damageNTSB IAD70FIG18Accident · Minor injuriesClosedTaylorcraft DCO-65 · Cumberland, MDJun 11, 1970 · N46113 · DestroyedNTSB CHI70FDW21Accident · No injuriesClosedPiper PA-25 · Linden, MIJun 11, 1970 · N7461Z · Substantial damageNTSB DEN70DXD45Accident · No injuriesClosedAlon A2-A · Boulder, COJun 10, 1970 · N5468F · Substantial damageNTSB CAN71AW902Accident · Minor injuriesClosedBellanca 17-30 · Livingston Ck, CanadaJun 10, 1970 · N4883V · Substantial damageNTSB ANC70DAA14Accident · Minor injuriesClosedChampion 7GCBC · Circle City, AKJun 9, 1970 · N5214X · Substantial damageNTSB SEA70FWD19Accident · No injuriesClosedCessna 172 · Puyallup, WAJun 8, 1970 · N46382 · Substantial damageNTSB NYC70FHM14Accident · Minor injuriesClosedPiper PA-28 · Spring Valley, NYJun 8, 1970 · N8042N · Substantial damageNTSB CHI70DBM20Accident · Minor injuriesClosedCessna 172 · Peru, INJun 8, 1970 · N3621L · Substantial damageNTSB NYC70FHG07Accident · Serious injuriesClosedCessna 172 · Forksville, PAJun 7, 1970 · N7862U · DestroyedNTSB NYC70FHD34Accident · No injuriesClosedBeech K35 · Salem, NJJun 7, 1970 · N826R · Substantial damageNTSB FTW70FRA25Accident · No injuriesClosedChampion 7FC · Dayton, TXJun 7, 1970 · N9873B · Substantial damageNTSB CHI70DBD43Accident · No injuriesClosedBeech A-23A · Durand, ILJun 7, 1970 · N1000J · Substantial damageNTSB ANC70DAG27Accident · Minor injuriesClosedPiper PA-18 · Willow, AKJun 7, 1970 · N3195Z · Substantial damageNTSB MKC70FDD20Accident · No injuriesClosedPiper PA-18 · Rothsay, MNJun 6, 1970 · N4878A · Substantial damageNTSB MIA70DLD29Accident · No injuriesClosedCessna T337D · St Petersburg, FLJun 6, 1970 · N5311Q · Substantial damageNTSB FTW70FRA27Accident · No injuriesClosedGrumman G-164 · Garwood, TXJun 5, 1970 · N6536 · DestroyedNTSB FTW71DRG26Accident · No injuriesClosedPiper PA-11 · Luling, TXJun 3, 1970 · N4524M · Substantial damageNTSB MKC70AK068Accident · No injuriesClosedBeech E18S · Detroit Lake, MNJun 1, 1970 · N221H · Substantial damageNTSB SEA70DWD17Accident · No injuriesClosedCessna 172 · Oakville, WAMay 31, 1970 · N9180B · Substantial damageNTSB MIA70FLA22Accident · Serious injuriesClosedPiper PA-28 · Mayo, FLMay 31, 1970 · N3630K · DestroyedNTSB MKC70FDQ08Accident · No injuriesClosedMooney M20C · Miller, SDMay 30, 1970 · N2937L · Substantial damageNTSB MKC70FCD33Accident · Serious injuriesClosedPiper PA-22 · Atlantic, IAMay 30, 1970 · N2549A · Substantial damageNTSB FTW70AF078Accident · 2 fatalClosedCessna 150 · Carrizo Sprgs, TXMay 29, 1970 · N6490F · DestroyedNTSB DEN70FQA29Accident · No injuriesClosedTibbetts M Mouz · Albuquerque, NMMay 29, 1970 · N44J · Substantial damageNTSB CAN71A0902Accident · 2 fatalClosedPiper PA-18 · Ontario, CanadaMay 29, 1970 · N2321A · Substantial damageNTSB FTW70DRA29Accident · No injuriesClosedBoeing E75N1 · Beaumont, TXMay 28, 1970 · N4577N · Substantial damageNTSB DEN70FXD28Accident · No injuriesClosedLuscombe T-8F · Broomfield, COMay 28, 1970 · N22088 · Substantial damageNTSB FTW70FPG36Accident · No injuriesClosedBeech T-34A · Clinton, ARMay 27, 1970 · N4937E · Substantial damageNTSB MKC70FCJ26Accident · No injuriesClosedPiper PA-25 · Gurley, NEMay 26, 1970 · N4445Y · Substantial damageNTSB CHI70FGG21Accident · Minor injuriesClosedTaylorcraft BL-65 · Columbus, OHMay 26, 1970 · N26663 · Substantial damageNTSB CHI70FBA20Accident · No injuriesClosedBeech 35 · Kokomo, INMay 25, 1970 · N2851V · Substantial damageNTSB MKC70DCD35Accident · No injuriesClosedBeech A35 · Newton, IAMay 24, 1970 · N473AB · Substantial damageNTSB FTW70FRG45Accident · Minor injuriesClosedBell 47D-1 · San Antonio, TXMay 24, 1970 · N730J · Destroyed