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 CAN71AP903Accident · Serious injuriesClosedTaylorcraft L2 · Williams Lake, CanadaAug 22, 1970 · N48448 · DestroyedNTSB SEA71FWA13Accident · Minor injuriesClosedPiper PA-28 · Glenwood, WAAug 21, 1970 · N95052 · DestroyedNTSB MKC71FCQ15Accident · No injuriesClosedCessna 401 · Baxter Sprgs, KSAug 21, 1970 · N8299F · Substantial damageNTSB NYC71FFA09Accident · Serious injuriesClosedBeech A23 · Johnstown, NYAug 20, 1970 · N1408L · Substantial damageNTSB CHI71FDW11Accident · Minor injuriesClosedPiper PA-22 · Deckerville, MIAug 20, 1970 · N3307Z · Substantial damageNTSB FTW71FRA14Accident · No injuriesClosedCessna 172 · Alvin, TXAug 19, 1970 · N7916B · Substantial damageNTSB MKC71FDD10Accident · Serious injuriesClosedAero Comdr 100 · Glenburn, NDAug 18, 1970 · N5531M · DestroyedNTSB CHI71DGD05Accident · No injuriesClosedN.American Navion · Cleveland, OHAug 18, 1970 · N91467 · Substantial damageNTSB SEA71FDA05Accident · Serious injuriesClosedStinson 108-2 · Red Lodge, MTAug 17, 1970 · N454C · DestroyedNTSB NYC71FGM09Accident · No injuriesClosedTaylor Titch · Finleyville, PAAug 17, 1970 · N5571 · Substantial damageNTSB CHI71FBJ19Accident · Minor injuriesClosedPiper PA-22 · Scotts, MIAug 17, 1970 · N6802B · Substantial damageNTSB CHI71DBA10Accident · No injuriesClosedAeronca 7EC · Odon, INAug 17, 1970 · N4309C · Substantial damageNTSB MKC71FCA11Accident · Serious injuriesClosedCessna 150H · Van Buren, MOAug 16, 1970 · N22124 · Substantial damageNTSB CHI71FBM03Accident · Serious injuriesClosedCessna T210 · Watervliet, MIAug 16, 1970 · N6147R · Substantial damageNTSB CHI71FBD14Accident · Minor injuriesClosedPiper J5 · Streator, ILAug 16, 1970 · N41180 · Substantial damageNTSB MKC71DDQ06Accident · No injuriesClosedCessna 170B · Wilmot, SDAug 15, 1970 · N4383B · Substantial damageNTSB MIA71DKD05Accident · No injuriesClosedAeronca 11AC · Arlington, TNAug 15, 1970 · N3889E · Substantial damageNTSB FTW71DPJ12Accident · No injuriesClosedGrumman G-164 · Boise City, OKAug 15, 1970 · N7716 · Substantial damageNTSB DEN71DXD20Accident · No injuriesClosedPiper PA-25 · Walsh, COAug 15, 1970 · N8737L · Substantial damageNTSB CHI71FBM05Accident · Serious injuriesClosedTaylorcraft 19 · Dowagiac, MIAug 15, 1970 · N43176 · Substantial damageNTSB ANC71DAG15Accident · No injuriesClosedCessna 150K · Anchorage, AKAug 15, 1970 · N5786G · DestroyedNTSB MIA71FLA09Accident · No injuriesClosedBell 47G-2 · Panama City, FLAug 14, 1970 · N5185B · Substantial damageNTSB IAD71DLJ09Accident · No injuriesClosedBell 47G-2 · Pactolus, NCAug 14, 1970 · N73264 · DestroyedNTSB CHI71FBQ10Accident · Minor injuriesClosedBeech B23 · Macomb, ILAug 14, 1970 · N4021T · Substantial damageNTSB NYC71FFD08Accident · No injuriesClosedAlon A2-A · Pepperell, MAAug 13, 1970 · N5477F · Substantial damageNTSB MIA71FLG04Accident · No injuriesClosedBeech 35 · Jackson, SCAug 13, 1970 · N3027V · Substantial damageNTSB MIA71FKQ07Accident · Minor injuriesClosedPiper PA-25 · Bolton, MSAug 13, 1970 · N4383Y · DestroyedNTSB CHI71DBQ07Accident · No injuriesClosedAero Comdr A-9 · Harrisburg, ILAug 13, 1970 · N8030V · Substantial damageNTSB OAK71AP008Accident · 2 fatalClosedChampion 7EC · Vacaville, CAAug 12, 1970 · N7466B · DestroyedNTSB FTW71DPD13Accident · No injuriesClosedCessna 177 · Iowa Park, TXAug 12, 1970 · N2963X · Substantial damageNTSB CHI71FBQ11Accident · Minor injuriesClosedPiper PA-18A · Gibson City, ILAug 12, 1970 · N1825A · Substantial damageNTSB ANC71AA012Accident · 1 fatalClosedPiper PA-18 · Seward, AKAug 11, 1970 · N3830Z · DestroyedNTSB OAK71FVG07Accident · Minor injuriesClosedPiper PA-24 · Redding, CAAug 10, 1970 · N9295P · Substantial damageNTSB MIA71FKG20Accident · Serious injuriesClosedWittman W-8 · La Grange, GAAug 10, 1970 · N1010B · DestroyedNTSB OAK71FVD04Accident · Serious injuriesClosedGlobe GC-1B · San Lorenzo, CAAug 9, 1970 · N2423B · Substantial damageNTSB ANC71DAG14Accident · Minor injuriesClosedPiper PA-12 · Chistochina, AKAug 9, 1970 · N3383M · Substantial damageNTSB SEA71FVQ05Accident · No injuriesClosedPiper PA-22 · Junction Cty, ORAug 8, 1970 · N5210Z · Substantial damageNTSB OAK71DVD03Accident · No injuriesClosedInterstate S-1B1 · Tracy, CAAug 8, 1970 · N49208 · Substantial damageNTSB MIA71DLD08Accident · No injuriesClosedCessna 172D · Bradenton, FLAug 8, 1970 · N2740U · Substantial damageNTSB FTW71DQG26Accident · No injuriesClosedGlobe GC-1B · Big Spring, TXAug 8, 1970 · N78118 · Substantial damageNTSB DEN71DXD19Accident · Minor injuriesClosedVolksplane WE-1 · Aurora, COAug 8, 1970 · N77PC · Substantial damageNTSB MIA71FKQ05Accident · No injuriesClosedPiper PA-25 · Quitman, MSAug 7, 1970 · N4462Y · DestroyedNTSB NYC71DFD05Accident · Minor injuriesClosedPiper J-3 · Eastham, MAAug 6, 1970 · N2108M · Substantial damageNTSB MKC71FCQ10Accident · Serious injuriesClosedSchultz BB · Harper, KSAug 6, 1970 · N2878 · Substantial damageNTSB MKC71DCG14Accident · No injuriesClosedCessna 210 · Protem, MOAug 6, 1970 · N3340S · Substantial damageNTSB CHI71FGA05Accident · No injuriesClosedCessna 150 · Oxford, OHAug 6, 1970 · N22980 · Substantial damageNTSB IAD71FIA02Accident · No injuriesClosedCessna 177 · Clinton, MDAug 4, 1970 · N2978X · Substantial damageNTSB FTW71FPJ09Accident · No injuriesClosedPiper PA-24 · Oklahoma Cty, OKAug 4, 1970 · N8840P · Substantial damageNTSB FTW71FPD09Accident · No injuriesClosedPop Rivet 1 · Newark, TXAug 4, 1970 · N4727 · DestroyedNTSB CHI71FBA03Accident · No injuriesClosedBeech 35 · Marion, INAug 4, 1970 · N444K · Substantial damage