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 OAK71DVA15Accident · No injuriesClosedPiper PA-12 · Fresno, CAAug 3, 1970 · N2392M · Substantial damageNTSB NYC71DFD03Accident · Minor injuriesClosedCessna 150H · Stow, MAAug 3, 1970 · N23250 · DestroyedNTSB CHI71FGD07Accident · Minor injuriesClosedPiper PA-28 · Bowlng Green, OHAug 3, 1970 · N1890J · DestroyedNTSB SEA71FDG07Accident · No injuriesClosedLuscombe 8F · Butte, MTAug 2, 1970 · N1995B · Substantial damageNTSB NYC71FHD13Accident · Serious injuriesClosedNoorduyn UC-64 · Lakewood, NJAug 2, 1970 · N1207 · DestroyedNTSB MKC71FDM15Accident · No injuriesClosedCessna 172E · Elysian, MNAug 1, 1970 · N5688T · Substantial damageNTSB MIA71FKJ07Accident · No injuriesClosedPiper PA-22 · Moundville, ALAug 1, 1970 · N4691Z · Substantial damageNTSB MIA71AM010Accident · 4 fatalClosedPiper PA-28 · Brunswick, GAAug 1, 1970 · N98271 · DestroyedNTSB FTW71FPD08Accident · No injuriesClosedPiper PA-28 · Denton, TXAug 1, 1970 · N4122J · Substantial damageNTSB CHI71FDW08Accident · Serious injuriesClosedPiper PA-28 · Chelsea, MIAug 1, 1970 · N3983K · DestroyedNTSB CHI71FBJ16Accident · No injuriesClosedMeyers 200-B · Midland, MIAug 1, 1970 · N34386 · Substantial damageNTSB FTW71DQG20Accident · Minor injuriesClosedTed Smith 601 · Big Spring, TXJul 31, 1970 · N7453S · Substantial damageNTSB DEN71FXD22Accident · Serious injuriesClosedCessna 172 · Denver, COJul 31, 1970 · N8585B · Substantial damageNTSB MKC71DCJ13Accident · No injuriesClosedCessna 188A · Oxford, NEJul 30, 1970 · N3538Q · Substantial damageNTSB CHI71FDW09Accident · Minor injuriesClosedPitts Specl S-1CBD · Pontiac, MIJul 30, 1970 · N161Y · Substantial damageNTSB MKC71FCA16Accident · Minor injuriesClosedStinson 108-3 · Mountain View, MOJul 29, 1970 · N6142M · Substantial damageNTSB MIA71DLA07Accident · Minor injuriesClosedCessna 150F · Spring Creek, FLJul 29, 1970 · N8944S · Substantial damageNTSB DEN71FXD15Accident · No injuriesClosedPiper PA-28 · Ft.Collins, COJul 29, 1970 · N7921N · Substantial damageNTSB OAK71FUA11Accident · No injuriesClosedGrumman TBM-3E · Columbia, CAJul 28, 1970 · N3356G · Substantial damageNTSB FTW71DQD03Accident · No injuriesClosedCessna 210 · Balmorhea, TXJul 27, 1970 · N5949F · Substantial damageNTSB MKC71FCG09Accident · No injuriesClosedAeronca 7AC · Independence, MOJul 26, 1970 · N2853E · Substantial damageNTSB MIA71FKA01Accident · No injuriesClosedCessna 172D · Vega Baja, PRJul 25, 1970 · N2160Y · Substantial damageNTSB MIA71DKJ05Accident · No injuriesClosedPiper PA-28 · Dutton, ALJul 25, 1970 · N5708F · Substantial damageNTSB DEN71FXD12Accident · 1 fatalClosedPiper PA-28R · Montrose, COJul 25, 1970 · N7569J · DestroyedNTSB CHI71FDJ08Accident · Serious injuriesClosedTaylorcraft BC12-D · Land O Lakes, WIJul 25, 1970 · N43009 · Substantial damageNTSB CHI71FDJ07Accident · Serious injuriesClosedBeech E18S · Delavan, WIJul 25, 1970 · N3333C · DestroyedNTSB LAX71FUA04Accident · No injuriesClosedPiper PA-28 · Torrance, CAJul 24, 1970 · N6505J · Substantial damageNTSB DEN71DQA11Accident · No injuriesClosedCessna 150F · Gallup, NMJul 24, 1970 · N8681S · Substantial damageNTSB MKC71FCG08Accident · No injuriesClosedBellanca 14-13 · Waldron, MOJul 23, 1970 · N74201 · Substantial damageNTSB LAX71FUM08Accident · No injuriesClosedPiper PA-28 · Chula Vista, CAJul 23, 1970 · N7275J · Substantial damageNTSB CHI71FBJ11Accident · Serious injuriesClosedPiper PA-22 · Kalamazoo, MIJul 23, 1970 · N2253A · Substantial damageNTSB CHI71DDM01Accident · No injuriesClosedCessna 150C · River Falls, WIJul 23, 1970 · N1907Z · Substantial damageNTSB ANC71FAG09Accident · No injuriesClosedCessna U206C · Naknek, AKJul 23, 1970 · N29179 · Substantial damageNTSB MKC71FCD05Accident · No injuriesClosedPietenpol AAA-1 · Ottumwa, IAJul 21, 1970 · NX4716 · Substantial damageNTSB LAX71FUG05Accident · No injuriesClosedPiper J3C-65 · Colton, CAJul 21, 1970 · N1528N · Substantial damageNTSB FTW71FQG07Accident · No injuriesClosedPiper PA-25 · Shallowater, TXJul 21, 1970 · N4984Y · DestroyedNTSB OAK71FVA10Accident · No injuriesClosedBell 47G-2 · Exeter, CAJul 20, 1970 · N10194 · Substantial damageNTSB CHI71FDM02Accident · No injuriesClosedCessna 172G · Mondovi, WIJul 20, 1970 · N3616L · Substantial damageNTSB NYC71AN009Accident · Serious injuriesClosedBoeing 737 · Philadelphia, PAJul 19, 1970 · N9005U · Substantial damageNTSB SEA71FWG08Accident · Serious injuriesClosedMaule M-4 · Daisy, WAJul 18, 1970 · N4616T · Substantial damageNTSB MIA71DKW01Accident · No injuriesClosedBellanca 14-13 · Lawrenceburg, TNJul 18, 1970 · N74480 · Substantial damageNTSB NYC71AN007Accident · 3 fatalClosedAero Comdr 680-FL · Butler, PAJul 17, 1970 · N6501V · DestroyedNTSB SEA71FDG05Accident · No injuriesClosedPiper PA-23 · Helena, MTJul 16, 1970 · N1279P · Substantial damageNTSB IAD71DKM02Accident · No injuriesClosedMooney M20F · Winston-Salem, NCJul 16, 1970 · N3417N · Substantial damageNTSB MKC71FCJ05Accident · No injuriesClosedMooney M20C · Davenport, NEJul 15, 1970 · N9257M · Substantial damageNTSB MIA71FLG02Accident · No injuriesClosedBeech H35 · Cayce, SCJul 15, 1970 · N7920D · Substantial damageNTSB NYC71AN006Accident · 4 fatalClosedPiper PA-28 · Conneaut Lake, PAJul 13, 1970 · N6759J · DestroyedNTSB MKC71FCA01Accident · 2 fatalClosedCessna 210H · Van Buren, MOJul 13, 1970 · N6142F · DestroyedNTSB CHI71FBJ05Accident · No injuriesClosedBell 47G · Edmore, MIJul 13, 1970 · N73872 · Substantial damageNTSB CHI71AC003Accident · 4 fatalClosedCessna 177 · Wild Rose, WIJul 12, 1970 · N2277Y · Destroyed