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 OAK71DVA09Accident · No injuriesClosedCessna 195 · Lone Pine, CAJul 11, 1970 · N195BD · Substantial damageNTSB CHI71FDW04Accident · Serious injuriesClosedRepublic RC-3 · Port Huron, MIJul 11, 1970 · N6465K · Substantial damageNTSB CHI71FBJ04Accident · No injuriesClosedBell 47GMK3 · Stanton, MIJul 10, 1970 · N953B · Substantial damageNTSB ANC71FAA07Accident · Minor injuriesClosedCessna 120 · Fairbanks, AKJul 10, 1970 · N77303 · DestroyedNTSB MIA71AM004Accident · 4 fatalClosedBeech 23 · Ocala, FLJul 9, 1970 · N8715M · DestroyedNTSB MIA71AM003Accident · 1 fatalClosedAeronca 65-TL · Lavonia, GAJul 8, 1970 · N31437 · DestroyedNTSB MIA71DKG07Accident · No injuriesClosedPiper PA-25 · Fort Valley, GAJul 7, 1970 · N7101Z · DestroyedNTSB DEN71DXD13Accident · Minor injuriesClosedPiper PA-28 · Littleton, COJul 6, 1970 · N8043N · Substantial damageNTSB MKC71FCD02Accident · No injuriesClosedBeech A35 · Mediapolis, IAJul 5, 1970 · N8567A · Substantial damageNTSB LAX71FUQ01Accident · No injuriesClosedHelio Acft H-295 · Tehachapi, CAJul 5, 1970 · N5462E · Substantial damageNTSB LAX70FUJ01Accident · No injuriesClosedPiper PA-23 · Mesa, AZJul 5, 1970 · N4066P · Substantial damageNTSB CHI71FBA02Accident · Serious injuriesClosedPiper PA-22 · Crawfordsvlle, INJul 5, 1970 · N6043D · Substantial damageNTSB SEA71FWD02Accident · No injuriesClosedBrantly B-2 · Bellevue, WAJul 4, 1970 · N5963X · Substantial damageNTSB NYC71FFD02Accident · Minor injuriesClosedPiper PA-18 · Amesbury, MAJul 4, 1970 · N65889 · DestroyedNTSB MIA71AM001Accident · 1 fatalClosedCessna 120 · Ringgold, GAJul 4, 1970 · N77269 · DestroyedNTSB CHI71DGD01Accident · No injuriesClosedAmer Avco AA-1 · Painesville, OHJul 4, 1970 · N5702L · Substantial damageNTSB SEA71FWD01Accident · No injuriesClosedHughes 269B · Bellingham, WAJul 3, 1970 · N9337F · Substantial damageNTSB FTW71FPM01Accident · Minor injuriesClosedPiper PA-22 · Wagoner, OKJul 3, 1970 · N5020Z · Substantial damageNTSB ANC71AA001Accident · 5 fatalClosedDouglas DC-3 · Mcgrath, AKJul 3, 1970 · N154R · DestroyedNTSB NYC71FGM01Accident · Minor injuriesClosedAeronca 65-TAC · Belle Vernon, PAJul 2, 1970 · N36834 · Substantial damageNTSB IAD71FIG02Accident · Serious injuriesClosedPiper PA-28 · Sykesville, MDJul 2, 1970 · N8009N · Substantial damageNTSB FTW71DQD04Accident · No injuriesClosedHelio Acft H-250 · Durango, MexicoJul 2, 1970 · N5456E · Substantial damageNTSB LAX71DUJ10Accident · No injuriesClosedGlobe GC-1B · Showlow, AZJul 1, 1970 · N78246 · Substantial damageNTSB FTW71FRA01Accident · No injuriesClosedGrumman G-164 · Raywood, TXJul 1, 1970 · N364Y · Substantial damageNTSB CHI71FBD01Accident · No injuriesClosedAmer Avco AA1 · Sycamore, ILJul 1, 1970 · N5683L · Substantial damageNTSB IAD71AI001Accident · No injuriesClosedSikorsky S-55B · Davis, WVJun 30, 1970 · N414A · Substantial damageNTSB IAD70FIJ08Accident · Serious injuriesClosedBeech 65S · Dublin, VAJun 30, 1970 · N447S · DestroyedNTSB CHI70FDW24Accident · Minor injuriesClosedPiper PA-22 · Richmond, MIJun 30, 1970 · N946A · Substantial damageNTSB CHI70DGD20Accident · No injuriesClosedPiper PA-28 · Milan, OHJun 30, 1970 · N6335R · Substantial damageNTSB CAN71AQ904Accident · Minor injuriesClosedGrumman G-44 · Chibougamau, CanadaJun 30, 1970 · N40011 · Substantial damageNTSB MIA70DLD37Accident · No injuriesClosedAmer Avco AA-1 · Sanford, FLJun 29, 1970 · N6155L · Substantial damageNTSB SEA70DDG13Accident · No injuriesClosedPiper PA-28 · Fairfield, MTJun 28, 1970 · N7338J · Substantial damageNTSB MKC70DCA18Accident · No injuriesClosedCessna 172E · Richwoods, MOJun 28, 1970 · N5475T · Substantial damageNTSB SEA70FDA16Accident · Serious injuriesClosedPiper PA-18 · Wolf Point, MTJun 27, 1970 · N7078B · DestroyedNTSB MKC70FCG35Accident · Serious injuriesClosedPiper J5A · Vinland, KSJun 27, 1970 · N35385 · Substantial damageNTSB FTW71DPG04Accident · No injuriesClosedAero Comdr S-2R · Eudora, ARJun 27, 1970 · N8877Q · Substantial damageNTSB IAD70AI065Accident · 3 fatalClosedCessna 310D · West Union, WVJun 26, 1970 · N6841T · DestroyedNTSB FTW70FRD23Accident · Minor injuriesClosedBell 47J · Cameron, LAJun 26, 1970 · N2896B · DestroyedNTSB FTW70FRA36Accident · Minor injuriesClosedGrumman G-164A · Rosharon, TXJun 26, 1970 · N5324 · Substantial damageNTSB LAX70FUG50Accident · No injuriesClosedBell 47G-3B · Idyllwild, CAJun 25, 1970 · N1301X · Substantial damageNTSB ANC70DAG34Accident · Minor injuriesClosedCessna 170B · Kenai, AKJun 25, 1970 · N3108B · Substantial damageNTSB SEA70FDA15Accident · No injuriesClosedCessna 182 · Joliet, MTJun 24, 1970 · N5417B · Substantial damageNTSB FTW70FQG21Accident · Serious injuriesClosedCessna 177 · San Angelo, TXJun 24, 1970 · N3266T · DestroyedNTSB FTW70AF084Accident · 1 fatalClosedPiper PA-28 · Mooreland, OKJun 24, 1970 · N8674W · DestroyedNTSB CHI70FBD46Accident · Serious injuriesClosedGlobe GC-1B · Hinsdale, ILJun 24, 1970 · N2351B · Substantial damageNTSB MIA70OKA07Accident · No injuriesClosedCessna 185A · La Romana, DRJun 23, 1970 · N4062Y · Substantial damageNTSB FTW70FRA34Accident · No injuriesClosedCessna 150F · Madisonville, TXJun 23, 1970 · N7025F · Substantial damageNTSB CHI70FDM08Accident · Minor injuriesClosedAeronca 11CC · Cumberland, WIJun 23, 1970 · N4121E · Substantial damageNTSB CHI70AC093Accident · 2 fatalClosedPiper PA-12 · Somerset, KYJun 23, 1970 · N78443 · DestroyedNTSB CHI70AC092Accident · 2 fatalClosedN.American AT6-F · Ann Arbor, MIJun 23, 1970 · N445C · Destroyed