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 MIA71FKQ27Accident · Serious injuriesClosedCessna A188A · Carrollton, MSMar 11, 1971 · N2168U · Substantial damageNTSB MKC71DCJ54Accident · No injuriesClosedPiper PA-24 · Rushville, NEMar 10, 1971 · N7755P · Substantial damageNTSB LAX71DVA62Accident · No injuriesClosedPiper J3C · Turlock, CAMar 9, 1971 · N30772 · Substantial damageNTSB LAX71FUA50Accident · Serious injuriesClosedHughes 269B · Long Beach, CAMar 7, 1971 · N9519F · DestroyedNTSB DEN71DXD54Accident · No injuriesClosedCessna 182 · Aspen, COMar 7, 1971 · N2700F · Substantial damageNTSB FTW71AF056Accident · 1 fatalClosedPiper J-3 · Rio Grand Cty, TXMar 6, 1971 · N77552 · DestroyedNTSB ANC71DAG70Accident · No injuriesClosedPiper PA-18 · Skwentna, AKMar 6, 1971 · N7391D · Substantial damageNTSB NYC71FFD39Accident · Minor injuriesClosedCessna 140 · Mrstns Mills, MAMar 2, 1971 · N76354 · Substantial damageNTSB FTW71DRG89Accident · 1 fatalClosedCessna 206 · Lagunitas, MexicoMar 1, 1971 · N2660X · DestroyedNTSB MIA71FKT43Accident · No injuriesClosedChampion 7ECA · Punta Gorda, FLFeb 28, 1971 · N11073 · Substantial damageNTSB NYC71FHM20Accident · No injuriesClosedPiper PA-12 · Monticello, NYFeb 27, 1971 · N2915M · Substantial damageNTSB MKC71DCQ30Accident · Minor injuriesClosedPiper J-3C · Russell, KSFeb 24, 1971 · N3517K · Substantial damageNTSB LAX71FUG44Accident · Serious injuriesClosedCessna 150 · Murrieta, CAFeb 21, 1971 · N6128T · Substantial damageNTSB DEN71FXD51Accident · No injuriesClosedCessna 150F · Aurora, COFeb 21, 1971 · N8209F · Substantial damageNTSB CHI71FBJ57Accident · No injuriesClosedCessna 172K · Montague, MIFeb 21, 1971 · N79421 · Substantial damageNTSB MIA71FKD12Accident · Minor injuriesClosedPiper PA-22 · Union City, TNFeb 20, 1971 · N8630C · DestroyedNTSB MKC71DDD19Accident · No injuriesClosedPiper J3C-65 · Crosby, NDFeb 16, 1971 · N25835 · DestroyedNTSB OAK71DVG55Accident · No injuriesClosedPiper PA-24 · Red Bluff, CAFeb 15, 1971 · N122GH · Substantial damageNTSB OAK71AP033Accident · 1 fatalClosedCulver LCA · Dos Palos, CAFeb 15, 1971 · N32467 · DestroyedNTSB OAK72DJY04Accident · 2 fatalClosedDouglas DC-3 · Phnom Penh, CBFeb 14, 1971 · N55L · DestroyedNTSB NYC71FHJ21Accident · No injuriesClosedPiper PA-28 · White Plains, NYFeb 14, 1971 · N7489R · Substantial damageNTSB NYC71FFA49Accident · No injuriesClosedPiper PA-28 · West Dover, VTFeb 14, 1971 · N5253L · Substantial damageNTSB LAX71FUM29Accident · Serious injuriesClosedBeech C-45H · Lakeside, CAFeb 14, 1971 · N4709C · DestroyedNTSB CHI71FLQ17Accident · Minor injuriesClosedAeronca 7AC · Stanton, KYFeb 14, 1971 · N82873 · Substantial damageNTSB FTW71FQG55Accident · No injuriesClosedPiper PA-30 · Big Spring, TXFeb 7, 1971 · N7039Y · Substantial damageNTSB SEA71FYM24Accident · Serious injuriesClosedCessna 421B · Gannett, IDFeb 6, 1971 · N8025Q · Substantial damageNTSB MKC71FCG43Accident · Minor injuriesClosedAlon A-2 · Topeka, KSFeb 6, 1971 · N6578Q · Substantial damageNTSB DEN71FQA46Accident · Serious injuriesClosedCessna 150F · Albuquerque, NMFeb 5, 1971 · N8651S · Substantial damageNTSB FTW71FRG74Accident · No injuriesClosedPiper PA-24 · San Antonio, TXFeb 2, 1971 · N8540P · Substantial damageNTSB MIA71AM064Accident · 2 fatalClosedPiper PA-23 · Daytona Beach, FLJan 31, 1971 · N2251P · DestroyedNTSB FTW71FPG35Accident · No injuriesClosedMooney M20E · Little Rock, ARJan 31, 1971 · N9302M · Substantial damageNTSB FTW71DPJ27Accident · Minor injuriesClosedCessna 172 · Taloga, OKJan 31, 1971 · N2595Y · Substantial damageNTSB MKC71FCJ43Accident · No injuriesClosedPiper PA-18 · Hyannis, NEJan 27, 1971 · N4297Z · Substantial damageNTSB NYC71FGM27Accident · No injuriesClosedBeech A23 · Belle Vernon, PAJan 24, 1971 · N8742M · Substantial damageNTSB OAK71FJA09Accident · Minor injuriesClosedCessna 150 · Honolulu, HIJan 23, 1971 · N2014Z · Substantial damageNTSB MIA71FKQ24Accident · Minor injuriesClosedPiper PA-22 · Raymond, MSJan 23, 1971 · N2400A · Substantial damageNTSB NYC71FHM15Accident · Serious injuriesClosedMooney M20F · Morristown, NJJan 21, 1971 · N9393M · Substantial damageNTSB FTW71AF041Accident · 2 fatalClosedPiper PA-30 · New Orleans, LAJan 21, 1971 · N7444Y · DestroyedNTSB NYC71FFG15Accident · Minor injuriesClosedCessna 310N · Auburn, MEJan 19, 1971 · N5046Q · Substantial damageNTSB NYC71AN082Accident · 1 fatalClosedCessna 411 · Little Ferry, NJJan 19, 1971 · N7386U · DestroyedNTSB LAX71FUJ58Accident · Minor injuriesClosedAmer Avco AA1 · Flagstaff, AZJan 18, 1971 · N9304L · Substantial damageNTSB CHI71AC054Accident · 1 fatalClosedBeech M-35 · Boyne, MIJan 17, 1971 · N9878R · DestroyedNTSB DEN71DXD46Accident · No injuriesClosedCessna 170B · Aspen, COJan 15, 1971 · N3056A · Substantial damageNTSB ANC71AA037Accident · No injuriesClosedCessna 180 · Fairbanks, AKJan 14, 1971 · N91442 · Substantial damageNTSB NYC71FFJ23Accident · No injuriesClosedBeech V35A · Endicott, NYJan 13, 1971 · N7941R · 2 aircraft · Substantial damageNTSB DEN71FQA37Accident · No injuriesClosedCessna 310I · Albuquerque, NMJan 13, 1971 · N8131M · Substantial damageNTSB FTW71FRA36Accident · Minor injuriesClosedCessna 172F · Bryan, TXJan 11, 1971 · N8232U · Substantial damageNTSB FTW71FRA33Accident · No injuriesClosedPiper PA-12 · Pearland, TXJan 11, 1971 · N2426M · Substantial damageNTSB FTW71FRA34Accident · No injuriesClosedCessna 172K · Katy, TXJan 10, 1971 · N7210G · Substantial damageNTSB DEN71FXD45Accident · No injuriesClosedCessna 150 · Aurora, COJan 10, 1971 · N23316 · Substantial damage