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 SEA71FWG46Accident · Minor injuriesClosedPiper PA-28 · Creston, WAApr 14, 1971 · N6931W · Substantial damageNTSB NYC71IN102Incident · No injuriesClosedBoeing 727 · Jamaica, NYApr 14, 1971 · N4733 · No damageNTSB DEN71DXA22Accident · No injuriesClosedPiper PA-25 · Casper, WYApr 14, 1971 · N7395Z · Substantial damageNTSB MKC71FCG56Accident · Serious injuriesClosedCessna 150G · Albany, MOApr 12, 1971 · N8315J · DestroyedNTSB LAX71FUJ85Accident · Minor injuriesClosedCessna U206D · Phoenix, AZApr 12, 1971 · N72045 · Substantial damageNTSB DEN71DXA21Accident · No injuriesClosedPiper PA-18 · Seely, WYApr 12, 1971 · N1979P · DestroyedNTSB OAK71DVG91Accident · No injuriesClosedCessna 150 · Wilseyville, CAApr 11, 1971 · N3202X · Substantial damageNTSB MKC71FDM55Accident · No injuriesClosedPiper PA-28 · Oklee, MNApr 11, 1971 · N3284R · Substantial damageNTSB MIA71FLA41Accident · Serious injuriesClosedChampion 7ECA · Bunnell, FLApr 11, 1971 · N7581F · Substantial damageNTSB SEA71FTA06Accident · No injuriesClosedTaylorcraft BC12-D · Baker, MTApr 10, 1971 · N94966 · Substantial damageNTSB MIA71FLA42Accident · No injuriesClosedCessna 150 · Macclenny, FLApr 10, 1971 · N5901G · Substantial damageNTSB OAK71FXQ35Accident · No injuriesClosedCessna 180 · Reno, NVApr 9, 1971 · N4974A · Substantial damageNTSB NYC71FGT13Accident · Minor injuriesClosedStinson 10-A · Hallam, PAApr 9, 1971 · N32203 · Substantial damageNTSB IAD71FIG27Accident · Minor injuriesClosedPiper J3L-65 · Edgewater, MDApr 9, 1971 · N23Q · Substantial damageNTSB FTW71FPG47Accident · Minor injuriesClosedPiper J3C-65 · Tamo, ARApr 9, 1971 · N3538K · Substantial damageNTSB MKC71FCJ59Accident · Minor injuriesClosedCallair A9 · Gibbon, NEApr 8, 1971 · N3606G · Substantial damageNTSB MKC71DCG53Accident · Minor injuriesClosedCessna 150 · Pt Lookout, MOApr 8, 1971 · N7702F · Substantial damageNTSB NYC71IN099Incident · No injuriesClosedBoeing 707 · Jamaica, NYApr 7, 1971 · VT-DPM · No damageNTSB MIA71FLG27Accident · No injuriesClosedPiper J3C-65 · Pageland, SCApr 7, 1971 · N92308 · Substantial damageNTSB OAK71DXQ33Accident · No injuriesClosedBeech 35 · Gerlach, NVApr 4, 1971 · N694B · Substantial damageNTSB LAX71FUG68Accident · Minor injuriesClosedBriegleb BG12BD · Perris, CAApr 4, 1971 · N4025 · Substantial damageNTSB ANC71AA050Accident · 1 fatalClosedPiper PA-22 · Fairbanks, AKApr 4, 1971 · N4951Z · Substantial damageNTSB SEA71FWA32Accident · Serious injuriesClosedStarduster Stolp · Vancouver, WAApr 3, 1971 · N2502 · DestroyedNTSB MKC71FCJ57Accident · Minor injuriesClosedBowers 1A375G · Norfolk, NEApr 3, 1971 · N24080 · Substantial damageNTSB IAD71FIG29Accident · No injuriesClosedPiper PA-28 · White Marsh, MDApr 3, 1971 · N6692J · Substantial damageNTSB MIA71DKQ32Accident · No injuriesClosedPiper PA-18A · Quitman, MSMar 31, 1971 · N9088D · Substantial damageNTSB SEA71DWG41Accident · No injuriesClosedPiper PA-22 · Dishman, WAMar 30, 1971 · N2709P · Substantial damageNTSB CHI71FLQ21Accident · No injuriesClosedBede BD-4 · Louisville, KYMar 30, 1971 · N400BD · Substantial damageNTSB MKC71FCG51Accident · No injuriesClosedCessna 182L · Kansas City, KSMar 29, 1971 · N3418R · Substantial damageNTSB MKC71DDQ26Accident · No injuriesClosedCessna 170 · Wsngtn Spgs, SDMar 29, 1971 · N2419D · Substantial damageNTSB LAX72DUJ13Accident · No injuriesClosedBeech 35 · Coolidge, AZMar 29, 1971 · N80443 · Substantial damageNTSB NYC71FGM35Accident · Minor injuriesClosedPiper PA-22 · Hawthorne, PAMar 28, 1971 · N6912D · Substantial damageNTSB LAX71DUG69Accident · No injuriesClosedCessna 177 · Heme, CAMar 28, 1971 · N29408 · Substantial damageNTSB FTW71FPJ36Accident · No injuriesClosedCallair A-4 · Terral, OKMar 28, 1971 · N7579C · Substantial damageNTSB CHI71FBD59Accident · No injuriesClosedCessna 150 · Naperville, ILMar 28, 1971 · N4611X · Substantial damageNTSB SEA71DXM28Accident · No injuriesClosedCessna 150 · Burley, IDMar 27, 1971 · N7368X · Substantial damageNTSB ANC71DAG81Accident · No injuriesClosedPiper PA-22 · Port Alswrth, AKMar 27, 1971 · N8520D · Substantial damageNTSB ANC71DAG78Accident · No injuriesClosedMaule M-4 · Glenallen, AKMar 27, 1971 · N9825M · Substantial damageNTSB CHI71DBA33Accident · No injuriesClosedPiper J3 · Mechanicsburg, INMar 26, 1971 · N21811 · Substantial damageNTSB FTW71FPG43Accident · 2 fatalClosedN.American P-51D · Hot Springs, ARMar 24, 1971 · N6302T · DestroyedNTSB CHI71FDJ46Accident · Minor injuriesClosedCessna 150F · Racine, WIMar 23, 1971 · N8927S · Substantial damageNTSB MIA71DKJ44Accident · Minor injuriesClosedCessna 182 · Toney, ALMar 20, 1971 · N2870F · Substantial damageNTSB NYC71IN092Incident · No injuriesClosedDouglas DC9-10 · Newark, NJMar 19, 1971 · N1066T · No damageNTSB CHI71FBM18Accident · No injuriesClosedPiper PA-28 · Kendallville, INMar 14, 1971 · N5658U · Substantial damageNTSB ANC71AA046Accident · No injuriesClosedCessna 180H · Bethel, AKMar 14, 1971 · N9929V · Substantial damageNTSB IAD71II041Incident · No injuriesClosedBoeing 727 · Washington, DCMar 13, 1971 · N491US · No damageNTSB FTW71FRG94Accident · Serious injuriesClosedCessna 172K · Killeen, TXMar 13, 1971 · N46446 · Substantial damageNTSB FTW71DPG41Accident · No injuriesClosedGrumman G-164A · Earl, ARMar 13, 1971 · N5352 · Substantial damageNTSB SEA71AS039Accident · 2 fatalClosedAeronca 7FC · Snohomish, WAMar 12, 1971 · N7508E · DestroyedNTSB SEA71AS038Accident · Minor injuriesClosedCessna 182F · Okanogan, WAMar 12, 1971 · N3338U · Destroyed