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 LAX70FUQ27Accident · No injuriesClosedErco 415-C · Pacoima, CAApr 19, 1970 · N3428H · Substantial damageNTSB LAX70AL066Accident · 4 fatalClosedCessna 172G · St.Johns, AZApr 19, 1970 · N4301L · DestroyedNTSB OAK70DVD17Accident · No injuriesClosedGlobe GC-1B · Concord, CAApr 18, 1970 · N78234 · Substantial damageNTSB MIA70DKT17Accident · Minor injuriesClosedCessna 172 · Sanibel, FLApr 18, 1970 · N35509 · Substantial damageNTSB FTW70DRG58Accident · No injuriesClosedBeech 95-B55 · Cabo Sn Lucas, MexicoApr 18, 1970 · N567M · Substantial damageNTSB IAD70FKM04Accident · No injuriesClosedChampion 7FC · Yadkinville, NCApr 17, 1970 · N9019B · Substantial damageNTSB LAX70FUG26Accident · No injuriesClosedCessna 310C · Chino, CAApr 16, 1970 · N1840H · Substantial damageNTSB MIA70AM083Accident · 2 fatalClosedDouglas C-54D · Miami, FLApr 14, 1970 · HC-AON · DestroyedNTSB ANC70AA070Accident · No injuriesClosedDehavilland DHC-2 · Prudhoe Bay, AKApr 13, 1970 · N7620 · Substantial damageNTSB SEA70AS038Accident · 2 fatalClosedLuscombe 8A · Vancouver, WAApr 12, 1970 · N2142K · DestroyedNTSB MIA70FKQ03Accident · No injuriesClosedPiper PA-22 · Carthage, MSApr 12, 1970 · N8414D · Substantial damageNTSB MIA70DLD18Accident · No injuriesClosedMooney M20C · Gotha, FLApr 11, 1970 · N6453U · Substantial damageNTSB LAX70FUQ21Accident · No injuriesClosedPiper PA-28 · Santa Susana, CAApr 11, 1970 · N5046W · Substantial damageNTSB FTW70FPJ11Accident · Minor injuriesClosedCessna 182 · Okeene, OKApr 11, 1970 · N3005Q · Substantial damageNTSB FTW70DRG34Accident · No injuriesClosedBell 47G-4A · Corpus Chrsti, TXApr 11, 1970 · N1438W · Substantial damageNTSB FTW70AF063Accident · 2 fatalClosedPiper PA-28 · Fordyce, ARApr 11, 1970 · N5992W · DestroyedNTSB DEN70AD052Accident · No injuriesClosedPiper PA-32 · Cloudcroft, NMApr 11, 1970 · N3249W · Substantial damageNTSB OAK70DVD14Accident · No injuriesClosedCessna 172 · Schellville, CAApr 8, 1970 · N8035X · DestroyedNTSB SEA70FWD08Accident · 1 fatalClosedBensen B-8 · Arlington, WAApr 5, 1970 · N7787 · DestroyedNTSB MKC70FDD11Accident · No injuriesClosedMooney M20G · Forbes, NDApr 4, 1970 · N3640N · Substantial damageNTSB MIA70FKW03Accident · Minor injuriesClosedCessna 150J · College Grve, TNApr 3, 1970 · N51409 · DestroyedNTSB NYC70AN107Accident · Serious injuriesClosedBeech C-45 · Albany, NYApr 2, 1970 · N146L · Substantial damageNTSB MKC70FCA06Accident · No injuriesClosedCessna 337C · St Louis, MOApr 2, 1970 · N2662S · Substantial damageNTSB FTW70DPD08Accident · No injuriesClosedCessna 150 · Eagle Mtn, TXApr 2, 1970 · N23261 · Substantial damageNTSB MKC70FCA05Accident · Serious injuriesClosedCessna 310G · Cape Girardeau, MOApr 1, 1970 · N2915R · DestroyedNTSB MIA70FKTI3Accident · Serious injuriesClosedCessna 172G · Clewiston, FLMar 30, 1970 · N4691L · DestroyedNTSB MIA70DLD11Accident · No injuriesClosedChampion 7ECA · Silver Sprngs, FLMar 30, 1970 · N11044 · Substantial damageNTSB FTW70DRD09Accident · No injuriesClosedCessna 177 · Lacassine, LAMar 29, 1970 · N29503 · Substantial damageNTSB LAX70DUJ30Accident · No injuriesClosedGrumman G-164 · Poston, AZMar 28, 1970 · N618U · Substantial damageNTSB FTW70DRD08Accident · No injuriesClosedBoeing B75NL · Lake Charles, LAMar 28, 1970 · N4443N · Substantial damageNTSB MKC70DCA04Accident · Minor injuriesClosedCessna 172K · Clarence, MOMar 26, 1970 · N79929 · Substantial damageNTSB MIA70DKG23Accident · No injuriesClosedCessna 172 · St Marys, GAMar 26, 1970 · N7332T · Substantial damageNTSB IAD70II053Incident · No injuriesClosedDouglas DC-8F · Dulles Int Apt, VAMar 26, 1970 · N8047U · Minor damageNTSB CHI70AC060Accident · 1 fatalClosedBeech 65-A80 · Wheeling, ILMar 25, 1970 · N242U · DestroyedNTSB DEN70FXD18Accident · Minor injuriesClosedCessna 172 · Hudson, COMar 24, 1970 · N46746 · Substantial damageNTSB SEA70FWD06Accident · No injuriesClosedBeech 35 · Georgetown, WAMar 23, 1970 · N3164V · Substantial damageNTSB OAK70AP048Accident · 6 fatalClosedPiper PA-30 · Cottonwood, CAMar 23, 1970 · N8720V · DestroyedNTSB FTW70DPJ09Accident · Minor injuriesClosedBeech C-50 · Sonora, MexicoMar 23, 1970 · N3796B · DestroyedNTSB NYC70AN103Accident · 3 fatalClosedBeech C-45H · Binghamton, NYMar 22, 1970 · N497DM · DestroyedNTSB CHI70FBD21Accident · No injuriesClosedBeech 35 · West Chicago, ILMar 21, 1970 · N3776N · Substantial damageNTSB ANC70AA065Accident · No injuriesClosedCessna 180 · Dillingham, AKMar 19, 1970 · N9651B · Substantial damageNTSB FTW70AF057Accident · 3 fatalClosedCessna 310F · Millington, TNMar 18, 1970 · N6705X · DestroyedNTSB CHI70FBM08Accident · Serious injuriesClosedCessna 140 · Roanoke, INMar 16, 1970 · N72774 · DestroyedNTSB FTW70DRG20Accident · No injuriesClosedNavion A · Mason, TXMar 15, 1970 · N91194 · DestroyedNTSB MKC70DCG14Accident · No injuriesClosedCessna 150G · Tipton, MOMar 14, 1970 · N964MA · Substantial damageNTSB FTW70FPD06Accident · No injuriesClosedN.American SNJ-4 · Brownwood, TXMar 14, 1970 · N9529C · Substantial damageNTSB NYC70FFG03Accident · No injuriesClosedCessna 411 · Claremont, NHMar 13, 1970 · N985J · Substantial damageNTSB LAX70FUI17Accident · No injuriesClosedCessna 177 · Sta Barbara, CAMar 13, 1970 · N1260S · DestroyedNTSB CHI70FBG09Accident · Minor injuriesClosedBeech 23 · Monroe, MIMar 13, 1970 · N2313Z · Substantial damageNTSB MIA70DKT09Accident · No injuriesClosedCessna 150 · Opa Locka, FLMar 12, 1970 · N22247 · Substantial damage