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 LAX68F0265Accident · No injuriesClosedCessna 411 · Phoenix, AZDec 13, 1967 · N617EJ · Substantial damageNTSB FTW68F0369Accident · No injuriesClosedPiper PA-25 · Gonzales, LADec 13, 1967 · N6519Z · DestroyedNTSB OAK68F0249Accident · Serious injuriesClosedCessna 150F · Wahaiwa, HIDec 12, 1967 · N6285R · DestroyedNTSB OAK68D0287Accident · No injuriesClosedMooney M21 · Truckee, CADec 10, 1967 · N74567 · Substantial damageNTSB MIA68F0368Accident · No injuriesClosedPiper PA-22 · Deland, FLDec 9, 1967 · N4312A · Substantial damageNTSB DEN68F0168Accident · Serious injuriesClosedBeech 65-80 · Salt Lake City, UTDec 9, 1967 · N814B · DestroyedNTSB MIA68O0008Accident · No injuriesClosedGrumman G-21A · Tortola, U.S. Virgin IslandsDec 5, 1967 · N95467 · Substantial damageNTSB CHI68F0469Accident · No injuriesClosedPiper PA-23 · Cottage Hill, ILDec 5, 1967 · N148AF · Substantial damageNTSB MKC68F0371Accident · No injuriesClosedNavion A · Willmar, MNDec 3, 1967 · N4861K · Substantial damageNTSB LAX68D0243Accident · Minor injuriesClosedPorterfield LP-65 · Riverside, CADec 3, 1967 · N27242 · Substantial damageNTSB MKC68F0385Accident · Serious injuriesClosedAero Comdr 680-FL · North Platte, NEDec 2, 1967 · N747NT · DestroyedNTSB CHI68F0450Accident · Minor injuriesClosedBeech H35 · Lombard, ILNov 28, 1967 · N5557D · DestroyedNTSB CHI68D0548Accident · No injuriesClosedCessna 150 · Germantown, WINov 25, 1967 · N3007J · Substantial damageNTSB CHI68D0480Accident · No injuriesClosedPiper J-3 · Randolph, WINov 25, 1967 · N1276V · Substantial damageNTSB CHI68F0467Accident · Minor injuriesClosedTaylorcraft BC12-D · Menominee, MINov 24, 1967 · N43273 · Substantial damageNTSB FTW68F0374Accident · Serious injuriesClosedMooney M20C · San Antonio, TXNov 23, 1967 · N6471U · Substantial damageNTSB OAK68D0235Accident · Minor injuriesClosedBoeing A75 · Montpellier, CANov 22, 1967 · N56416 · Substantial damageNTSB FTW68D0365Accident · No injuriesClosedBeech 35 · Booneville, ARNov 22, 1967 · N3878N · Substantial damageNTSB UNK68X0110Incident · No injuriesClosedDouglas DC-8 · Munich, GeorgiaNov 19, 1967 · N809PANTSB NYC68D0293Accident · No injuriesClosedPiper PA-18 · Orleans, MANov 19, 1967 · N9075H · Substantial damageNTSB MKC68F0355Accident · Minor injuriesClosedAeronca 15AC · Markville, MNNov 19, 1967 · N1451H · Substantial damageNTSB MIA68F0303Accident · No injuriesClosedAeronca 7DC · Sanford, MSNov 19, 1967 · N4651E · Substantial damageNTSB OAK68D0225Accident · No injuriesClosedBeech C35 · Oakland, CANov 18, 1967 · N676D · Substantial damageNTSB OAK68D0238Accident · No injuriesClosedPiper PA-32 · San Jose, CANov 17, 1967 · N3223W · Substantial damageNTSB NYC68F0292Accident · No injuriesClosedCessna 310 · Carlstadt, NJNov 17, 1967 · N193R · Substantial damageNTSB MKC68F0335Accident · No injuriesClosedPiper PA-12 · Eden Prairie, MNNov 14, 1967 · N3017M · Substantial damageNTSB MKC68F0358Accident · No injuriesClosedCessna 172 · Hill City, MNNov 13, 1967 · N5686A · Substantial damageNTSB MKC68F0406Accident · No injuriesClosedCessna 170 · Eldon, MONov 12, 1967 · N4254V · Substantial damageNTSB LAX68O0295Accident · Minor injuriesClosedPiper PA-22 · Nogales, MexicoNov 12, 1967 · N7202D · DestroyedNTSB FTW68F0387Accident · No injuriesClosedBeech J35 · Beaumont, TXNov 12, 1967 · N8358D · Substantial damageNTSB ANC68D0068Accident · No injuriesClosedAeronca 7GC · Willow, AKNov 12, 1967 · N4883E · Substantial damageNTSB MKC68D0419Accident · No injuriesClosedPiper PA-28 · Ottawa, KSNov 11, 1967 · N5366W · Substantial damageNTSB MIA68D0285Accident · No injuriesClosedCessna 150 · Villa Rica, GANov 11, 1967 · N6254R · Substantial damageNTSB MKC68F0300Accident · No injuriesClosedPiper PA-25 · Leroy, KSNov 10, 1967 · N6591Z · Substantial damageNTSB FTW68F0360Accident · No injuriesClosedPiper PA-28 · Lubbock, TXNov 10, 1967 · N6066W · Substantial damageNTSB DEN68D0153Accident · No injuriesClosedCessna 150 · Columbus, NMNov 10, 1967 · N6297R · Substantial damageNTSB CHI68F0514Accident · Serious injuriesClosedStinson 108 · Wellington, OHNov 10, 1967 · N97319 · Substantial damageNTSB MKC68F0360Accident · No injuriesClosedPiper J-3 · St.Charles, MNNov 9, 1967 · N88173 · Substantial damageNTSB NYC68F0315Accident · Minor injuriesClosedHelio H-395 · Wilmington, VTNov 7, 1967 · N4197D · DestroyedNTSB FTW68F0305Accident · No injuriesClosedCessna 177 · Waco, TXNov 7, 1967 · N2325Y · DestroyedNTSB LAX68D0177Accident · Minor injuriesClosedCessna 180 · Sasabe, MexicoNov 4, 1967 · N7912VNTSB MIA68D0264Accident · No injuriesClosedBoeing B75N1 · Belzoni, MSNov 2, 1967 · N52598 · Substantial damageNTSB NYC68F0268Accident · Serious injuriesClosedCessna 140 · Wayne, NJOct 30, 1967 · N89161 · DestroyedNTSB FTW68D0415Accident · Minor injuriesClosedPiper PA-22 · Honobia, OKOct 28, 1967 · N6707B · Substantial damageNTSB NYC68A0066Accident · No injuriesClosedPiper PA-32 · Allentown, PAOct 26, 1967 · N4159W · Substantial damageNTSB LAX68D0174Accident · Minor injuriesClosedCessna 180 · Avenal, CAOct 26, 1967 · N5232D · DestroyedNTSB MIA68F0300Accident · No injuriesClosedCessna 150G · W Hollywood, FLOct 25, 1967 · N4744X · Substantial damageNTSB DEN68D0132Accident · No injuriesClosedPiper PA-18 · Riverton, WYOct 25, 1967 · N4152Z · Substantial damageNTSB CHI68A0038Accident · 2 fatalClosedCessna 182H · Collinsville, ILOct 24, 1967 · N1824X · DestroyedNTSB DEN68F0108Accident · No injuriesClosedBeech C35 · Broomfield, COOct 22, 1967 · N1833D · Substantial damage