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 LAX72DUJ70Accident · No injuriesClosedCessna 150 · Tuba City, AZJan 22, 1972 · N6604S · Substantial damageNTSB CHI72FEE56Accident · Minor injuriesClosedCessna P206 · Chicago, ILJan 21, 1972 · N2568X · Substantial damageNTSB MIA72FLA29Accident · Serious injuriesClosedBeech D18S · Bunnell, FLJan 20, 1972 · N80367 · Substantial damageNTSB CHI72FEP30Accident · 1 fatalClosedCessna 337 · Woodruff, WIJan 20, 1972 · N2140 · DestroyedNTSB MKC72FTG16Accident · No injuriesClosedPiper J-3 · Crosby, NDJan 17, 1972 · N24705 · Substantial damageNTSB DEN72DTC15Accident · No injuriesClosedPiper PA-18 · Dayton, WYJan 17, 1972 · N3989P · Substantial damageNTSB NYC72AN089Accident · 1 fatalClosedAmer Avco AA1 · Plattsburgh, NYJan 16, 1972 · N6167L · DestroyedNTSB MKC72DTK20Accident · No injuriesClosedPiper J-3 · Watertown, SDJan 16, 1972 · N88653 · Substantial damageNTSB LAX72FUQ55Accident · Serious injuriesClosedBeech F35 · Simi, CAJan 16, 1972 · N3814B · DestroyedNTSB NYC72FLJ20Accident · Minor injuriesClosedEnstrom F-28A · Hillsborough, NCJan 9, 1972 · N426RD · Substantial damageNTSB MIA72FKG65Accident · Serious injuriesClosedCessna 150 · Fayetteville, GAJan 9, 1972 · N704X · Substantial damageNTSB MIA72AM066Accident · 2 fatalClosedPiper PA-23 · Ocoee, FLJan 8, 1972 · N2220P · DestroyedNTSB LAX72AL044Accident · 1 fatalClosedSchweizer SGU222 · Elsinore, CAJan 8, 1972 · N8691R · DestroyedNTSB CHI72DEE52Accident · No injuriesClosedCessna 172L · Lockport, ILJan 8, 1972 · N7885G · Substantial damageNTSB MKC72DTK18Accident · No injuriesClosedPiper J-3 · Arlington, SDJan 7, 1972 · N35124 · Substantial damageNTSB MIA72FKQ28Accident · Serious injuriesClosedCessna 188B · Columbus, MSJan 7, 1972 · PT-DZL · DestroyedNTSB NYC72FGT07Accident · No injuriesClosedCessna 150F · Elysburg, PAJan 6, 1972 · N6869F · Substantial damageNTSB NYC72FFJ24Accident · No injuriesClosedCessna 150J · Buffalo, NYJan 6, 1972 · N60658 · Substantial damageNTSB LAX72DVM19Accident · Minor injuriesClosedSnow 600S2R · San Miguel, CAJan 5, 1972 · N1724S · Substantial damageNTSB CHI72FEC23Accident · No injuriesClosedCessna 172E · Lake Orion, MIJan 5, 1972 · N5611T · Substantial damageNTSB NYC72FNA24Accident · Serious injuriesClosedAmer Avco AA5 · Tewksbury, MAJan 2, 1972 · N5803L · DestroyedNTSB MIA72FKW12Accident · Serious injuriesClosedCessna 140 · Knoxville, TNDec 31, 1971 · N76180 · DestroyedNTSB ANC72AA033Accident · Minor injuriesClosedHelio H-250 · Lttl Diomede, AKDec 30, 1971 · N8868E · Substantial damageNTSB FTW72FRA27Accident · No injuriesClosedCessna 310 · Tomball, TXDec 27, 1971 · N5307A · Substantial damageNTSB FTW72DPG58Accident · No injuriesClosedCessna 150 · Heth, ARDec 27, 1971 · N61242 · Substantial damageNTSB MIA72FLA23Accident · Minor injuriesClosedCessna 177 · Carrabelle, FLDec 24, 1971 · N2393Y · Substantial damageNTSB MIA72FKT36Accident · No injuriesClosedCallair A-9B · W Palm Beach, FLDec 24, 1971 · N7992V · Substantial damageNTSB MIA72FKQ27Accident · No injuriesClosedHiller Acft FH1100 · Hattiesburg, MSDec 22, 1971 · N5035F · Substantial damageNTSB FTW72AF039Accident · 1 fatalClosedCessna 172 · Seguin, TXDec 22, 1971 · N3941L · DestroyedNTSB OAK72FVA38Accident · No injuriesClosedCessna 150 · Selma, CADec 21, 1971 · N6684G · Substantial damageNTSB ANC72FAG82Accident · No injuriesClosedCessna 180 · Northway, AKDec 19, 1971 · N6538A · Substantial damageNTSB CHI72AC052Accident · 1 fatalClosedPiper PA-22 · Evansville, INDec 16, 1971 · N8641C · DestroyedNTSB SEA72DYG21Accident · Minor injuriesClosedBeech 23 · Pullman, WADec 11, 1971 · N7608R · Substantial damageNTSB NYC72FGM24Accident · No injuriesClosedCessna 182K · Sharon, PADec 11, 1971 · N2742Q · Substantial damageNTSB FTW72AF033Accident · 7 fatalClosedBeech 65-80 · Okla.City, OKDec 9, 1971 · N252Q · DestroyedNTSB NYC72FNC16Accident · No injuriesClosedPiper J3C-65 · East Dixfield, MEDec 5, 1971 · N98462 · Substantial damageNTSB UNK72X0101Accident · Minor injuriesClosedLake LA-4 · Seoul, KSDec 2, 1971 · N7612L · DestroyedNTSB FTW72FPM16Accident · No injuriesClosedLuscombe 8A · Cleveland, OKNov 30, 1971 · N1561K · Substantial damageNTSB MIA72FKQ26Accident · No injuriesClosedCessna 175 · Duck Hill, MSNov 28, 1971 · N9259B · Substantial damageNTSB ANC72DAG77Accident · No injuriesClosedPiper J5A · Anchorage, AKNov 27, 1971 · N35408 · Substantial damageNTSB CHI72FEG24Accident · No injuriesClosedPiper PA-23 · Lebanon, OHNov 26, 1971 · N4175P · Substantial damageNTSB NYC72FHD22Accident · Minor injuriesClosedMooney M20F · Vineland, NJNov 24, 1971 · N9652M · Substantial damageNTSB CHI72FEI23Accident · No injuriesClosedHelio Acft H-295 · Medina, OHNov 24, 1971 · N6469V · Substantial damageNTSB ANC72AA023Accident · No injuriesClosedPiper PA-28 · Iliamna, AKNov 24, 1971 · N7311W · Substantial damageNTSB NYC72FHD21Accident · No injuriesClosedCessna 182J · Williamstown, NJNov 21, 1971 · N2733F · Substantial damageNTSB MKC72FER30Accident · Serious injuriesClosedPiper PA-18 · Gilman, MNNov 21, 1971 · N4316Z · Substantial damageNTSB ANC72AA022Accident · 4 fatalClosedCessna 170B · Kotzebue, AKNov 21, 1971 · N3455D · DestroyedNTSB OAK72DVA47Accident · No injuriesClosedErco 415-C · Merced, CANov 20, 1971 · N2601H · Substantial damageNTSB LAX72DUJ58Accident · Minor injuriesClosedPiper J3C-65 · Pearce, AZNov 20, 1971 · N70524 · DestroyedNTSB DEN72DTE44Accident · Minor injuriesClosedCessna 172 · Fountain, CONov 20, 1971 · N4339Q · Substantial damage