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 IAD70DIG10Accident · No injuriesClosedCallair A-9 · Massey, MDMar 12, 1970 · N7220V · Substantial damageNTSB FTW70DPG07Accident · No injuriesClosedErco 415-C · West Point, ARMar 8, 1970 · N2152H · Substantial damageNTSB DEN70DXD11Accident · No injuriesClosedAmer Avco AA-1 · Aurora, COMar 8, 1970 · N5950L · Substantial damageNTSB CHI70FBQ08Accident · No injuriesClosedPiper PA-28 · Paris, ILMar 8, 1970 · N8199N · Substantial damageNTSB OAK70DVM06Accident · No injuriesClosedPiper J3C-65 · Half Mn Bay, CAMar 7, 1970 · N40855 · Substantial damageNTSB MKC70FCG12Accident · Minor injuriesClosedTaylorcraft BL-65 · Springfield, MOMar 7, 1970 · N27668 · Substantial damageNTSB MKC70FCG11Accident · Minor injuriesClosedAeronca 7AC · Smithville, MOMar 7, 1970 · N84825 · DestroyedNTSB CHI70AC060Accident · 2 fatalClosedAeronca 7AC · Columbus, OHMar 7, 1970 · N1178E · DestroyedNTSB LAX70AL053Accident · 2 fatalClosedPiper PA-23 · Pacoima, CAMar 5, 1970 · N3496P · DestroyedNTSB LAX70FUQ16Accident · Minor injuriesClosedBeech P35 · Goleta, CAMar 4, 1970 · N1578S · Substantial damageNTSB MIA70DLD07Accident · Minor injuriesClosedCessna 120 · Brooksville, FLMar 3, 1970 · N1802N · Substantial damageNTSB IAD70FLJ01Accident · 1 fatalClosedPiper PA-22 · Orrum, NCMar 3, 1970 · N7120B · Substantial damageNTSB FTW70DRG16Accident · No injuriesClosedCessna 310L · Matamoros, MexicoMar 1, 1970 · N3291X · Substantial damageNTSB MIA70AM073Accident · Minor injuriesClosedGrumman G-21A · St Thomas, VIFeb 28, 1970 · N40R · Substantial damageNTSB CHI70AC057Accident · 2 fatalClosedPiper PA-28 · Kokomo, INFeb 26, 1970 · N7326J · DestroyedNTSB LAX70DUJ15Accident · Minor injuriesClosedCessna 172E · Mormon Lake, AZFeb 23, 1970 · N3888S · Substantial damageNTSB OAK70DVW03Accident · No injuriesClosedBeech BE23 · Mesquite, NVFeb 22, 1970 · N5974S · Substantial damageNTSB LAX70FUG10Accident · Minor injuriesClosedCessna 172 · Rubidoux, CAFeb 22, 1970 · N1212K · Substantial damageNTSB MIA70AM072Accident · 1 fatalClosedAeronca 7BCM · Augusta, GAFeb 21, 1970 · N2551B · DestroyedNTSB FTW70DRG12Accident · No injuriesClosedCessna 177 · Randolph Afb, TXFeb 21, 1970 · N3255T · DestroyedNTSB MKC70DCQ08Accident · No injuriesClosedCessna 401A · Independence, KSFeb 19, 1970 · N3119K · Substantial damageNTSB LAX70FUG09Accident · No injuriesClosedPiper PA-28R · Big Bear City, CAFeb 18, 1970 · N7471J · DestroyedNTSB FTW70FRD01Accident · Minor injuriesClosedBell 47G-4A · Morgan City, LAFeb 17, 1970 · N6230N · Substantial damageNTSB DEN70FXA05Accident · Serious injuriesClosedPiper PA-22 · Saratoga, WYFeb 16, 1970 · N3320A · Substantial damageNTSB CHI70FGD06Accident · Minor injuriesClosedPiper PA-22 · Painesville, OHFeb 16, 1970 · N6843B · DestroyedNTSB CHI70DBD18Accident · No injuriesClosedLil Honey H0-1 · Monee, ILFeb 14, 1970 · N4943E · Substantial damageNTSB NYC70AN089Accident · 1 fatalClosedCessna 182 · Sterling, MAFeb 12, 1970 · N3339Y · DestroyedNTSB LAX70FUQ09Accident · Minor injuriesClosedBell 47D1 · Van Nuys, CAFeb 12, 1970 · N3088G · Substantial damageNTSB OAK70DXQ03Accident · No injuriesClosedRepublic RC-3 · Homewood, CAFeb 11, 1970 · N6652K · Substantial damageNTSB DEN70FXT05Accident · No injuriesClosedCessna 320 · Ephraim, UTFeb 10, 1970 · N5719X · Substantial damageNTSB SEA70DWA04Accident · No injuriesClosedCessna 182 · Hillsboro, ORFeb 8, 1970 · N3414R · Substantial damageNTSB MKC70FCJ07Accident · No injuriesClosedPiper PA-28 · Gothenburg, NEFeb 8, 1970 · N5521U · Substantial damageNTSB NYC70FHM06Accident · Minor injuriesClosedCessna 172D · Spring Valley, NYFeb 7, 1970 · N2465Y · Substantial damageNTSB MKC70FDM08Accident · No injuriesClosedCessna 150G · Crystal, MNFeb 6, 1970 · N8680J · Substantial damageNTSB NYC70FFM04Accident · No injuriesClosedAeronca 7AC · Turner Falls, MAFeb 1, 1970 · N85191 · Substantial damageNTSB ANC70DAG04Accident · No injuriesClosedCessna 180 · Glennallen, AKFeb 1, 1970 · N2757X · Substantial damageNTSB MKC70FDM07Accident · No injuriesClosedCessna 140 · Onamia, MNJan 31, 1970 · N4243N · Substantial damageNTSB MKC70DDM05Accident · No injuriesClosedCessna 180 · Minneapolis, MNJan 31, 1970 · N9310C · Substantial damageNTSB MIA70DLG01Accident · Minor injuriesClosedJay Tee RCB-1 · Johns Island, SCJan 31, 1970 · N8142 · Substantial damageNTSB DEN70FXD08Accident · No injuriesClosedAero Comdr 520 · Broomfield, COJan 30, 1970 · N2634B · Substantial damageNTSB MKC70DDQ04Accident · Minor injuriesClosedPiper PA-24 · Onida, SDJan 29, 1970 · N6616P · Substantial damageNTSB FTW70DRG06Accident · No injuriesClosedCessna 182D · Mcallen, TXJan 28, 1970 · N9119X · Substantial damageNTSB MKC70FCJ03Accident · Minor injuriesClosedPiper PA-22 · Mccool Jct, NEJan 26, 1970 · N3453A · DestroyedNTSB MKC70DDQ12Accident · No injuriesClosedPiper PA-18 · Altamont, SDJan 25, 1970 · N1256A · Substantial damageNTSB OAK70DJA04Accident · No injuriesClosedPiper PA-30 · S.Pole Station, AYJan 24, 1970 · N123LF · Substantial damageNTSB MIA70FKG04Accident · No injuriesClosedCessna 177 · Villa Rica, GAJan 24, 1970 · N2312Y · Substantial damageNTSB MIA70DKA01Accident · Serious injuriesClosedCessna 172 · Guayma, PRJan 24, 1970 · N3218L · Substantial damageNTSB NYC70FFD07Accident · Minor injuriesClosedPiper PA-23 · Mansfield, MAJan 22, 1970 · N4498P · DestroyedNTSB NYC70FHJ01Accident · Minor injuriesClosedCessna 172 · Farmingdale, NYJan 20, 1970 · N6696A · Substantial damageNTSB CHI70FBG02Accident · No injuriesClosedHughes 269B · Farmington, MIJan 19, 1970 · N9405F · Destroyed