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 SEA71FWD22Accident · No injuriesClosedPiper PA-18 · Sekiu, WANov 21, 1970 · N3891Z · Substantial damageNTSB NYC71FHA22Accident · No injuriesClosedBeech A23 · Basking Ridge, NJNov 21, 1970 · N2360Q · Substantial damageNTSB FTW71DRA30Accident · Serious injuriesClosedAeronca 15AC · Clute, TXNov 21, 1970 · N1286H · DestroyedNTSB DEN71AD024Accident · 2 fatalClosedPiper PA-18 · Grand Junct, CONov 21, 1970 · N1094A · DestroyedNTSB OAK71DVG39Accident · No injuriesClosedBoeing A75 · Woodland, CANov 19, 1970 · N57001 · Substantial damageNTSB NYC71FHJ16Accident · Minor injuriesClosedAmer Avco AA-1 · Bohemia, NYNov 18, 1970 · N5749L · Substantial damageNTSB FTW71AF023Accident · 5 fatalClosedCessna 182M · Vidalia, LANov 18, 1970 · N71605 · 2 aircraft · Substantial damageNTSB ANC71DAG48Accident · No injuriesClosedCessna 180 · Eureka, AKNov 18, 1970 · N2968A · Substantial damageNTSB NYC71AN061Accident · Serious injuriesClosedCessna 310D · Warwick, RINov 17, 1970 · N6769T · DestroyedNTSB MKC71FCG35Accident · No injuriesClosedCessna 182G · Eldon, MONov 16, 1970 · N3143S · Substantial damageNTSB MKC71FCG33Accident · No injuriesClosedErco 415-E · Gardner, KSNov 15, 1970 · N94824 · Substantial damageNTSB LAX71FUQ36Accident · Serious injuriesClosedPiper PA-28R · Mojave, CANov 15, 1970 · N7519J · Substantial damageNTSB NYC71FHM10Accident · No injuriesClosedCessna 172 · Spring Valley, NYNov 14, 1970 · N7486X · Substantial damageNTSB LAX71AL028Accident · 2 fatalClosedNavion H · El Monte, CANov 13, 1970 · N2516T · DestroyedNTSB CHI71FBJ39Accident · No injuriesClosedMaule M-4 · Ludington, MINov 13, 1970 · N4140C · Substantial damageNTSB DEN71FXD34Accident · Minor injuriesClosedAmer Avco AA-1 · Aurora, CONov 11, 1970 · N5610L · Substantial damageNTSB MIA71FKA08Accident · No injuriesClosedCessna 150K · St.Croix, VINov 9, 1970 · N5917J · Substantial damageNTSB CHI71DDJ27Accident · No injuriesClosedBensen B8M · Waterford, WINov 7, 1970 · N2434 · Substantial damageNTSB CHI71DDJ21Accident · Minor injuriesClosedCessna 180A · Tony, WINov 6, 1970 · N5395D · Substantial damageNTSB MKC71FDM36Accident · No injuriesClosedBensen B-8M · Lake Elmo, MNNov 5, 1970 · N2436 · Substantial damageNTSB CAN71AP909Accident · No injuriesClosedBeech B35 · Tsoniah Lake, CanadaNov 3, 1970 · N8604M · Substantial damageNTSB MKC71DCG29Accident · No injuriesClosedCessna 140 · Moberly, MONov 1, 1970 · N4052N · Substantial damageNTSB CHI71FDM04Accident · No injuriesClosedPiper PA-28 · Chetek, WINov 1, 1970 · N4363J · Substantial damageNTSB IAD71DIG20Accident · No injuriesClosedBeech 58 · Capon Sprngs, WVOct 31, 1970 · N633D · Substantial damageNTSB FTW71DRG47Accident · No injuriesClosedPiper PA-28 · Rancho S.J., MexicoOct 31, 1970 · N9395W · Substantial damageNTSB FTW71AF020Accident · 1 fatalClosedPiper PA-18 · Breaux Bridge, LAOct 31, 1970 · N3799A · DestroyedNTSB SEA71DWG30Accident · No injuriesClosedPiper PA-28 · Royal City, WAOct 30, 1970 · N7178R · Substantial damageNTSB MIA71DKT14Accident · No injuriesClosedBeech 95-B55 · Opa Locka, FLOct 30, 1970 · N8624Q · Substantial damageNTSB FTW71FPD24Accident · No injuriesClosedCessna 177 · Abilene, TXOct 30, 1970 · N2985X · Substantial damageNTSB FTW71DRD24Accident · No injuriesClosedGrumman G-164 · Welsh, LAOct 30, 1970 · N10291 · DestroyedNTSB LAX71FUM18Accident · Serious injuriesClosedBeech C35 · San Diego, CAOct 28, 1970 · N1854D · Substantial damageNTSB CHI71AC029Accident · Serious injuriesClosedBeech D18S · Plymouth, MIOct 28, 1970 · N8999E · DestroyedNTSB NYC71DFD19Accident · No injuriesClosedAeronca 7AC · S.Middleboro, MAOct 25, 1970 · N83457 · Substantial damageNTSB FTW71FRA24Accident · No injuriesClosedPiper PA-18 · Conroe, TXOct 25, 1970 · N3996Z · Substantial damageNTSB LAX71FUQ28Accident · Serious injuriesClosedPiper PA-28 · Van Nuys, CAOct 24, 1970 · N95054 · DestroyedNTSB FTW71FRG40Accident · No injuriesClosedAero Comdr A-9B · Tivoli, TXOct 24, 1970 · N7718V · DestroyedNTSB MIA71AM035Accident · 2 fatalClosedCessna 120 · Marietta, GAOct 23, 1970 · N89865 · DestroyedNTSB LAX71DUJ38Accident · No injuriesClosedCessna 150H · Chandler, AZOct 20, 1970 · N23106 · Substantial damageNTSB CHI71FGG17Accident · No injuriesClosedNavion NAV4 · Findlay, OHOct 18, 1970 · N8569H · Substantial damageNTSB CHI71FGG16Accident · Minor injuriesClosedStinson 108 · Nelsonville, OHOct 18, 1970 · N97212 · Substantial damageNTSB CHI71FBQ21Accident · No injuriesClosedCessna 172 · Manito, ILOct 18, 1970 · N3981L · Substantial damageNTSB CHI71FDJ23Accident · 2 fatalClosedConvair PBY-5A · Rhinelander, WIOct 15, 1970 · N610FF · DestroyedNTSB NYC71DFA18Accident · No injuriesClosedStinson 108-2 · Heuvelton, NYOct 13, 1970 · N97230 · Substantial damageNTSB CHI71FGD22Accident · Serious injuriesClosedMitsubishi MU-2B · Northfield, OHOct 13, 1970 · N549LK · DestroyedNTSB NYC71AN048Accident · No injuriesClosedPiper PA-23 · Erie, PAOct 12, 1970 · N4377P · Substantial damageNTSB CHI71FBD30Accident · Minor injuriesClosedCessna 150H · Sandwich, ILOct 11, 1970 · N50240 · Substantial damageNTSB NYC71AN046Accident · 1 fatalClosedPiper PA-28 · Hammondsport, NYOct 9, 1970 · N5852U · DestroyedNTSB NYC71DFA23Accident · No injuriesClosedPiper PA-18A · Newcomb, NYOct 8, 1970 · N1551P · Substantial damageNTSB OAK71FVM19Accident · 1 fatalClosedPiper PA-28 · Calif.Vally, CAOct 4, 1970 · N9008W · DestroyedNTSB NYC71FFM19Accident · Minor injuriesClosedAeronca 7AC · Lanesboro, MAOct 4, 1970 · N3854E · Substantial damage