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 DEN70DXD34Accident · Minor injuriesClosedPiper PA-28 · Kim, COMay 24, 1970 · N7859N · Substantial damageNTSB DEN70FQD15Accident · No injuriesClosedBellanca 14-19 · Anthony, NMMay 23, 1970 · N8822R · Substantial damageNTSB CHI70FBD36Accident · Serious injuriesClosedN.American SNJ-4 · Addison, ILMay 23, 1970 · N173V · DestroyedNTSB CHI70FBA19Accident · Serious injuriesClosedBeech J35 · Greenwood, INMay 23, 1970 · N8340D · DestroyedNTSB CHI70DBJ26Accident · No injuriesClosedTaylorcraft BC1285 · Slt St Marie, MIMay 23, 1970 · N36241 · Substantial damageNTSB MKC70FDD15Accident · No injuriesClosedCallair B-1A · Grand Forks, NDMay 22, 1970 · N7200V · DestroyedNTSB LAX70FUQ29Accident · Minor injuriesClosedStinson 108 · Santa Paula, CAMay 21, 1970 · N97292 · Substantial damageNTSB OAK70AP059Accident · 2 fatalClosedBeech B95 · Sacramento, CAMay 20, 1970 · N2063C · DestroyedNTSB NYC70AN120Accident · 1 fatalClosedTaylorcraft BC12-D · Vineland, NJMay 20, 1970 · N36104 · DestroyedNTSB MKC70DDD14Accident · Minor injuriesClosedPiper PA-25 · Walhalla, NDMay 20, 1970 · N7193Z · DestroyedNTSB MKC70DCQ22Accident · No injuriesClosedPiper PA-22 · Bird City, KSMay 20, 1970 · N9331D · Substantial damageNTSB MIA70FKG32Accident · Minor injuriesClosedAmer Avco AA-1 · Dallas, GAMay 20, 1970 · N5912L · DestroyedNTSB DEN70AD056Accident · 2 fatalClosedPiper PA-28R · Boulder, COMay 20, 1970 · N4555J · DestroyedNTSB CHI70FDW14Accident · No injuriesClosedStinson 108-3 · Flushing, MIMay 20, 1970 · N712C · Substantial damageNTSB MKC70FCA11Accident · No injuriesClosedBeech 95-C55 · Berkeley, MOMay 19, 1970 · N2764T · Substantial damageNTSB FTW70FQA21Accident · No injuriesClosedPiper PA-28R · Raton, NMMay 18, 1970 · N3894T · Substantial damageNTSB LAX70FUQ33Accident · No injuriesClosedCessna 172 · Santa Barbara, CAMay 17, 1970 · N5060A · Substantial damageNTSB FTW70FPJ19Accident · Serious injuriesClosedCessna 170A · Midwest City, OKMay 17, 1970 · N9572A · Substantial damageNTSB CHI70FDW13Accident · Minor injuriesClosedCessna 140 · Port Huron, MIMay 17, 1970 · N2060N · Substantial damageNTSB NYC70FFD15Accident · No injuriesClosedCessna 172F · Beverly, MAMay 16, 1970 · N8310U · Substantial damageNTSB MKC70DCJ22Accident · No injuriesClosedAeronca 65-TC · Fairmont, NEMay 16, 1970 · N29182 · Substantial damageNTSB MKC70AK061Accident · 1 fatalClosedCessna 140 · Waldron, MOMay 16, 1970 · N89874 · DestroyedNTSB FTW70FPM07Accident · No injuriesClosedAeronca 11AC · Grove, OKMay 16, 1970 · N85928 · Substantial damageNTSB FTW70FPJ18Accident · Serious injuriesClosedRyan ST-3KR · Overbrook, OKMay 16, 1970 · N1344 · Substantial damageNTSB OAK70AP057Accident · No injuriesClosedCessna 206 · San Frncsco, CAMay 15, 1970 · N29096 · Substantial damageNTSB OAK70DVG23Accident · No injuriesClosedPiper PA-32 · Calistoga, CAMay 14, 1970 · N4054R · Substantial damageNTSB MIA70FKQ08Accident · No injuriesClosedCessna 188 · Shelby, MSMay 13, 1970 · N3575Q · DestroyedNTSB FTW70FRA17Accident · No injuriesClosedGrumman G-164 · Devers, TXMay 13, 1970 · N10262 · DestroyedNTSB NYC71FHA38Accident · Minor injuriesClosedCessna 177 · Basking Ridge, NJMay 12, 1970 · N2984X · DestroyedNTSB OAK70FXQ11Accident · No injuriesClosedPiper PA-28R · Bridgeport, CAMay 9, 1970 · N4990J · Substantial damageNTSB CHI70FBD33Accident · Minor injuriesClosedHughes 269A · West Chicago, ILMay 9, 1970 · N8790F · Substantial damageNTSB CHI70AC078Accident · 2 fatalClosedCessna 177A · Lexington, OHMay 9, 1970 · N30505 · DestroyedNTSB FTW70FQG13Accident · Serious injuriesClosedPiper PA-28 · Slaton, TXMay 8, 1970 · N8276N · Substantial damageNTSB MIA70AM091Accident · 2 fatalClosedNavion A · Dalton, GAMay 7, 1970 · N4510K · DestroyedNTSB LAX70FUG31Accident · No injuriesClosedHughes 269A · Arlington, CAMay 5, 1970 · N8709F · Substantial damageNTSB FTW70FPG25Accident · No injuriesClosedGrumman G-164A · Amagon, ARMay 4, 1970 · N5309 · Substantial damageNTSB FTW70DRJ09Accident · No injuriesClosedPiper PA-28 · Lufkin, TXMay 4, 1970 · N6933W · Substantial damageNTSB UNK70X0111Accident · Serious injuriesClosedCessna 150 · Gelnhausn, GeorgiaMay 3, 1970 · N8744G · DestroyedNTSB MKC70DCQ19Accident · No injuriesClosedPiper PA-28 · Hill City, KSMay 3, 1970 · N9312W · Substantial damageNTSB MKC70DCJ21Accident · No injuriesClosedPiper PA-25 · Snyder, NEMay 2, 1970 · N6455Z · Substantial damageNTSB NYC70FGM15Accident · Minor injuriesClosedCessna 150E · Punxsutawney, PAApr 30, 1970 · N6214R · Substantial damageNTSB CHI70DBD26Accident · No injuriesClosedCessna 150G · Naperville, ILApr 29, 1970 · N3045S · Substantial damageNTSB MKC70DCJ19Accident · No injuriesClosedPiper PA-28 · Gothenburg, NEApr 28, 1970 · N6189W · Substantial damageNTSB MKC70FDM22Accident · No injuriesClosedRhein RW3B · Minneapolis, MNApr 27, 1970 · N111ZZ · Substantial damageNTSB MKC70DGD40Accident · No injuriesClosedCessna 150A · Maysville, IAApr 26, 1970 · N4738X · Substantial damageNTSB CHI70FBJ22Accident · Minor injuriesClosedPiper PA-22 · Orangeville, MIApr 26, 1970 · N3338Z · DestroyedNTSB NYC70FFA18Accident · No injuriesClosedCessna 172 · Schroon Lake, NYApr 25, 1970 · N6234E · Substantial damageNTSB LAX70FUG27Accident · Minor injuriesClosedCessna 140 · Rialto, CAApr 25, 1970 · N76317 · Substantial damageNTSB FTW70FPG19Accident · Minor injuriesClosedCessna 177 · Little Rock, ARApr 24, 1970 · N2241Y · DestroyedNTSB NYC70AN115Accident · No injuriesClosedBell 47J-2 · Piscataway, NJApr 22, 1970 · N73949 · Substantial damage