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 CHI71FEM78Accident · Minor injuriesClosedChampion 7KCAB · Kalamazoo, MIMay 13, 1971 · N1639G · DestroyedNTSB CHI71DER07Accident · No injuriesClosedPiper J3C · Star Prairie, WIMay 13, 1971 · N92094 · Substantial damageNTSB SEA71DYG51Accident · No injuriesClosedChampion 7GCBC · Coulee Dam, WAMay 12, 1971 · N6327N · Substantial damageNTSB FTW71FPJ41Accident · No injuriesClosedBeech G50 · Oklahoma Cty, OKMay 11, 1971 · N5697D · DestroyedNTSB ANC71DAD07Accident · Serious injuriesClosedTaylorcraft BC12-D · Sitka, AKMay 11, 1971 · N96261 · DestroyedNTSB OAK71FVA71Accident · No injuriesClosedPiper PA-32 · Merced, CAMay 10, 1971 · N3343W · Substantial damageNTSB FTW71DRH24Accident · No injuriesClosedGrumman G-164A · Floresville, TXMay 10, 1971 · N6575 · DestroyedNTSB MKC71FCG64Accident · Minor injuriesClosedPiper PA-28 · Liberty, MOMay 9, 1971 · N5151S · Substantial damageNTSB DEN71DTM28Accident · No injuriesClosedFournier RF4D · Park City, UTMay 9, 1971 · N2188 · Substantial damageNTSB CHI71DEE77Accident · No injuriesClosedGlobe GC-1B · Kankakee, ILMay 9, 1971 · N80605 · Substantial damageNTSB ANC71FAG87Accident · Serious injuriesClosedStinson 108-2 · Seldovia, AKMay 9, 1971 · N8064K · DestroyedNTSB MKC71DCD81Accident · No injuriesClosedPiper PA-28 · Guthrie Ctr, IAMay 8, 1971 · N7179R · Substantial damageNTSB IAD71AI051Accident · No injuriesClosedPiper PA-32 · Buckhannon, WVMay 8, 1971 · N3344W · Substantial damageNTSB MKC71DCG65Accident · No injuriesClosedCessna 150H · Stillwell, KSMay 6, 1971 · N7132S · Substantial damageNTSB DEN71FQD21Accident · No injuriesClosedPiper PA-24 · Pinon, NMMay 5, 1971 · N7760P · Substantial damageNTSB MIA71AM093Accident · 4 fatalClosedCessna 421A · Conestee, SCMay 2, 1971 · N3194K · DestroyedNTSB SEA71DYI24Accident · Minor injuriesClosedAeronca 7AC · Central Pt, ORMay 1, 1971 · N2412E · Substantial damageNTSB NYC71FGM45Accident · No injuriesClosedCessna 172 · Beaver Falls, PAMay 1, 1971 · N35723 · Substantial damageNTSB DEN71FXT23Accident · No injuriesClosedPiper PA-32 · Provo, UTMay 1, 1971 · N4250R · Substantial damageNTSB DEN71DQD19Accident · Minor injuriesClosedCessna 150 · Silver City, NMMay 1, 1971 · N51255 · Substantial damageNTSB SEA71FYC39Accident · No injuriesClosedPiper PA-28 · Troutdale, ORApr 30, 1971 · N7746N · Substantial damageNTSB SEA71FYC37Accident · No injuriesClosedMaule M-4 · Aurora, ORApr 29, 1971 · N4727T · Substantial damageNTSB SEA71DYG47Accident · No injuriesClosedMooney M20F · Lacrosse, WAApr 28, 1971 · N6774V · Substantial damageNTSB MKC71FCG61Accident · No injuriesClosedStinson 108-3 · Gravois Mills, MOApr 28, 1971 · N6614M · DestroyedNTSB FTW71FPD52Accident · No injuriesClosedHiller UH12B · Arlington, TXApr 28, 1971 · N10195 · DestroyedNTSB DEN71FTE05Accident · Serious injuriesClosedCessna 310C · Denver, COApr 28, 1971 · N1883H · DestroyedNTSB FTW71FRA47Accident · No injuriesClosedGrumman G-164 · Katy, TXApr 27, 1971 · N6573 · Substantial damageNTSB SEA71DYG50Accident · No injuriesClosedNavion B · Spokane, WAApr 26, 1971 · N5273K · Substantial damageNTSB MKC71DCD78Accident · Serious injuriesClosedCessna 140 · Lamoni, IAApr 26, 1971 · N89355 · DestroyedNTSB CHI71FEM73Accident · Minor injuriesClosedCessna 172 · Hillman, MIApr 26, 1971 · N6810A · DestroyedNTSB IAD71FKM35Accident · No injuriesClosedCessna 150G · Mocksville, NCApr 25, 1971 · N3785J · Substantial damageNTSB MKC71FCG57Accident · Minor injuriesClosedPiper PA-25 · Marshall, MOApr 24, 1971 · N6011Z · Substantial damageNTSB MIA71FKT56Accident · No injuriesClosedPiper PA-24 · Opa Locka, FLApr 24, 1971 · N5005P · Substantial damageNTSB CHI71FLQ23Accident · No injuriesClosedVolksplane VP-1 · Louisville, KYApr 24, 1971 · N33GB · DestroyedNTSB CHI71FEC46Accident · No injuriesClosedCessna 150E · Memphis, MIApr 24, 1971 · N3085J · Substantial damageNTSB CHI71FEC45Accident · Minor injuriesClosedKay Spcl · Sterling Hts, MIApr 24, 1971 · N57K · Substantial damageNTSB CHI71FEV38Accident · No injuriesClosedPiper PA-28 · Centralia, ILApr 23, 1971 · N6944W · Substantial damageNTSB NYC71AN106Accident · 4 fatalClosedBeech C35 · Manchester, NHApr 22, 1971 · N144H · DestroyedNTSB NYC71FFA57Accident · 1 fatalClosedPiper PA-25 · Livingston, NYApr 21, 1971 · N8227 · DestroyedNTSB MKC71DCQ36Accident · No injuriesClosedBeech J35 · Plainville, KSApr 21, 1971 · N8271D · Substantial damageNTSB MIA71AM091Accident · 8 fatalClosedBeech E18S · C.Amalie, VIApr 21, 1971 · N456T · DestroyedNTSB SEA71FDG28Accident · No injuriesClosedPiper PA-18 · Hamilton, MTApr 19, 1971 · N4299Z · Substantial damageNTSB NYC71DHD46Accident · Serious injuriesClosedBell 47G4 · Mays Ldg, NJApr 19, 1971 · N73944 · DestroyedNTSB OAK71FVG73Accident · Serious injuriesClosedPiper PA-24 · Vacaville, CAApr 18, 1971 · N6189P · DestroyedNTSB CHI71FET23Accident · No injuriesClosedPiper PA-25 · Roling Prarie, INApr 18, 1971 · N7418Z · Substantial damageNTSB SEA71FDA18Accident · No injuriesClosedCessna A188 · Joliet, MTApr 17, 1971 · N9821V · Substantial damageNTSB NYC71FNA47Accident · No injuriesClosedCessna 170B · Taunton, MAApr 17, 1971 · N2501D · Substantial damageNTSB SEA71FDG27Accident · No injuriesClosedMooney M20C · Santa Rita, MTApr 16, 1971 · N5996Q · Substantial damageNTSB NYC71FFJ32Accident · No injuriesClosedPiper PA-23 · Horseheads, NYApr 16, 1971 · N13791 · Substantial damageNTSB LAX71AL060Accident · 5 fatalClosedBeech G18S · Grnd Canyon, AZApr 16, 1971 · N318M · Destroyed