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 MKC67D0569Accident · No injuriesClosedCessna 172H · Harmony, MNMay 6, 1967 · N2412L · Substantial damageNTSB LAX67F0437Accident · Minor injuriesClosedPiper PA-22 · Santa Susana, CAMay 6, 1967 · N2385P · Substantial damageNTSB LAX67D0425Accident · No injuriesClosedPiper PA-28 · Black Canyon, AZMay 6, 1967 · N5905W · DestroyedNTSB LAX67F0454Accident · Minor injuriesClosedCessna 205 · Long Beach, CAMay 5, 1967 · N4824U · Substantial damageNTSB FTW67F0570Accident · No injuriesClosedGrumman G-164 · Church Point, LAMay 5, 1967 · N526Y · Substantial damageNTSB LAX67D0415Accident · No injuriesClosedPiper J3C-65 · Scottsdale, AZMay 4, 1967 · N3635K · Substantial damageNTSB MKC67A0064Accident · Serious injuriesClosedGrumman G-73 · Huron, SDMay 3, 1967 · N298GB · Substantial damageNTSB LAX67F0384Accident · Minor injuriesClosedGlobe GC-1B · Van Nuys, CAMay 3, 1967 · N3749K · Substantial damageNTSB DEN67D0268Accident · No injuriesClosedPiper PA-18 · Gillette, WYMay 3, 1967 · N3578P · Substantial damageNTSB DIA67A0016Accident · 1 fatalClosedBeech D18S · Baltimore, MDMay 2, 1967 · N44596 · DestroyedNTSB MKC68F0042Accident · Serious injuriesClosedPiper PA-22 · Ainsworth, NEApr 30, 1967 · N8819D · Substantial damageNTSB MIA67D0510Accident · No injuriesClosedPiper PA-22 · Clewiston, FLApr 30, 1967 · N5399Z · Substantial damageNTSB MIA67D0501Accident · No injuriesClosedBeech A23 · Rogersville, TNApr 30, 1967 · N1494L · Substantial damageNTSB NYC67F0684Accident · No injuriesClosedPiper PA-22 · New Alexandria, PAApr 29, 1967 · N5725D · Substantial damageNTSB NYC67F0629Accident · No injuriesClosedPiper PA-22 · Gettysburg, PAApr 29, 1967 · N9951D · Substantial damageNTSB NYC67F0628Accident · Minor injuriesClosedCessna 150F · Duncannon, PAApr 29, 1967 · N8716S · Substantial damageNTSB MKC67F0507Accident · Serious injuriesClosedCessna 150 · Monett, MOApr 29, 1967 · N5713E · Substantial damageNTSB FTW67A0094Accident · 2 fatalClosedAeronca 11AC · N Richlnd Hls, TXApr 29, 1967 · N9664E · Substantial damageNTSB SEA68F0029Accident · No injuriesClosedLake LA-4 · Renton, WAApr 28, 1967 · N2012L · Substantial damageNTSB SEA67F0342Accident · Minor injuriesClosedBoeing A75N1 · Lind, WAApr 28, 1967 · N58700 · Substantial damageNTSB NYC67F0639Accident · No injuriesClosedPiaggio P.136L · Coatesville, PAApr 28, 1967 · N219A · Substantial damageNTSB MKC67F0568Accident · Minor injuriesClosedStinson 108-1 · Papillion, NEApr 27, 1967 · N8855K · Substantial damageNTSB MKC67F0505Accident · No injuriesClosedPiper PA-28 · Springfield, MOApr 27, 1967 · N7449R · Substantial damageNTSB FTW67F0568Accident · No injuriesClosedCessna 150 · Lone Grove, OKApr 27, 1967 · N2937J · Substantial damageNTSB DEN67D0283Accident · No injuriesClosedPiper PA-28 · Wheatland, WYApr 27, 1967 · N9773J · Substantial damageNTSB LAX67F0432Accident · Serious injuriesClosedCessna 140 · Compton, CAApr 26, 1967 · N77268 · DestroyedNTSB FTW67D0601Accident · No injuriesClosedCessna 140A · Dalhart, TXApr 26, 1967 · N5630C · Substantial damageNTSB DIA67D0087Accident · No injuriesClosedAeronca 7AC · Pennington Gap, VAApr 26, 1967 · N83762 · Substantial damageNTSB MKC67F0540Accident · No injuriesClosedPiper PA-22 · Thedford, NEApr 24, 1967 · N2832A · Substantial damageNTSB FTW67D0544Accident · No injuriesClosedBoeing E75 · Gueydan, LAApr 24, 1967 · N75266 · Substantial damageNTSB CHI67D0027Accident · Minor injuriesClosedNavion A · Waterville, OHApr 23, 1967 · N91173 · Substantial damageNTSB CHI67A0120Accident · 1 fatalClosedCessna 170 · Walbridge, OHApr 23, 1967 · N9096A · DestroyedNTSB DIA67F0080Accident · No injuriesClosedPiper PA-20 · Benson, NCApr 21, 1967 · N1508A · Substantial damageNTSB MKC67F0561Accident · Minor injuriesClosedPiper PA-22 · Higginsville, MOApr 18, 1967 · N2566P · Substantial damageNTSB LAX67F0380Accident · Minor injuriesClosedBrown-Linn A · Lancaster, CAApr 16, 1967 · N93169 · Substantial damageNTSB DIA67A0014Accident · 2 fatalClosedCessna 150A · Wakefield, VAApr 16, 1967 · N7013X · DestroyedNTSB SEA67D0334Accident · No injuriesClosedStearman A-75 · Moscow, IDApr 15, 1967 · N9082H · Substantial damageNTSB NYC67A0142Accident · 2 fatalClosedBoeing A75N1 · Trexlertown, PAApr 15, 1967 · N57843 · DestroyedNTSB MIA67F0516Accident · Serious injuriesClosedGlobe GC-1B · St Augustine, FLApr 15, 1967 · N80646 · DestroyedNTSB CHI67D0612Accident · No injuriesClosedPiper PA-24 · North Benton, OHApr 15, 1967 · N8100P · Substantial damageNTSB NYC67F0620Accident · Minor injuriesClosedPiper PA-28 · South Vineland, NJApr 14, 1967 · N5411W · Substantial damageNTSB FTW67F0558Accident · No injuriesClosedPiper PA-23 · Haskell, TXApr 13, 1967 · N6073Y · Substantial damageNTSB CHI67F0653Accident · Minor injuriesClosedBeech 65-A80 · Rhinelander, WIApr 13, 1967 · N314Q · Substantial damageNTSB ANC67A0057Accident · Serious injuriesClosedBell 47J-2A · Tyonek, AKApr 13, 1967 · N3070G · Substantial damageNTSB FTW67A0100Accident · 2 fatalClosedCessna 337 · La Porte, TXApr 11, 1967 · N5484S · DestroyedNTSB DEN67F0246Accident · Minor injuriesClosedCessna 310C · Santa Fe, NMApr 11, 1967 · N1813H · Substantial damageNTSB SEA67F0305Accident · No injuriesClosedChampion 7ECA · Mose Lake, WAApr 10, 1967 · N5135T · Substantial damageNTSB NYC67F0583Accident · No injuriesClosedPiper PA-30 · Allentown, PAApr 10, 1967 · N7110Y · Substantial damageNTSB FTW67F0556Accident · Minor injuriesClosedSnow 600 · Newkirk, OKApr 10, 1967 · N1617S · DestroyedNTSB MIA67F0497Accident · No injuriesClosedPiper PA-22 · Augusta, GAApr 9, 1967 · N4972Z · Substantial damage