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 MKC71FDD12Accident · No injuriesClosedAlon A2-A · Napoleon, NDSep 8, 1970 · N5470F · Substantial damageNTSB FTW71FRJ14Accident · No injuriesClosedPiper PA-25 · Wisner, LASep 8, 1970 · N4920Y · Substantial damageNTSB DCA71AZ002Accident · 11 fatalClosedDouglas DC-8F · Jamaica, NYSep 8, 1970 · N4863T · DestroyedNTSB NYC71FFJ13Accident · Serious injuriesClosedVolmer VJ-22 · Fulton, NYSep 7, 1970 · N178RW · DestroyedNTSB MIA71FKW09Accident · No injuriesClosedCessna 177 · Portland, TNSep 7, 1970 · N3463T · Substantial damageNTSB FTW71DQG33Accident · No injuriesClosedCessna 188 · Sunray, TXSep 7, 1970 · N8090V · Substantial damageNTSB CHI71FBQ14Accident · No injuriesClosedPiper J-3 · Havana, ILSep 7, 1970 · N21681 · Substantial damageNTSB CHI71DDJ12Accident · Serious injuriesClosedBeech C-45H · Lakewood, WISep 7, 1970 · N9961Z · DestroyedNTSB MIA71FKG38Accident · 1 fatalClosedWolf-Hirth LO-150 · Sylvania, GASep 6, 1970 · N1110V · DestroyedNTSB DEN71DXT08Accident · No injuriesClosedPiper PA-28 · Mexican Hat, UTSep 6, 1970 · N6859W · Substantial damageNTSB MKC71DDD11Accident · No injuriesClosedErco 415-C · Verona, NDSep 5, 1970 · N99826 · Substantial damageNTSB CHI71FGG10Accident · No injuriesClosedErco 415-C · Findlay, OHSep 5, 1970 · N94002 · Substantial damageNTSB CHI71FBJ28Accident · Serious injuriesClosedCessna 310H · Houghton Lk, MISep 5, 1970 · N1019Q · DestroyedNTSB NYC71AN032Accident · 4 fatalClosedPiper PA-28 · Minersville, PASep 4, 1970 · N8289N · DestroyedNTSB LAX71DUJ20Accident · No injuriesClosedPiper PA-28 · Mayer, AZSep 4, 1970 · N133SF · Substantial damageNTSB CHI71FBD20Accident · No injuriesClosedMaule M-4 · Shannon, ILSep 4, 1970 · N4730T · Substantial damageNTSB CHI71DDW14Accident · No injuriesClosedMooney M20C · Port Huron, MISep 4, 1970 · N2723W · Substantial damageNTSB CAN72AP905Accident · 2 fatalClosedPiper PA-30 · Prince George, CanadaSep 4, 1970 · N8636Y · Substantial damageNTSB FTW71FQD08Accident · No injuriesClosedCessna 140 · El Paso, TXSep 2, 1970 · N4193N · Substantial damageNTSB MIA71KKQ10Accident · 1 fatalClosedPiper PA-18A · Lucedale, MSSep 1, 1970 · N6364D · DestroyedNTSB LAX71FUA15Accident · No injuriesClosedCessna 150 · Torrance, CASep 1, 1970 · N6752S · Substantial damageNTSB OAK71AP013Accident · 4 fatalClosedBeech A35 · Georgetown, CAAug 31, 1970 · N899A · DestroyedNTSB MIA71FKG32Accident · Serious injuriesClosedPiper PA-18 · Girard, GAAug 31, 1970 · N4896A · DestroyedNTSB CAN71AP904Accident · Minor injuriesClosedCessna 175 · Mons Lake, CanadaAug 31, 1970 · N6638E · Substantial damageNTSB OAK71FVA24Accident · 1 fatalClosedGrumman G-164A · Kerman, CAAug 30, 1970 · N898X · DestroyedNTSB IAD71DIG11Accident · No injuriesClosedPiper PA-28 · Fallston, MDAug 30, 1970 · N8047N · Substantial damageNTSB DEN71FQD06Accident · Minor injuriesClosedCessna 172 · Sunspot, NMAug 30, 1970 · N3917L · Substantial damageNTSB CHI71FGD14Accident · Minor injuriesClosedCessna 177 · Northfield, OHAug 30, 1970 · N30037 · DestroyedNTSB IAD71FLJ11Accident · No injuriesClosedCessna 177 · Raleigh, NCAug 29, 1970 · N3271T · Substantial damageNTSB CHI71FBA11Accident · No injuriesClosedPiper PA-25 · Laurel, INAug 29, 1970 · N6503Z · Substantial damageNTSB OAK71FVM11Accident · No injuriesClosedZlin 526F · Belmont, CAAug 28, 1970 · N34RS · Substantial damageNTSB OAK71DVD09Accident · Minor injuriesClosedCessna 172 · Concord, CAAug 28, 1970 · N2439L · Substantial damageNTSB FTW71DPD14Accident · No injuriesClosedPiper PA-28 · Hico, TXAug 28, 1970 · N8600W · Substantial damageNTSB ANC71DAD03Accident · No injuriesClosedCessna 170 · Haines, AKAug 27, 1970 · N4116V · Substantial damageNTSB SEA71AS011Accident · 1 fatalClosedCessna 172 · Agness, ORAug 26, 1970 · N79511 · DestroyedNTSB MKC71FCD21Accident · No injuriesClosedCessna 150K · Leighton, IAAug 26, 1970 · N6274G · Substantial damageNTSB MIA71FLD12Accident · No injuriesClosedPiper PA-24 · St Petersburg, FLAug 25, 1970 · N7934P · Substantial damageNTSB MIA71DKG28Accident · No injuriesClosedPiper PA-25 · Pinehurst, GAAug 25, 1970 · N7360Z · DestroyedNTSB FTW71DRG21Accident · Minor injuriesClosedPiper PA-25 · Mason, TXAug 25, 1970 · N6243Z · DestroyedNTSB FTW71DRD09Accident · Minor injuriesClosedAero Cmdr 600S2D · Napoleonville, LAAug 25, 1970 · N1777S · Substantial damageNTSB MKC71DDM21Accident · No injuriesClosedPiper J3C-65 · Duluth, MNAug 24, 1970 · N6250H · Substantial damageNTSB DEN71AD007Accident · Minor injuriesClosedLockheed 188 · Hill Af Base, UTAug 24, 1970 · N855U · DestroyedNTSB OAK71DVA21Accident · No injuriesClosedCallair A-9 · Kettleman Cty, CAAug 23, 1970 · N7624V · DestroyedNTSB MKC71FCQ14Accident · No injuriesClosedPiper PA-22 · Dighton, KSAug 23, 1970 · N8623D · Substantial damageNTSB MIA71FLG08Accident · No injuriesClosedPiper J3C-65 · Hartsville, SCAug 23, 1970 · N98105 · Substantial damageNTSB DEN71FQA07Accident · Minor injuriesClosedAero Comdr 680V · Taos, NMAug 23, 1970 · N601G · Substantial damageNTSB DEN71AD006Accident · No injuriesClosedCessna Wren · Fort Washakie, WYAug 23, 1970 · N1929X · Substantial damageNTSB CAN71AQ905Accident · Minor injuriesClosedPiper PA-22 · Furnace Falls, CanadaAug 23, 1970 · N8811D · Substantial damageNTSB MIA71FLD11Accident · Minor injuriesClosedCessna 177 · Holmes Beach, FLAug 22, 1970 · N29354 · Substantial damageNTSB IAD71DKM10Accident · No injuriesClosedMonocoupe 90A · Monroe, NCAug 22, 1970 · N119V · Substantial damage