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 DEN71AD031Accident · 4 fatalClosedBeech G35 · Aspen, COJan 10, 1971 · N4441D · DestroyedNTSB SEA71DXM19Accident · No injuriesClosedPiper PA-22 · Middleton, IDJan 9, 1971 · N629A · Substantial damageNTSB NYC71FFD32Accident · No injuriesClosedCessna 150K · Marlboro, MAJan 9, 1971 · N6346G · Substantial damageNTSB CHI71FBA23Accident · No injuriesClosedCessna 120 · Monticello, INJan 9, 1971 · N4236N · Substantial damageNTSB CHI71DBA24Accident · No injuriesClosedCessna 150H · Richmond, INJan 9, 1971 · N22701 · Substantial damageNTSB NYC71DFD30Accident · No injuriesClosedPiper PA-28 · Plymouth, MAJan 6, 1971 · N7249F · Substantial damageNTSB MKC71FDM42Accident · No injuriesClosedCessna 150J · Lake Elmo, MNJan 6, 1971 · N3492J · Substantial damageNTSB DEN71FXD44Accident · Minor injuriesClosedPiper PA-32 · Littleton, COJan 4, 1971 · N3935W · Substantial damageNTSB SEA71FWD26Accident · Minor injuriesClosedAero Comdr 100 · Vashon Isl, WAJan 3, 1971 · N3768X · Substantial damageNTSB MIA71FLG21Accident · Minor injuriesClosedPiper PA-22 · Calhoun Falls, SCJan 3, 1971 · N802A · Substantial damageNTSB MIA71DLD41Accident · No injuriesClosedCessna 177 · Minneola, FLJan 3, 1971 · N2381Y · Substantial damageNTSB SEA71FWA26Accident · Minor injuriesClosedCessna 172 · Oregon City, ORJan 1, 1971 · N8479U · DestroyedNTSB OAK71AP030Accident · 3 fatalClosedPiper PA-30 · H.Moon Bay, CADec 31, 1970 · N7667Y · DestroyedNTSB NYC71DFM25Accident · Minor injuriesClosedLuscombe 11A · G.Barrington, MADec 31, 1970 · N1686B · Substantial damageNTSB FTW71FPJ24Accident · No injuriesClosedCessna 150 · Wellston, OKDec 30, 1970 · N8449J · DestroyedNTSB CHI71FGD33Accident · Minor injuriesClosedCessna 150H · Akron, OHDec 29, 1970 · N6493S · Substantial damageNTSB NYC71FFG09Accident · No injuriesClosedCessna 185 · Portland, MEDec 27, 1970 · N4710 · Substantial damageNTSB CHI71FBD47Accident · Serious injuriesClosedPiper PA-28 · Schaumburg, ILDec 26, 1970 · N9049J · DestroyedNTSB NYC71FFD25Accident · No injuriesClosedCessna 180 · Bedford, MADec 20, 1970 · N3370D · Substantial damageNTSB MIA71DKT29Accident · No injuriesClosedCessna 177 · Ochopee, FLDec 20, 1970 · N2921X · Substantial damageNTSB NYC71FFM24Accident · No injuriesClosedCessna 150D · Goshen, CTDec 19, 1970 · N4569U · Substantial damageNTSB MIA71FKG54Accident · Serious injuriesClosedGlobe GC-1B · Stone Mountain, GADec 19, 1970 · N78146 · Substantial damageNTSB FTW71FPA18Accident · No injuriesClosedErco 415-C · Plano, TXDec 19, 1970 · N2890H · Substantial damageNTSB ANC71DAG60Accident · No injuriesClosedPiper PA-22 · Soldotna, AKDec 19, 1970 · N4444A · Substantial damageNTSB NYC71IN070Incident · No injuriesClosedBoeing 747 · Jamaica, NYDec 18, 1970 · N740PA · Minor damageNTSB NYC71FHJ17Accident · Minor injuriesClosedPiper PA-28 · Flushing, NYDec 18, 1970 · N7192R · Substantial damageNTSB CHI71FBJ47Accident · No injuriesClosedPiper PA-28 · Greenville, MIDec 15, 1970 · N418HP · Substantial damageNTSB MKC71DCD44Accident · No injuriesClosedCessna 150E · Johnston, IADec 13, 1970 · N6230R · Substantial damageNTSB LAX71FUA33Accident · Minor injuriesClosedStinson 108-3 · Torrance, CADec 13, 1970 · N6927M · Substantial damageNTSB SEA71FWD23Accident · Serious injuriesClosedCessna 177 · Eatonville, WADec 12, 1970 · N2382Y · DestroyedNTSB CHI71FDJ32Accident · Serious injuriesClosedCessna 150 · Strum, WIDec 12, 1970 · N6770G · DestroyedNTSB SEA71FXM17Accident · No injuriesClosedBeech G35 · Caldwell, IDDec 11, 1970 · N4469D · Substantial damageNTSB MKC71FDM35Accident · Minor injuriesClosedAero Comdr 100 · Lake Elmo, MNDec 6, 1970 · N4050X · DestroyedNTSB OAK71DVM30Accident · No injuriesClosedCessna 150 · Palo Alto, CADec 5, 1970 · N4254U · Substantial damageNTSB NYC71IN104Incident · No injuriesClosedBoeing 707 · Atlantic City, NJDec 5, 1970 · N460PA · Minor damageNTSB DEN71DQA36Accident · No injuriesClosedFleet Acft 7 · Cerillos, NMDec 3, 1970 · N440K · Substantial damageNTSB LAX71AL033Accident · 1 fatalClosedBell 47G3B1 · Rim Forest, CADec 1, 1970 · N1126W · DestroyedNTSB FTW71FRA32Accident · No injuriesClosedSnow 600S2D · Katy, TXDec 1, 1970 · N344AA · Substantial damageNTSB NYC71FFM22Accident · 1 fatalClosedJeanies Teenie · Stratford, CTNov 29, 1970 · N2288 · DestroyedNTSB NYC71DFJ17Accident · No injuriesClosedMooney M18LA · Kirkville, NYNov 29, 1970 · N4105 · Substantial damageNTSB MKC71DCD36Accident · Minor injuriesClosedPiper PA-28 · Denison, IANov 29, 1970 · N8299N · Substantial damageNTSB ANC71DAG57Accident · No injuriesClosedPiper PA-11 · Skwentna, AKNov 28, 1970 · N4931M · Substantial damageNTSB FTW71FRD27Accident · No injuriesClosedCessna 185D · Lafayette, LANov 27, 1970 · N1512F · Substantial damageNTSB MKC71FCG34Accident · No injuriesClosedCessna 310 · Lawrence, KSNov 25, 1970 · N790S · Substantial damageNTSB CHI71FGG20Accident · No injuriesClosedBeech A23A · Hicksville, OHNov 25, 1970 · N4760J · Substantial damageNTSB FTW71FQG45Accident · No injuriesClosedBoeing A75N1 · Tahoka, TXNov 24, 1970 · N75262 · DestroyedNTSB SEA71FVQ15Accident · Serious injuriesClosedPiper PA-28R · Bend, ORNov 22, 1970 · N3729T · DestroyedNTSB NYC71FGM22Accident · No injuriesClosedStits SA6B · Finleyville, PANov 22, 1970 · N1292V · Substantial damageNTSB NYC71DHJ13Accident · No injuriesClosedCessna 172 · Flushing, NYNov 22, 1970 · N1191M · Substantial damageNTSB LAX71FUQ37Accident · No injuriesClosedCessna 150G · Van Nuys, CANov 22, 1970 · N4076J · Substantial damage