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 MIA67F0008Accident · Serious injuriesClosedCessna 150 · Fort Benning, GAJul 4, 1966 · N7964E · Substantial damageNTSB FTW67F0083Accident · Serious injuriesClosedLuscombe 8A · Caddo Mills, TXJul 4, 1966 · N1702K · DestroyedNTSB DEN67D0032Accident · No injuriesClosedCessna 170 · Uravan, COJul 4, 1966 · N1924C · Substantial damageNTSB CHI67F0084Accident · Minor injuriesClosedCessna 172 · Circleville, OHJul 4, 1966 · N6462B · Substantial damageNTSB CAN F0404Accident · No injuriesClosedCessna 172 · St.Laurent, CanadaJul 4, 1966 · N2382U · Substantial damageNTSB ANC67F0024Accident · No injuriesClosedStinson 108-2 · Nchorage, AKJul 4, 1966 · N9293K · Substantial damageNTSB MIA67F0080Accident · Serious injuriesClosedTaylorcraft BC-65 · Stockbridge, GAJul 3, 1966 · N2447M · Substantial damageNTSB DEN67D0033Accident · No injuriesClosedCessna 150 · Elizabeth, COJul 3, 1966 · N8784S · Substantial damageNTSB SEA67D0037Accident · Serious injuriesClosedBellanca 14-13 · Mackay, IDJul 2, 1966 · N41878 · DestroyedNTSB LAX67D0039Accident · No injuriesClosedBeech 95-C55 · Mckittrick, CAJul 2, 1966 · N465B · Substantial damageNTSB NYC67F0040Accident · No injuriesClosedStinson 108-1 · Lindenhurst, NYJul 1, 1966 · N8332K · Substantial damageNTSB NYC67A0002Accident · 3 fatalClosedPiper PA-23 · Neptune, NJJul 1, 1966 · N2014P · DestroyedNTSB DEN67F0176Accident · 1 fatalClosedCessna 140 · Broomfield, COJun 30, 1966 · N72276 · DestroyedNTSB CHI67F0088Accident · Serious injuriesClosedPiper J3C-65 · La Porte, INJun 30, 1966 · N70580 · Substantial damageNTSB NYC67F0111Accident · Minor injuriesClosedHelio H-250 · Northville, NYJun 29, 1966 · N5458E · Substantial damageNTSB MIA67D0056Accident · No injuriesClosedPiper PA-23 · Orlando, FLJun 29, 1966 · N4905P · Substantial damageNTSB FTW67F0101Accident · Serious injuriesClosedJodel PH-1 · Del Rio, TXJun 28, 1966 · N2841J · DestroyedNTSB CHI67F0087Accident · No injuriesClosedNavion B · Marshall, WIJun 28, 1966 · N29P · Substantial damageNTSB MKC67F0131Accident · No injuriesClosedPiper PA-22 · Topeka, KSJun 27, 1966 · N7159D · Substantial damageNTSB MIA67D0049Accident · No injuriesClosedCessna 150 · Fayetteville, GAJun 27, 1966 · N7181X · Substantial damageNTSB CHI67F0093Accident · Serious injuriesClosedCessna 150 · Ann Arbor, MIJun 26, 1966 · N2753J · 2 aircraft · DestroyedNTSB SEA67D0039Accident · Serious injuriesClosedStinson 108 · Buhl, IDJun 25, 1966 · N97306 · DestroyedNTSB ANC67F0005Accident · 1 fatalClosedPiper PA-14 · Fairbanks, AKJun 25, 1966 · N5163H · DestroyedNTSB NYC66A0156Accident · 1 fatalClosedErco 415C · Topping, VAJun 24, 1966 · N93484 · DestroyedNTSB DEN67D0009Accident · No injuriesClosedCessna 150 · Meeker, COJun 23, 1966 · N5658E · Substantial damageNTSB UNK66X0150Accident · No injuriesClosedCessna 172 · Perrault Flls, CanadaJun 22, 1966 · N8116U · DestroyedNTSB MIA66F0608Accident · Minor injuriesClosedPiper PA-28 · Daytona Beach, FLJun 22, 1966 · N6992W · Substantial damageNTSB SEA67D0058Accident · Minor injuriesClosedPiper PA-24 · Montpelier, IDJun 21, 1966 · N7093P · Substantial damageNTSB FTW67D0044Accident · Minor injuriesClosedPiper PA-24 · Tampico, MexicoJun 21, 1966 · N7342P · Substantial damageNTSB FTW67A0117Accident · Minor injuriesClosedBell 47G-2A · Morgan City, LAJun 21, 1966 · N73233 · DestroyedNTSB OAK66A0058Accident · No injuriesClosedBell 47G3B1 · Lone Pine, CAJun 20, 1966 · N73987 · Substantial damageNTSB CHI66A0101Accident · Minor injuriesClosedSikorsky S58C · Gary, INJun 20, 1966 · N866 · DestroyedNTSB SEA67D0029Accident · Minor injuriesClosedCessna 182 · Three Forks, MTJun 19, 1966 · N6235A · Substantial damageNTSB MIA66F0621Accident · No injuriesClosedMooney M20E · Fairburn, GAJun 19, 1966 · N1256X · Substantial damageNTSB MIA66A0146Accident · 4 fatalClosedNavion NAV-4 · Newman, GAJun 19, 1966 · N4610K · DestroyedNTSB LAX67D0020Accident · No injuriesClosedPiper PA-22 · Strawberry, AZJun 19, 1966 · N2191A · Substantial damageNTSB FTW67F0289Accident · Serious injuriesClosedBeech A-23A · Pearland, TXJun 19, 1966 · N2304W · Substantial damageNTSB FTW67D0079Accident · No injuriesClosedPiper PA-28 · Nederland, TXJun 19, 1966 · N417HP · Substantial damageNTSB DEN66A0089Accident · No injuriesClosedBell 47D1 · Hyattville, WYJun 19, 1966 · N74121 · Substantial damageNTSB CHI67F0016Accident · No injuriesClosedTemco GC1B · Ann Arbor, MIJun 19, 1966 · N2386B · Substantial damageNTSB ANC67F0011Accident · Minor injuriesClosedPiper PA-28 · Naknek, AKJun 19, 1966 · N4544R · Substantial damageNTSB SEA67D0057Accident · Serious injuriesClosedLuscombe 8A · Challis, IDJun 18, 1966 · N45554 · DestroyedNTSB MKC66F0584Accident · Minor injuriesClosedPiper PA-25 · Wisner, NEJun 18, 1966 · N6417Z · DestroyedNTSB FTW66A0116Accident · 1 fatalClosedCherokee II · Tulsa, OKJun 18, 1966 · N74007 · DestroyedNTSB CHI66F0012Accident · Serious injuriesClosedSchweizer SGU222 · Bay City, WIJun 18, 1966 · N10399 · DestroyedNTSB SEA67D0025Accident · No injuriesClosedCessna 172 · Atomic City, IDJun 17, 1966 · N3826L · Substantial damageNTSB SEA66A0068Accident · No injuriesClosedBell 47G3B1 · Lincoln, MTJun 17, 1966 · N8505F · Substantial damageNTSB MKC67F0057Accident · No injuriesClosedBeech 35 · Lowden, IAJun 17, 1966 · N3751N · Substantial damageNTSB MKC66A0067Accident · 1 fatalClosedMooney M20C · Kansas City, KSJun 17, 1966 · N6211U · DestroyedNTSB MKC67F0042Accident · Serious injuriesClosedCessna 170 · Grand River, IAJun 16, 1966 · N2580V · Substantial damage