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 NYC72FHD10Accident · Minor injuriesClosedPiper PA-28 · Berlin, NJSep 3, 1971 · N8841J · Substantial damageNTSB MIA72FKQ19Accident · Minor injuriesClosedCessna 177 · Vicksburg, MSSep 3, 1971 · N30344 · Substantial damageNTSB LAX72FUG11Accident · Serious injuriesClosedAeronca 7AC · 29 Palms, CASep 2, 1971 · N3344E · Substantial damageNTSB LAX72DUJ35Accident · Minor injuriesClosedStinson 108 · Page, AZSep 2, 1971 · N998D · Substantial damageNTSB ANC72AA015Accident · No injuriesClosedPiper PA-14 · Homer, AKSep 2, 1971 · N4200H · Substantial damageNTSB LAX72AL018Accident · Minor injuriesClosedHiller Acft UH12L4 · Grnd Canyon, AZSep 1, 1971 · N90486 · DestroyedNTSB FTW72DPG34Accident · No injuriesClosedPiper PA-25 · Tyronza, ARSep 1, 1971 · N6919Z · Substantial damageNTSB CHI72DET07Accident · No injuriesClosedPiper PA-25 · Colon, MISep 1, 1971 · N7200Z · Substantial damageNTSB ANC72IA014Incident · No injuriesClosedCessna 185 · Kodiak, AKSep 1, 1971 · N1924U · Minor damageNTSB NYC72FNE08Accident · Serious injuriesClosedPiper PA-28 · Danbury, CTAug 29, 1971 · N95409 · DestroyedNTSB NYC72FHA14Accident · No injuriesClosedErco 415-C · Doylestown, PAAug 29, 1971 · N87089 · Substantial damageNTSB ANC72DAA08Accident · Minor injuriesClosedPiper PA-12 · Big Delta, AKAug 29, 1971 · N1121W · Substantial damageNTSB MKC72DER19Accident · No injuriesClosedCessna 172I · Crosby, MNAug 28, 1971 · N35375 · Substantial damageNTSB DEN72DTC07Accident · Minor injuriesClosedForney F1 · Hulett, WYAug 28, 1971 · N7524C · DestroyedNTSB ANC72DAA13Accident · No injuriesClosedPiper PA-18 · Fairbanks, AKAug 28, 1971 · N8676D · Substantial damageNTSB FTW72FRA06Accident · Minor injuriesClosedBeech P35 · Alief, TXAug 27, 1971 · N870T · Substantial damageNTSB DEN72FQA17Accident · No injuriesClosedCessna 150 · Albuquerque, NMAug 27, 1971 · N8682S · Substantial damageNTSB NYC72FLJ07Accident · Minor injuriesClosedAero Comdr A9 · Rocky Mount, NCAug 26, 1971 · N7716V · Substantial damageNTSB MIA72FKQ11Accident · Minor injuriesClosedAeronca 7FC · Collins, MSAug 26, 1971 · N9832Y · Substantial damageNTSB MIA72DKT12Accident · Minor injuriesClosedCessna 172 · Bokeelia, FLAug 26, 1971 · N2442Y · Substantial damageNTSB FTW72DQG16Accident · Minor injuriesClosedPiper PA-25 · Seminole, TXAug 26, 1971 · N6564Z · Substantial damageNTSB MKC72FTK06Accident · No injuriesClosedAmer Avco AA1-A · Rapid City, SDAug 25, 1971 · N9341L · Substantial damageNTSB DEN72FTM05Accident · Serious injuriesClosedCessna 210-5 · Monticello, UTAug 25, 1971 · N8368Z · Substantial damageNTSB MIA72IM026Incident · No injuriesClosedDouglas DC-9 · Bwi, BarbadosAug 24, 1971 · N967PR · No damageNTSB LAX72IL014Incident · No injuriesClosedBoeing 747 · Los Angeles, CAAug 24, 1971 · N748PA · Minor damageNTSB CHI72FEV14Accident · No injuriesClosedPiper PA-25 · Fisher, ILAug 24, 1971 · N4772Y · Substantial damageNTSB SEA72DYA12Accident · No injuriesClosedCessna 150 · Rexburg, IDAug 23, 1971 · N22281 · Substantial damageNTSB MIA72FKW04Accident · No injuriesClosedPiper PA-25 · Murfreesboro, TNAug 23, 1971 · N4719Y · Substantial damageNTSB MKC72FCA07Accident · No injuriesClosedPiper PA-28 · Augusta, MOAug 22, 1971 · N5690U · Substantial damageNTSB CHI72FEP14Accident · Minor injuriesClosedPiper PA-28 · Wauwatosa Br, WIAug 22, 1971 · N7269J · Substantial damageNTSB SEA72DYA11Accident · No injuriesClosedPiper PA-18 · Burley, IDAug 21, 1971 · N8562C · Substantial damageNTSB MKC72DCD27Accident · No injuriesClosedCessna 150G · Calamus, IAAug 21, 1971 · N4738X · Substantial damageNTSB MIA72IM020Incident · No injuriesClosedCessna 310 · Peachtree City, GAAug 21, 1971 · N3606D · Minor damageNTSB MIA72FLD08Accident · Serious injuriesClosedCessna 180 · Brandon, FLAug 21, 1971 · N204JG · Substantial damageNTSB MIA72FKG26Accident · Serious injuriesClosedPiper PA-28 · Lithonia, GAAug 21, 1971 · N1715T · DestroyedNTSB LAX72FVA12Accident · 1 fatalClosedBoeing A75 · Kettleman, CAAug 19, 1971 · N4774Y · DestroyedNTSB ANC72DAG22Accident · No injuriesClosedPiper PA-12 · Cordova, AKAug 18, 1971 · N2383M · Substantial damageNTSB NYC72FFA07Accident · No injuriesClosedPiper PA-22 · Cobleskill, NYAug 16, 1971 · N4469A · Substantial damageNTSB NYC72FHM03Accident · No injuriesClosedBeech 23 · Morganville, NJAug 15, 1971 · N2318Z · Substantial damageNTSB MIA72FKJ17Accident · No injuriesClosedPiper PA-28 · Attalla, ALAug 15, 1971 · N1692J · Substantial damageNTSB CHI72DEP12Accident · Minor injuriesClosedCessna 150 · Gays Mills, WIAug 15, 1971 · N6872F · Substantial damageNTSB ANC72AK014Accident · 3 fatalClosedFairchild 24W46 · Lino Lakes, MNAug 15, 1971 · N81344 · DestroyedNTSB FTW72DPG26Accident · No injuriesClosedGrumman G-164 · Truman, ARAug 14, 1971 · N615U · Substantial damageNTSB NYC72FHA08Accident · No injuriesClosedCessna 172 · Collegeville, PAAug 13, 1971 · N3622L · Substantial damageNTSB MKC72DCD22Accident · No injuriesClosedPiper J3C-65 · Bennett, IAAug 13, 1971 · N42935 · Substantial damageNTSB MIA72DKG21Accident · No injuriesClosedAero Comdr A-9B · Aragon, GAAug 13, 1971 · N7781V · Substantial damageNTSB FTW72FPA04Accident · No injuriesClosedCessna 120 · Waco, TXAug 13, 1971 · N1806N · Substantial damageNTSB MKC72DCD23Accident · No injuriesClosedPiper PA-25 · Manchester, IAAug 12, 1971 · N7230Z · Substantial damageNTSB MIA72FKQ09Accident · No injuriesClosedGrumman G-164A · Swan Lake, MSAug 12, 1971 · N7843 · Substantial damageNTSB MIA72FKQ05Accident · No injuriesClosedCessna 188 · Shaw, MSAug 12, 1971 · N3536Q · Substantial damage