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 CHI68F0392Accident · No injuriesClosedPiper PA-28 · Edison, OHOct 22, 1967 · N4202J · Substantial damageNTSB MKC68D0285Accident · No injuriesClosedPiper PA-28 · Marshalltown, IAOct 21, 1967 · N3422K · Substantial damageNTSB LAX68F0195Accident · Minor injuriesClosedPiper PA-15 · Tucson, AZOct 21, 1967 · N4466H · Substantial damageNTSB NYC68I0065Incident · No injuriesClosedBoeing 707 · Geneva, EswatiniOct 17, 1967 · N5771 · Minor damageNTSB DEN68D0140Accident · Minor injuriesClosedBeech B95 · Longmont, COOct 17, 1967 · N3051 · Substantial damageNTSB OAK68A0036Accident · No injuriesClosedHiller UH-12C · Riverdale, CAOct 15, 1967 · N5321V · Substantial damageNTSB LAX68D0176Accident · No injuriesClosedMooney M21 · Grand Canyon, AZOct 15, 1967 · N6691U · Substantial damageNTSB DEN68D0124Accident · Minor injuriesClosedCessna 150 · Casper, WYOct 15, 1967 · N4574U · Substantial damageNTSB DEN68D0111Accident · No injuriesClosedCessna 150 · Pierce, COOct 15, 1967 · N3614J · Substantial damageNTSB CHI68F0361Accident · Minor injuriesClosedAeronca 7AC · Mt.Sterling, OHOct 15, 1967 · N81403 · Substantial damageNTSB OAK68F0156Accident · No injuriesClosedBeech A45 · Truckee, CAOct 14, 1967 · N12277 · Substantial damageNTSB NYC68F0306Accident · Serious injuriesClosedCessna 150 · Braintree, MAOct 14, 1967 · N7994E · DestroyedNTSB SEA68D0157Accident · No injuriesClosedNavion A · Burley, IDOct 13, 1967 · N91188 · Substantial damageNTSB OAK68D0142Accident · No injuriesClosedBeech B35 · Elko, NVOct 12, 1967 · N5208C · Substantial damageNTSB DEN68F0129Accident · Minor injuriesClosedTaylorcraft BC-65 · Aztec, NMOct 12, 1967 · N29810 · Substantial damageNTSB MKC68D0275Accident · No injuriesClosedPiper PA-28 · Lewellen, NEOct 11, 1967 · N7515W · Substantial damageNTSB MIA68F0227Accident · Serious injuriesClosedBeech F35 · Nashville, TNOct 11, 1967 · N5000B · Substantial damageNTSB UNK68X0103Accident · No injuriesClosedCessna 180 · Val D Or, CanadaOct 10, 1967 · N26564 · Substantial damageNTSB MIA68D0255Accident · No injuriesClosedAero Comdr 100 · Exhuma Is., Burkina FasoOct 9, 1967 · N2923T · Substantial damageNTSB OAK68D0172Accident · No injuriesClosedCessna A188 · Madera, CAOct 8, 1967 · N8003V · DestroyedNTSB OAK68A0035Accident · 1 fatalClosedRyan ST-3KR · Moss Beach, CAOct 8, 1967 · N56253 · DestroyedNTSB LAX68D0171Accident · No injuriesClosedLuscombe 8A · Dateland, AZOct 8, 1967 · N1186B · Substantial damageNTSB SEA68D0178Accident · No injuriesClosedTaylorcraft BC12 · Malta, MTOct 7, 1967 · N34032 · DestroyedNTSB ANC68F0078Accident · No injuriesClosedCessna 180 · Anchorage, AKOct 7, 1967 · N4654A · Substantial damageNTSB ANC68F0056Accident · No injuriesClosedPiper PA-18 · Chitina, AKOct 7, 1967 · N1271A · Substantial damageNTSB ANC68D0059Accident · No injuriesClosedPiper PA-18 · Ketchikan, AKOct 7, 1967 · N9919D · Substantial damageNTSB SEA68D0162Accident · Minor injuriesClosedTaylorcraft BC12-D · Ketchum, IDOct 6, 1967 · N95561 · Substantial damageNTSB MIA68F0228Accident · No injuriesClosedPiper PA-25 · Houston, MSOct 6, 1967 · N7361Z · Substantial damageNTSB LAX68F0165Accident · Minor injuriesClosedAlon A-2 · El Monte, CAOct 6, 1967 · N6585Q · Substantial damageNTSB LAX68A0039Accident · 2 fatalClosedCessna 150 · Compton, CAOct 6, 1967 · N2967J · DestroyedNTSB NYC68A0057Accident · 1 fatalClosedPiper J-3 · Spencer, NYOct 5, 1967 · N42904 · DestroyedNTSB FTW68F0279Accident · Minor injuriesClosedMooney M20A · Plano, TXOct 5, 1967 · N8194E · Substantial damageNTSB LAX68D0154Accident · No injuriesClosedCessna 140 · Hunt.Beach, CAOct 3, 1967 · N89517 · Substantial damageNTSB MIA68F0224Accident · No injuriesClosedPiper PA-23 · Nashville, TNOct 1, 1967 · N5100Y · Substantial damageNTSB FTW68F0264Accident · Minor injuriesClosedAeronca 11AC · Benton, ARSep 30, 1967 · N3294E · Substantial damageNTSB SEA68F0149Accident · Minor injuriesClosedPiper J-3 · Kent, WASep 27, 1967 · N6899H · Substantial damageNTSB DIA68D0093Accident · No injuriesClosedCessna 150 · Rocky Mount, NCSep 27, 1967 · N8407G · Substantial damageNTSB MKC68F0380Accident · No injuriesClosedPiper PA-28 · Clinton, MOSep 25, 1967 · N6906W · Substantial damageNTSB MKC68D0235Accident · No injuriesClosedPiper PA-28 · Chariton, IASep 25, 1967 · N9428J · DestroyedNTSB MKC68D0289Accident · No injuriesClosedPiper PA-28 · Crystal, MNSep 24, 1967 · N9282J · Substantial damageNTSB FTW68D0241Accident · No injuriesClosedCessna 150 · Lexington, OKSep 24, 1967 · N7985E · Substantial damageNTSB MKC68D0270Accident · Minor injuriesClosedCessna 150 · Lodgepole, NESep 23, 1967 · N8262F · Substantial damageNTSB MIA68F0273Accident · No injuriesClosedBeech D50 · Nassau, Burkina FasoSep 23, 1967 · N4311D · Substantial damageNTSB LAX68F0134Accident · Minor injuriesClosedCessna 150 · Compton, CASep 23, 1967 · N3980U · DestroyedNTSB LAX68A0034Accident · Minor injuriesClosedLockheed PV-2 · Palm Sprngs, CASep 23, 1967 · N7436C · DestroyedNTSB CHI68F0340Accident · Serious injuriesClosedAeronca 7AC · Strongsville, OHSep 23, 1967 · N2733E · Substantial damageNTSB CHI68F0337Accident · No injuriesClosedLuscombe 8A · Muncie, INSep 23, 1967 · N2136K · Substantial damageNTSB UNK67X0159Accident · Minor injuriesClosedStinson 108-3 · Wauchula, FLSep 22, 1967 · N6019M · Substantial damageNTSB NYC68F0196Accident · Serious injuriesClosedBellanca 14-13 · So.Plainfield, NJSep 22, 1967 · N6503N · Substantial damageNTSB UNK67X0158Accident · 1 fatalClosedRepublic RC-3 · Mcneil Lake, CanadaSep 21, 1967 · N6316K · Substantial damage