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 FTW68F0256Accident · No injuriesClosedCallair A-9 · Mcgehee, ARAug 26, 1967 · N8249H · Substantial damageNTSB FTW68F0212Accident · Minor injuriesClosedPiper PA-22 · Arlington, TXAug 26, 1967 · N1629P · Substantial damageNTSB SEA68F0126Accident · No injuriesClosedCessna 185 · Pendleton, ORAug 25, 1967 · N9931X · Substantial damageNTSB MKC68F0211Accident · No injuriesClosedPiper PA-25 · Cozad, NEAug 25, 1967 · N6914Z · Substantial damageNTSB CHI68F0282Accident · Minor injuriesClosedStinson 108-1 · Hidalgo, ILAug 25, 1967 · N8290K · Substantial damageNTSB SEA68D0156Accident · No injuriesClosedMaule M-4 · Moore, MTAug 24, 1967 · N12010 · Substantial damageNTSB MKC68F0208Accident · No injuriesClosedPiper PA-14 · Bemidji, MNAug 24, 1967 · N5158H · Substantial damageNTSB SEA68D0100Accident · No injuriesClosedGrumman TBM · Grangeville, IDAug 23, 1967 · N68683 · Substantial damageNTSB LAX68A0024Accident · No injuriesClosedBell 47G-2 · Hntngtn Bch, CAAug 23, 1967 · N6720D · Substantial damageNTSB DEN68D0100Accident · No injuriesClosedAeronca 7GCBC · Ten Sleep, WYAug 23, 1967 · N9698S · Substantial damageNTSB OAK68A0016Accident · 1 fatalClosedPiper PA-25 · Gustine, CAAug 22, 1967 · N7122Z · DestroyedNTSB MIA68D0130Accident · Minor injuriesClosedPiper PA-25 · Coffeeville, MSAug 22, 1967 · N6967Z · DestroyedNTSB FTW68F0157Accident · No injuriesClosedLuscombe 8C · Norman, OKAug 22, 1967 · N2863K · Substantial damageNTSB MKC68A0012Accident · Minor injuriesClosedCessna 182H · Leawood, KSAug 21, 1967 · N3459S · Substantial damageNTSB LAX68F0099Accident · Minor injuriesClosedErco 415-C · Colton, CAAug 21, 1967 · N93585 · DestroyedNTSB FTW68D0173Accident · No injuriesClosedCessna 150 · Abilene, TXAug 21, 1967 · N8726G · DestroyedNTSB DIA68D0063Accident · No injuriesClosedCessna 150 · Forest Hill, WVAug 21, 1967 · N2668J · Substantial damageNTSB SEA68D0108Accident · No injuriesClosedPiper PA-24 · Gannett, IDAug 20, 1967 · N6319P · Substantial damageNTSB SEA68A0019Accident · 3 fatalClosedMooney M20F · Albany, ORAug 20, 1967 · N2979L · DestroyedNTSB OAK68D0095Accident · No injuriesClosedGrumman G-164A · Woodlake, CAAug 20, 1967 · N667U · Substantial damageNTSB NYC68D0118Accident · No injuriesClosedCessna 172 · New Hope, PAAug 19, 1967 · N7393T · Substantial damageNTSB FTW68D0187Accident · No injuriesClosedMooney M20E · Big Spring, TXAug 19, 1967 · N5514Q · Substantial damageNTSB LAX68A0023Accident · 4 fatalClosedCessna 172 · San Diego, CAAug 18, 1967 · N7937B · DestroyedNTSB LAX68A0021Accident · No injuriesClosedBell 47D1 · Buttonwilow, CAAug 18, 1967 · N10190 · Substantial damageNTSB DEN68F0079Accident · Minor injuriesClosedPiper PA-24 · Denver, COAug 18, 1967 · N5985P · Substantial damageNTSB CHI68A0019Accident · 1 fatalClosedPiper J3C-65 · Bloomfield, OHAug 18, 1967 · N92605 · DestroyedNTSB ANC68D0038Accident · No injuriesClosedAeronca 7AC · Gakona, AKAug 18, 1967 · N1949E · Substantial damageNTSB SEA68D0172Accident · No injuriesClosedPiper PA-24 · Klamath Fall, ORAug 17, 1967 · N6097P · Substantial damageNTSB OAK68D0117Accident · No injuriesClosedCessna 150 · San Luis Ob, CAAug 17, 1967 · N8406G · Substantial damageNTSB FTW68D0186Accident · No injuriesClosedCessna 140 · Menard, TXAug 17, 1967 · N2020V · Substantial damageNTSB CHI68A0018Accident · 1 fatalClosedPiper J3 · Medina, OHAug 16, 1967 · N98231 · DestroyedNTSB ANC68A0010Accident · 1 fatalClosedCessna 180A · Talkeetna, AKAug 15, 1967 · N9509B · DestroyedNTSB MKC68F0158Accident · Serious injuriesClosedPiper PA-28 · Napoleon, NDAug 14, 1967 · N8727W · Substantial damageNTSB FTW68F0255Accident · No injuriesClosedGrumman G-164 · Eudora, ARAug 14, 1967 · N651Y · Substantial damageNTSB MKC68D0154Accident · No injuriesClosedPiper PA-30 · Waterloo, IAAug 13, 1967 · N8306Y · Substantial damageNTSB CHI68A0017Accident · No injuriesClosedRussell CGI · Three Rivers, MIAug 13, 1967 · N7540U · Substantial damageNTSB ANC68F0065Accident · No injuriesClosedCessna TU206 · Fairbanks, AKAug 13, 1967 · N3913G · Substantial damageNTSB OAK68D0276Accident · Minor injuriesClosedPiper PA-25 · Davis, CAAug 12, 1967 · N6487Z · DestroyedNTSB OAK68D0213Accident · No injuriesClosedCessna 172 · Franklin, CAAug 12, 1967 · N6825X · DestroyedNTSB NYC68F0109Accident · Minor injuriesClosedCherokee II · Hershey, PAAug 12, 1967 · N10342 · Substantial damageNTSB FTW68F0165Accident · No injuriesClosedCessna 150F · Crane, TXAug 12, 1967 · N8035F · Substantial damageNTSB DEN68F0068Accident · No injuriesClosedBeech T-34A · Albuquerque, NMAug 12, 1967 · N421NM · Substantial damageNTSB SEA68F0161Accident · Serious injuriesClosedWaco UKC · Monroe, WAAug 11, 1967 · N16967 · Substantial damageNTSB MKC68F0278Accident · Minor injuriesClosedAero Comdr 200-E · Augusta, KSAug 11, 1967 · N4001X · Substantial damageNTSB CHI68F0285Accident · No injuriesClosedMooney M20E · Holland, MIAug 11, 1967 · N6943U · Substantial damageNTSB MIA68F0128Accident · No injuriesClosedAeronca 7AC · Baldwyn, MSAug 10, 1967 · N82556 · Substantial damageNTSB CHI68F0277Accident · Serious injuriesClosedMael RC-3 · Negaunee, MIAug 10, 1967 · CF-UYA · Substantial damageNTSB LAX68D0101Accident · Minor injuriesClosedCallair A-5 · Maricopa, AZAug 9, 1967 · N6151C · DestroyedNTSB DIA68F0061Accident · Minor injuriesClosedPiper PA-28 · Laurel, MDAug 9, 1967 · N9399J · Substantial damageNTSB ANC68F0033Accident · No injuriesClosedAeronca 65-CA · Anchorage, AKAug 7, 1967 · N33765 · Substantial damage