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 OAK70D0115Accident · No injuriesClosedLuscombe 8E · Antioch, CASep 6, 1969 · N1197B · Substantial damageNTSB DEN70D0116Accident · No injuriesClosedCessna 150F · Calhan, COSep 6, 1969 · N7808F · Substantial damageNTSB CHI70D0227Accident · No injuriesClosedLuscombe 8A · Gobles, MISep 6, 1969 · N1160K · Substantial damageNTSB DEN70A0018Accident · 4 fatalClosedBeech K35 · Provo, UTSep 5, 1969 · N524T · DestroyedNTSB CHI70F0197Accident · No injuriesClosedJeane Tenie · Lafayette, INSep 5, 1969 · N8513 · Substantial damageNTSB LAX70F0114Accident · No injuriesClosedLuscombe 8A · Pacoima, CASep 4, 1969 · N72017 · Substantial damageNTSB IAD70F0082Accident · 1 fatalClosedPiper PA-18A · Browns Store, VASep 4, 1969 · N8158C · DestroyedNTSB DEN70F0100Accident · No injuriesClosedBell 47J2B · Santa Fe, NMSep 3, 1969 · N6224N · Substantial damageNTSB OAK70F0103Accident · Serious injuriesClosedStinson 108-2 · Stockton, CASep 1, 1969 · N9305K · Substantial damageNTSB MIA70F0152Accident · Minor injuriesClosedLuscombe 8A · Melbourne, FLSep 1, 1969 · N1251K · DestroyedNTSB CHI70F0256Accident · No injuriesClosedBeech 35 · Mcleansboro, ILSep 1, 1969 · N3048V · Substantial damageNTSB CHI70F0228Accident · Serious injuriesClosedPiper PA-16 · Sidney, OHSep 1, 1969 · N6868K · DestroyedNTSB MKC70F0221Accident · No injuriesClosedPiper PA-28 · Blue Earth, MNAug 31, 1969 · N7193R · Substantial damageNTSB MKC70F0182Accident · Minor injuriesClosedPiper PA-28 · Cottonwood, SDAug 31, 1969 · N7875W · Substantial damageNTSB MIA70F0154Accident · No injuriesClosedChampion 7FC · Merritt Is, FLAug 31, 1969 · N9883B · Substantial damageNTSB OAK70D0128Accident · No injuriesClosedBoeing A75 · Castroville, CAAug 30, 1969 · N4767V · Substantial damageNTSB MKC70F0207Accident · No injuriesClosedCessna 150H · Ft Leonard Wd, MOAug 30, 1969 · N22062 · Substantial damageNTSB MIA70F0131Accident · No injuriesClosedErcoupe 415-D · Ft Lauderdale, FLAug 30, 1969 · N3271H · Substantial damageNTSB CHI70F0333Accident · Minor injuriesClosedPiper PA-24 · Sextonville, WIAug 30, 1969 · N7797P · Substantial damageNTSB OAK70D0102Accident · Minor injuriesClosedPiper PA-18A · Livermore, CAAug 29, 1969 · N3689P · Substantial damageNTSB MKC70D0155Accident · No injuriesClosedGrumman G-164 · Johnson, KSAug 29, 1969 · N518Y · Substantial damageNTSB LAX70F0135Accident · Minor injuriesClosedPiper PA-32 · Ontario, CAAug 29, 1969 · N4272R · Substantial damageNTSB ANC70A0023Accident · No injuriesClosedBell 47G-2 · Prudhoe Bay, AKAug 27, 1969 · N6708D · Substantial damageNTSB LAX70D0113Accident · No injuriesClosedPiper PA-24 · Marble Cnon, AZAug 26, 1969 · N6408P · Substantial damageNTSB IAD70D0054Accident · Serious injuriesClosedPiper PA-28 · Robbinsville, NCAug 26, 1969 · N8099N · Substantial damageNTSB CHI70F0236Accident · No injuriesClosedCessna 150 · Kansasville, WIAug 26, 1969 · N3906J · Substantial damageNTSB MIA70F0105Accident · Minor injuriesClosedCessna 188 · Dubbs, MSAug 25, 1969 · N9877V · Substantial damageNTSB NYC70D0135Accident · No injuriesClosedCessna 177 · Westfield, NYAug 24, 1969 · N3439T · Substantial damageNTSB NYC70A0027Accident · 1 fatalClosedPiper PA-22 · Langhorne, PAAug 24, 1969 · N3339Z · DestroyedNTSB SEA70F0089Accident · Minor injuriesClosedBullock Curtis · Spokane, WAAug 23, 1969 · N5704N · Substantial damageNTSB OAK70D0095Accident · Minor injuriesClosedCessna 150 · Visalia, CAAug 23, 1969 · N8640S · Substantial damageNTSB NYC70F0091Accident · Minor injuriesClosedLuscombe 8E · Basking Ridge, NJAug 23, 1969 · N2646K · Substantial damageNTSB NYC70A0026Accident · 1 fatalClosedPiper J-5A · Kittanning, PAAug 23, 1969 · N38649 · DestroyedNTSB LAX70F0116Accident · No injuriesClosedCessna 140 · Chino, CAAug 23, 1969 · N77119 · Substantial damageNTSB LAX70F0107Accident · Serious injuriesClosedLuscombe 8A · Apple Valley, CAAug 23, 1969 · N1124K · Substantial damageNTSB DEN70D0146Accident · No injuriesClosedBell 47J-2A · Montrose, COAug 23, 1969 · N73202 · Substantial damageNTSB NYC70F0126Accident · Minor injuriesClosedCessna 150D · Yulan, NYAug 22, 1969 · N4468U · Substantial damageNTSB LAX70D0112Accident · No injuriesClosedCessna T337C · Wickenburg, AZAug 22, 1969 · N2623S · Substantial damageNTSB CHI70F0211Accident · No injuriesClosedBeech K35 · Ringwood, ILAug 22, 1969 · N818R · DestroyedNTSB OAK70D0090Accident · No injuriesClosedPiper PA-25 · Visalia, CAAug 21, 1969 · N6356Z · DestroyedNTSB NYC70F0134Accident · No injuriesClosedBell 47G · Worthington, MAAug 21, 1969 · N54047 · Substantial damageNTSB FTW70F0185Accident · No injuriesClosedBoeing E75N1 · Katy, TXAug 21, 1969 · N4918N · Substantial damageNTSB CHI70F0252Accident · Minor injuriesClosedBeech B35 · Birchwood, WIAug 20, 1969 · N5200C · Substantial damageNTSB MIA70A0013Accident · 1 fatalClosedMooney M20E · Whitmire, SCAug 19, 1969 · N79353 · DestroyedNTSB SEA70F0057Accident · Minor injuriesClosedStinson 108-2 · Donnelly, IDAug 18, 1969 · N9664K · Substantial damageNTSB IAD70F0058Accident · No injuriesClosedPiper PA-28 · Charlotte, NCAug 18, 1969 · N5258W · Substantial damageNTSB FTW70D0193Accident · No injuriesClosedGrumman G-164A · Garwood, TXAug 18, 1969 · N5295 · Substantial damageNTSB ANC70A0033Accident · No injuriesClosedHiller Acft US-12E · Kenai, AKAug 18, 1969 · N5384V · Substantial damageNTSB SEA70D0087Accident · No injuriesClosedErco 415C · Canby, ORAug 17, 1969 · N3286H · Substantial damageNTSB LAX70D0076Accident · No injuriesClosedBoeing PT-17 · Somerton, AZAug 17, 1969 · N48573 · Destroyed