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 CHI70D0204Accident · No injuriesClosedBeech A-23 · Neenah, WIAug 17, 1969 · N1423L · Substantial damageNTSB OAK70D0083Accident · No injuriesClosedBoeing 75 · Los Banos, CAAug 16, 1969 · N67472 · Substantial damageNTSB LAX70F0080Accident · Serious injuriesClosedPiper PA-28 · El Monte, CAAug 16, 1969 · N95222 · DestroyedNTSB FTW70F0159Accident · Serious injuriesClosedMooney M20B · Quitman, ARAug 16, 1969 · N74575 · DestroyedNTSB CHI70D0132Accident · No injuriesClosedCessna 150 · Grandville, MIAug 16, 1969 · N7947E · Substantial damageNTSB MKC70F0214Accident · Serious injuriesClosedPiper PA-23 · Lake Elmo, MNAug 15, 1969 · N2246P · DestroyedNTSB MIA70D0108Accident · No injuriesClosedCessna 172 · Immokalee, FLAug 15, 1969 · N7550T · Substantial damageNTSB LAX70F0077Accident · No injuriesClosedPiper PA-24 · Frazier Park, CAAug 15, 1969 · N434AJ · Substantial damageNTSB IAD70A0014Accident · No injuriesClosedBeech D18S · Martinsburg, WVAug 15, 1969 · N273C · Substantial damageNTSB CHI70F0255Accident · Minor injuriesClosedPiper PA-28 · Atwater, OHAug 14, 1969 · N1819J · Substantial damageNTSB CHI70F0148Accident · No injuriesClosedChampion 7ECA · Mooresville, INAug 14, 1969 · N5114X · Substantial damageNTSB ANC70F0083Accident · Serious injuriesClosedPiper PA-11S · Willow, AKAug 14, 1969 · N4732M · Substantial damageNTSB MKC70F0197Accident · No injuriesClosedFly-Baby Hughs · Vinland, KSAug 13, 1969 · N74080 · Substantial damageNTSB DEN70A0011Accident · 3 fatalClosedBeech 23 · Colo.Springs, COAug 13, 1969 · N2349J · DestroyedNTSB MIA70F0114Accident · No injuriesClosedGrumman G-164 · Juncos, PRAug 12, 1969 · N10398 · Substantial damageNTSB DEN70A0010Accident · 2 fatalClosedHughes 369HS · American Fork, UTAug 12, 1969 · N9030F · DestroyedNTSB CHI70F0218Accident · No injuriesClosedPiper PA-24 · Valley City, OHAug 12, 1969 · N7119P · Substantial damageNTSB NYC70F0085Accident · Serious injuriesClosedAeronca 7AC · Oxford, NYAug 11, 1969 · N3454E · Substantial damageNTSB MIA70D0200Accident · No injuriesClosedCessna A188 · Louisville, GAAug 11, 1969 · N3532Q · Substantial damageNTSB DEN70F0088Accident · Serious injuriesClosedCessna 170 · Worland, WYAug 10, 1969 · N4242V · DestroyedNTSB DEN70D0095Accident · Minor injuriesClosedPiper PA-22 · Arriba, COAug 10, 1969 · N2477A · DestroyedNTSB SEA70F0121Accident · No injuriesClosedPiper PA-28R · Lake O Woods, ORAug 9, 1969 · N7526J · Substantial damageNTSB NYC70D0114Accident · No injuriesClosedPiper PA-22 · Oxford, PAAug 9, 1969 · N5459Z · Substantial damageNTSB MKC70F0135Accident · No injuriesClosedCessna 180 · Ely, MNAug 9, 1969 · N91261 · DestroyedNTSB MIA70F0147Accident · Minor injuriesClosedCessna 150 · Plymouth, FLAug 9, 1969 · N6521S · Substantial damageNTSB OAK70D0067Accident · Minor injuriesClosedBoeing A75N1 · Kettlman City, CAAug 8, 1969 · N4774V · Substantial damageNTSB FTW70D0118Accident · No injuriesClosedBell 47G-4A · Morgan City, LAAug 7, 1969 · N7818S · Substantial damageNTSB DEN70D0114Accident · No injuriesClosedPiper J3C-65 · Denver, COAug 7, 1969 · N77553 · Substantial damageNTSB NYC70A0020Accident · 1 fatalClosedBeech E35 · Block Island, RIAug 6, 1969 · N8818A · DestroyedNTSB FTW70F0144Accident · Serious injuriesClosedAero Comdr S-2D · Lockport, LAAug 6, 1969 · N1711S · DestroyedNTSB DEN70F0053Accident · Minor injuriesClosedStinson 108-1 · Albuquerque, NMAug 6, 1969 · N9164K · DestroyedNTSB NYC70F0123Accident · Minor injuriesClosedPiper PA-30 · Windsr Locks, CTAug 5, 1969 · N448Q · Substantial damageNTSB MIA70F0286Accident · Serious injuriesClosedNavion A · Savannah, GAAug 4, 1969 · N4208A · Substantial damageNTSB SEA70F0077Accident · Serious injuriesClosedPuss Moth 80A · Tekoa, WAAug 3, 1969 · N223EC · Substantial damageNTSB CHI70F0254Accident · Minor injuriesClosedCessna 172 · Hobart, INAug 3, 1969 · N6654A · Substantial damageNTSB MKC70I0081Incident · No injuriesClosedBell 47G3B1 · Hill City, SDAug 2, 1969 · N7924S · Minor damageNTSB MKC70F0178Accident · No injuriesClosedPiper PA-28 · Macon, MOAug 2, 1969 · N4426J · Substantial damageNTSB MKC70D0166Accident · No injuriesClosedCessna 150 · Maysville, MOAug 2, 1969 · N6519F · Substantial damageNTSB IAD70F0046Accident · Serious injuriesClosedPiper PA-32 · Salisbury, MDAug 2, 1969 · N3257W · DestroyedNTSB DIA70D0037Accident · No injuriesClosedPiper PA-28 · Franklin, NCAug 2, 1969 · N7532J · Substantial damageNTSB MIA70F0080Accident · Minor injuriesClosedPiper J3C · Bentonia, MSAug 1, 1969 · N92191 · Substantial damageNTSB OAK70A0009Accident · Minor injuriesClosedHughes 269B · Foresthill, CAJul 31, 1969 · N9384F · Substantial damageNTSB SEA70D0041Accident · No injuriesClosedWaco UMF · Adrian, ORJul 30, 1969 · N14085 · DestroyedNTSB MKC70D0143Accident · No injuriesClosedBoeing A75 · Hawarden, IAJul 29, 1969 · N54545 · Substantial damageNTSB LAX70F0230Accident · 2 fatalClosedBell 206A · Grnd Canyon, AZJul 29, 1969 · N4057G · DestroyedNTSB OAK70D0064Accident · No injuriesClosedCessna 150 · Los Banos, CAJul 28, 1969 · N6416F · Substantial damageNTSB CHI70D0136Accident · No injuriesClosedPiper J3C-65 · Midland, MIJul 28, 1969 · N98045 · Substantial damageNTSB FTW70D0139Accident · Minor injuriesClosedPiper PA-22 · Freer, TXJul 27, 1969 · N9589D · Substantial damageNTSB CHI70A0011Accident · 2 fatalClosedCessna 177 · Northfield, OHJul 27, 1969 · N29555 · DestroyedNTSB OAK70F0142Accident · Serious injuriesClosedPiper PA-28R · Sacramento, CAJul 26, 1969 · N7580J · Destroyed