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 NYC69D0336Accident · No injuriesClosedPiper PA-22 · East Bethany, NYFeb 22, 1969 · N8935C · Substantial damageNTSB SEA69F0197Accident · No injuriesClosedCessna 120 · Beaverton, ORFeb 21, 1969 · N2551N · DestroyedNTSB DIA69F0160Accident · Serious injuriesClosedChampion 7KCAB · Midland, VAFeb 21, 1969 · N1683G · Substantial damageNTSB LAX69F0288Accident · Minor injuriesClosedPiper PA-28 · Corona, CAFeb 15, 1969 · N3702K · DestroyedNTSB LAX69D0279Accident · Minor injuriesClosedGrumman G-164A · Greenfield, CAFeb 15, 1969 · N931X · Substantial damageNTSB MKC69F0362Accident · No injuriesClosedCessna 421A · Wichita, KSFeb 14, 1969 · N2241Q · Substantial damageNTSB DIA69F0144Accident · Serious injuriesClosedCessna 172 · Mooresville, NCFeb 10, 1969 · N371LA · Substantial damageNTSB MIA69F0342Accident · Minor injuriesClosedBeech C-45A · Ramey Afb, PRFeb 8, 1969 · N9085R · Substantial damageNTSB LAX69D0247Accident · No injuriesClosedCessna 150 · Lakeside, CAFeb 8, 1969 · N8128S · Substantial damageNTSB CHI70F0196Accident · Serious injuriesClosedCessna 337 · Monmouth, ILFeb 7, 1969 · N2154X · DestroyedNTSB ANC69F0107Accident · No injuriesClosedPiper PA-12 · Anchorage, AKFeb 6, 1969 · N3676N · Substantial damageNTSB SEA69A0045Accident · 10 fatalClosedBeech C-45H · Port Angeles, WAFeb 5, 1969 · N8181H · DestroyedNTSB SEA69F0195Accident · No injuriesClosedCessna T210F · Vancouver, WAFeb 3, 1969 · N6143R · Substantial damageNTSB DEN69D0198Accident · No injuriesClosedNavion G · Fountain, COFeb 3, 1969 · N2441T · Substantial damageNTSB ANC69F0101Accident · No injuriesClosedPiper PA-18 · Anchorage, AKFeb 3, 1969 · N9487D · Substantial damageNTSB NYC69F0323Accident · No injuriesClosedAero Comdr 100 · Westboro, MAFeb 2, 1969 · N2927T · Substantial damageNTSB MKC69D0345Accident · No injuriesClosedPiper J-3 · Plymouth, IAFeb 2, 1969 · N3245N · Substantial damageNTSB MKC69A0047Accident · Serious injuriesClosedBeech D18S · Kansas City, MOFeb 1, 1969 · N44634 · DestroyedNTSB ANC69F0126Accident · Serious injuriesClosedChampion 7GCB · Cordova, AKFeb 1, 1969 · N8997R · DestroyedNTSB MKC69F0370Accident · No injuriesClosedPiper J3F-65 · Sargeant, MNJan 26, 1969 · N38438 · Substantial damageNTSB CHI69D0459Accident · Minor injuriesClosedPiper PA-22 · Grand Haven, MIJan 26, 1969 · N8839C · DestroyedNTSB MIA69F0317Accident · Minor injuriesClosedCessna 182 · Nashville, TNJan 25, 1969 · N9234X · DestroyedNTSB CHI69F0450Accident · No injuriesClosedCessna 150H · Northbrook, ILJan 25, 1969 · N22046 · Substantial damageNTSB UNK69X0107Accident · No injuriesClosedPiper PA-25 · Kerkira, GreeceJan 23, 1969 · N4696Y · Substantial damageNTSB MIA69F0343Accident · Minor injuriesClosedCessna 150G · Opa Locka, FLJan 22, 1969 · N4032J · Substantial damageNTSB DIA69A0035Accident · Minor injuriesClosedPiper PA-28 · Wadesboro, NCJan 21, 1969 · N7595J · Substantial damageNTSB LAX69F0269Accident · Minor injuriesClosedPiper PA-22 · Flagstaff, AZJan 18, 1969 · N8642D · DestroyedNTSB DIA69F0137Accident · Minor injuriesClosedPiper PA-18 · Fort Meade, MDJan 18, 1969 · N1005A · Substantial damageNTSB DIA69A0034Accident · Minor injuriesClosedDouglas DC-3 · Dulles Itl Apt, VAJan 18, 1969 · N34113 · Substantial damageNTSB ANC69A0036Accident · No injuriesClosedBeech D50C · Umiat, AKJan 17, 1969 · N4031H · Substantial damageNTSB OAK69F0226Accident · Serious injuriesClosedCessna TU206A · Palo Alto, CAJan 16, 1969 · N4899F · DestroyedNTSB LAX69F0270Accident · 1 fatalClosedCessna 150 · Davis Monthn, AZJan 16, 1969 · N6858F · DestroyedNTSB ANC69D0104Accident · No injuriesClosedCessna 180 · Dillingham, AKJan 16, 1969 · N4601A · Substantial damageNTSB MKC69F0321Accident · Minor injuriesClosedBeech S35 · Mankato, MNJan 13, 1969 · N1727W · Substantial damageNTSB MIA69D0310Accident · No injuriesClosedCessna 337B · Opa Locka, FLJan 12, 1969 · N2393S · Substantial damageNTSB FTW69D0357Accident · No injuriesClosedGrumman G-73 · Jasper, TXJan 12, 1969 · N2940 · Substantial damageNTSB CHI69D0431Accident · No injuriesClosedPiper PA-32 · Boyne Falls, MIJan 12, 1969 · N3922W · Substantial damageNTSB SEA69F0182Accident · Serious injuriesClosedPietenpol Parasl · Spanaway, WAJan 11, 1969 · N9258 · DestroyedNTSB MKC69D0353Accident · No injuriesClosedTaylorcraft BC12-D · Amelia, NEJan 11, 1969 · N95067 · Substantial damageNTSB FTW69F0368Accident · Minor injuriesClosedLake LA-4 · Quinlan, TXJan 7, 1969 · N7615L · Substantial damageNTSB CHI69F0433Accident · No injuriesClosedPiper PA-28 · Plainwell, MIJan 7, 1969 · N4607R · Substantial damageNTSB OAK69D0217Accident · No injuriesClosedCessna 175 · Oceano, CAJan 5, 1969 · N9323B · Substantial damageNTSB CHI69D0482Accident · No injuriesClosedAeronca L-3 · Twinsburg, OHJan 5, 1969 · N47002 · Substantial damageNTSB OAK69F0210Accident · Minor injuriesClosedStearman B75N1 · Castroville, CAJan 4, 1969 · N1226V · DestroyedNTSB MIA69F0320Accident · No injuriesClosedPiper PA-28 · Enterprise, ALJan 4, 1969 · N4536J · Substantial damageNTSB MKC69F0307Accident · No injuriesClosedBeech A35 · Burlington, IAJan 2, 1969 · N8666A · Substantial damageNTSB UNK69X0103Accident · No injuriesClosedMooney M20A · Mexico City, MexicoDec 31, 1968 · N6544B · Substantial damageNTSB DEN69D0205Accident · No injuriesClosedPiper PA-23 · Boulder, CODec 31, 1968 · N1097P · Substantial damageNTSB CHI69F0641Accident · No injuriesClosedCessna 172G · Brownsburg, INDec 31, 1968 · N4215L · Substantial damageNTSB CHI69F0424Accident · 1 fatalClosedAlbar 02 · Mt.Pleasant, MIDec 29, 1968 · N3141 · Destroyed