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 MIA71FKT07Accident · Serious injuriesClosedAero Comdr 680T · Pompano Beach, FLOct 3, 1970 · N541W · Substantial damageNTSB LAX71FUQ24Accident · Serious injuriesClosedAmer Avco AA1 · Agua Dulce, CAOct 3, 1970 · N5666L · DestroyedNTSB FTW71FPM09Accident · Serious injuriesClosedBellanca 14-133 · Tulsa, OKOct 3, 1970 · N6505N · Substantial damageNTSB MIA71FLG14Accident · Serious injuriesClosedHelio Acft H-295 · Pelion, SCOct 2, 1970 · N6474V · Substantial damageNTSB ANC71AA022Accident · Serious injuriesClosedCessna 207 · Sitkinak, AKOct 2, 1970 · N91117 · DestroyedNTSB NYC71AN042Accident · 1 fatalClosedFairchild PT-19B · Ware, MAOct 1, 1970 · N50482 · DestroyedNTSB MIA71DKJ21Accident · No injuriesClosedPiper J-3 · Hazel Green, ALOct 1, 1970 · N5555P · Substantial damageNTSB MIA71AKJ20Accident · Minor injuriesClosedPiper PA-28 · Jasper, ALSep 29, 1970 · N5763U · DestroyedNTSB FTW71FPJ18Accident · Minor injuriesClosedAero Comdr 680V · Norman, OKSep 28, 1970 · N1123V · Substantial damageNTSB FTW71DRG30Accident · Minor injuriesClosedCessna 172 · Lowake, TXSep 28, 1970 · N5997R · Substantial damageNTSB MIA71FLD20Accident · No injuriesClosedCessna 140 · Eustis, FLSep 26, 1970 · N2643N · Substantial damageNTSB LAX71FUQ21Accident · Serious injuriesClosedGlobe GC-1B · S Fernando, CASep 26, 1970 · N90368 · DestroyedNTSB LAX71DUQ22Accident · No injuriesClosedCessna 182 · Oxnard, CASep 26, 1970 · N3266Y · Substantial damageNTSB FTW71AF014Accident · 4 fatalClosedBeech C35 · Fayetteville, ARSep 26, 1970 · N5810C · DestroyedNTSB CHI71FBA13Accident · No injuriesClosedPiper PA-28 · Seymour, INSep 26, 1970 · N6346W · Substantial damageNTSB FTW71FRA20Accident · No injuriesClosedMooney M20G · Fresno, TXSep 25, 1970 · N6946N · Substantial damageNTSB ANC71DAG30Accident · No injuriesClosedTaylorcraft L-2 · Gulkana, AKSep 25, 1970 · N57744 · Substantial damageNTSB DEN71FXA10Accident · Minor injuriesClosedCessna 182E · Rock Springs, WYSep 23, 1970 · N3189Y · Substantial damageNTSB ANC71DAA18Accident · Minor injuriesClosedPiper PA-18 · Mckinley Prk, AKSep 22, 1970 · N1342A · Substantial damageNTSB SEA71FXM10Accident · Serious injuriesClosedCessna 182B · Sun Valley, IDSep 21, 1970 · N8441T · DestroyedNTSB FTW71FRA21Accident · No injuriesClosedMooney M20G · Palacios, TXSep 21, 1970 · N6913N · Substantial damageNTSB FTW71FPG23Accident · Minor injuriesClosedPiper PA-25 · Keo, ARSep 21, 1970 · N7299Z · Substantial damageNTSB MIA71AM029Accident · 2 fatalClosedBeech V35A · Ft.Lauderdale, FLSep 20, 1970 · N7977R · DestroyedNTSB DEN71DXD26Accident · Serious injuriesClosedBeech 35-A33 · Salida, COSep 20, 1970 · N309Z · Substantial damageNTSB CHI72DDJ17Accident · Minor injuriesClosedStinson 108-3 · Bruce, WISep 20, 1970 · N960C · Substantial damageNTSB MKC71FDQ11Accident · Minor injuriesClosedAeronca 7AC · Rapid City, SDSep 19, 1970 · N82576 · Substantial damageNTSB MKC71DDQ12Accident · No injuriesClosedCessna 120 · Harrold, SDSep 19, 1970 · N3141N · Substantial damageNTSB FTW71DQG36Accident · Minor injuriesClosedCessna 150H · Amarillo, TXSep 19, 1970 · N22706 · Substantial damageNTSB CHI71FGD15Accident · Serious injuriesClosedCessna 177 · Painesville, OHSep 19, 1970 · N2936E · DestroyedNTSB MKC71FDM24Accident · Minor injuriesClosedCessna 150F · Isabella, MNSep 18, 1970 · N8743G · Substantial damageNTSB MIA71DKG39Accident · No injuriesClosedCessna 150F · Warner Robins, GASep 18, 1970 · N6809F · Substantial damageNTSB IAD71II010Incident · No injuriesClosedLear Jet 23 · Washington, DCSep 18, 1970 · N1966W · Minor damageNTSB FTW71FPD20Accident · No injuriesClosedPiper PA-28 · Saginaw, TXSep 18, 1970 · N8197N · Substantial damageNTSB DEN71AD012Accident · 2 fatalClosedPiper PA-30 · Boulder, COSep 18, 1970 · N8414Y · DestroyedNTSB DCA71IZ002Incident · No injuriesClosedBoeing 747 · San Francisco, CASep 18, 1970 · N743PA · Minor damageNTSB CHI71FBD22Accident · Serious injuriesClosedCessna 150 · Wheeling, ILSep 18, 1970 · N3007J · Substantial damageNTSB MIA71FKQ11Accident · 1 fatalClosedPiper PA-25 · Georgetown, MSSep 14, 1970 · N6420Z · DestroyedNTSB FTW71FPG21Accident · No injuriesClosedPiper PA-22 · N Little Rock, ARSep 14, 1970 · N7026B · Substantial damageNTSB SEA71AS014Accident · 3 fatalClosedBeech A23A · Medford, ORSep 13, 1970 · N2355W · DestroyedNTSB NYC71FHJ08Accident · Minor injuriesClosedAlon A2-A · East Hampton, NYSep 13, 1970 · N5651F · Substantial damageNTSB FTW71FRA18Accident · No injuriesClosedCessna 170A · Pearland, TXSep 13, 1970 · N9560A · Substantial damageNTSB DEN71DXT09Accident · No injuriesClosedK.Twister IMP · Coalville, UTSep 13, 1970 · N5DF · Substantial damageNTSB ANC71FAG23Accident · No injuriesClosedMaule M4-210 · Glennallen, AKSep 13, 1970 · N4604T · Substantial damageNTSB ANC71DAG21Accident · No injuriesClosedChampion 7GCB · Anchorage, AKSep 13, 1970 · N9810W · Substantial damageNTSB MIA71AM025Accident · 2 fatalClosedLake LA-4 · Hernando, MSSep 11, 1970 · N7603L · DestroyedNTSB MIA71FKD07Accident · No injuriesClosedCessna 177 · Savannah, TNSep 10, 1970 · N30024 · DestroyedNTSB CHI71FDW16Accident · No injuriesClosedBeech 35-B33 · Plymouth, MISep 10, 1970 · N1422G · Substantial damageNTSB MKC71AK013Accident · 1 fatalClosedPiper PA-28 · Ewing, MOSep 9, 1970 · N8117W · DestroyedNTSB CHI71FDJ15Accident · No injuriesClosedCessna 150 · East Troy, WISep 9, 1970 · N7900E · Substantial damageNTSB SEA71DWD16Accident · No injuriesClosedAeronca 15AC · Olympia, WASep 8, 1970 · N1118H · Substantial damage