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 OAK69D0306Accident · Serious injuriesClosedCessna 182M · Livermore, CAApr 22, 1969 · N667PL · Substantial damageNTSB FTW69F0464Accident · No injuriesClosedGrumman G-164A · Oberlin, LAApr 22, 1969 · N5295 · Substantial damageNTSB DIA69I0057Incident · No injuriesClosedBoeing 707 · Baltimore, MDApr 22, 1969 · N706PA · No damageNTSB OAK69D0307Accident · No injuriesClosedCallair A-9 · Dixon, CAApr 21, 1969 · N9616Z · Substantial damageNTSB MKC69F0438Accident · Serious injuriesClosedPiper PA-22 · Richland, MOApr 21, 1969 · N5705Z · Substantial damageNTSB OAK69D0310Accident · No injuriesClosedGlobe GC-1B · Redding, CAApr 20, 1969 · N78291 · Substantial damageNTSB MIA70F0027Accident · Serious injuriesClosedAeronca 65-TC · Cartersville, GAApr 20, 1969 · N31387 · DestroyedNTSB MIA69F0474Accident · Serious injuriesClosedPiper PA-28 · Pelzer, SCApr 20, 1969 · N6307J · DestroyedNTSB CHI69F0662Accident · Minor injuriesClosedMooney M20C · Northbrook, ILApr 20, 1969 · N6836N · Substantial damageNTSB MIA69F0470Accident · No injuriesClosedHiller UH-12C · Hato Rey, PRApr 19, 1969 · N5317V · Substantial damageNTSB ANC69A0046Accident · 9 fatalClosedDehavilland DHC-2 · Craig, AKApr 19, 1969 · N167W · DestroyedNTSB FTW69F0493Accident · No injuriesClosedPiper PA-28 · Okla.City, OKApr 18, 1969 · N1895J · Substantial damageNTSB SEA69D0241Accident · Minor injuriesClosedKaman H-43A · Castle Rock, WAApr 16, 1969 · N2849J · DestroyedNTSB MIA69A0068Accident · 1 fatalClosedCessna 172D · Summerton, SCApr 16, 1969 · N50001 · Substantial damageNTSB FTW69A0074Accident · 6 fatalClosedCessna 310N · Huntsville, TXApr 15, 1969 · N5072Q · DestroyedNTSB NYC69F0428Accident · Minor injuriesClosedPiper PA-22 · Titusville, PAApr 13, 1969 · N7733D · Substantial damageNTSB MIA69A0067Accident · 1 fatalClosedPiper PA-22 · Meadville, MSApr 13, 1969 · N9581D · DestroyedNTSB FTW70F0014Accident · No injuriesClosedPiper PA-18A · Bloomington, TXApr 13, 1969 · N2886P · Substantial damageNTSB CHI69D0618Accident · No injuriesClosedPiper PA-24 · Alliance, OHApr 13, 1969 · N6146P · Substantial damageNTSB SEA69D0230Accident · Serious injuriesClosedCessna 188 · Genesee, IDApr 11, 1969 · N8024V · Substantial damageNTSB SEA69F0249Accident · 1 fatalClosedPiper PA-25 · Geraldine, MTApr 10, 1969 · N7291Z · DestroyedNTSB SEA69D0205Accident · No injuriesClosedCessna 140 · Midvale, IDApr 10, 1969 · N72758 · Substantial damageNTSB OAK69D0301Accident · No injuriesClosedPiper PA-28 · Santa Rosa, CAApr 10, 1969 · N8679W · Substantial damageNTSB OAK69D0270Accident · No injuriesClosedCessna 150 · Lanai City, HIApr 10, 1969 · N8777S · Substantial damageNTSB FTW69D0482Accident · Minor injuriesClosedAero Comdr S-2R · Garwood, TXApr 10, 1969 · N1761S · DestroyedNTSB SEA69D0219Accident · No injuriesClosedCessna 172C · Mccall, IDApr 9, 1969 · N8268X · Substantial damageNTSB NYC69A0092Accident · 1 fatalClosedAeronca 65-C · Phelps, NYApr 9, 1969 · N31938 · DestroyedNTSB FTW69A0071Accident · 3 fatalClosedPiper PA-24 · Wolfe City, TXApr 9, 1969 · N6028P · DestroyedNTSB LAX69F0310Accident · No injuriesClosedBeech F35 · Burbank, CAApr 8, 1969 · N3219C · Substantial damageNTSB CHI70D0071Accident · No injuriesClosedCessna 150F · Burlington, WIApr 7, 1969 · N8927S · Substantial damageNTSB SEA69F0251Accident · Serious injuriesClosedMooney M20F · Melville, MTApr 6, 1969 · N2974L · DestroyedNTSB FTW69F0448Accident · Minor injuriesClosedStinson 108-1 · Pearland, TXApr 6, 1969 · N8761K · DestroyedNTSB FTW69A0068Accident · 1 fatalClosedRhein 3A-P75 · Morrilton, ARApr 6, 1969 · N4949E · DestroyedNTSB CHI69F0575Accident · No injuriesClosedMooney M20C · Joliet, ILApr 6, 1969 · N9281M · Substantial damageNTSB SEA69D0223Accident · No injuriesClosedStinson L-5 · Heppner, ORApr 5, 1969 · N1785 · Substantial damageNTSB CHI69F0563Accident · No injuriesClosedCessna 150 · Rockford, ILApr 5, 1969 · N50850 · Substantial damageNTSB ANC69D0130Accident · No injuriesClosedBensen B-8M · Eagle River, AKApr 4, 1969 · N1443 · Substantial damageNTSB FTW69F0436Accident · No injuriesClosedPiper PA-28 · Baton Rouge, LAApr 3, 1969 · N6761J · Substantial damageNTSB MKC69F0401Accident · Serious injuriesClosedCessna 177 · Jennings, KSApr 2, 1969 · N2367Y · Substantial damageNTSB LAX69D0330Accident · No injuriesClosedHughes 269B · Grnd Canyon, AZApr 1, 1969 · N9442F · Substantial damageNTSB DIA69F0170Accident · Minor injuriesClosedPiper PA-28 · Bower, MDApr 1, 1969 · N8564W · Substantial damageNTSB MKC69D0388Accident · No injuriesClosedPiper PA-25 · Colby, KSMar 31, 1969 · N4927Y · Substantial damageNTSB MKC69D0381Accident · No injuriesClosedCessna 150 · Odessa, MOMar 30, 1969 · N6451F · Substantial damageNTSB LAX69A0056Accident · 1 fatalClosedCessna 182A · Chino, CAMar 30, 1969 · N5909B · DestroyedNTSB CHI69D0582Accident · No injuriesClosedCessna 150 · New Market, OHMar 29, 1969 · N60592 · Substantial damageNTSB ANC69D0125Accident · No injuriesClosedAeronca 15AC · Bettles, AKMar 28, 1969 · N1077H · Substantial damageNTSB MKC69D0377Accident · No injuriesClosedCessna 337 · Council Grove, KSMar 27, 1969 · N6313F · Substantial damageNTSB MIA69A0058Accident · No injuriesClosedAero Comdr 560-F · Greenwood, MSMar 27, 1969 · N356R · Substantial damageNTSB LAX69F0301Accident · No injuriesClosedAmer Avco AA-1 · La Verne, CAMar 27, 1969 · N5647L · Substantial damageNTSB MKC69F0395Accident · No injuriesClosedCessna 310F · Columbus, NEMar 25, 1969 · N1298Q · Substantial damage