NTSB investigations, OR

250 cases where the phase of flight was Takeoff.

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.\"eventStateOrRegion\" = 'OR' AND c.\"eventCountry\" = 'USA'
    AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 300)
  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 SEA81DYK12Accident · No injuriesClosedCessna 180 · Florence, ORJul 12, 1981 · N7964V · Substantial damageNTSB SEA80DYM55Accident · No injuriesClosedHiller Acft UH-12E · Tillamook, ORAug 19, 1980 · N59366 · Substantial damageNTSB SEA80DYM35Accident · No injuriesClosedStinson 108-3 · Dayton, ORMay 17, 1980 · N20Q · Substantial damageNTSB SEA80DYK08Accident · Minor injuriesClosedBell 214B-1 · Bend, ORMay 1, 1980 · N224RM · Substantial damageNTSB SEA80DYK06Accident · Minor injuriesClosedErco 415-C · Glide, ORApr 12, 1980 · N2957H · DestroyedNTSB SEA80DYM22Accident · No injuriesClosedPiper PA-18 · Maupin, ORMar 16, 1980 · N7600B · Substantial damageNTSB SEA79DYM40Accident · No injuriesClosedBellanca 8GCBC · The Dalles, ORJun 14, 1979 · N4143Y · Substantial damageNTSB SEA79DXK12Accident · No injuriesClosedAmer Avco AA1-B · Chiloquin, ORMay 20, 1979 · N9876L · Substantial damageNTSB SEA79FYM06Accident · No injuriesClosedHiller Acft UH-12E · Salem, ORNov 9, 1978 · N6ZH · Substantial damageNTSB SEA78FYM51Accident · Minor injuriesClosedCessna 182 · Oregon City, ORSep 18, 1978 · N4854D · Substantial damageNTSB SEA78FYK17Accident · No injuriesClosedSnow 600S2D · Malin, ORJun 2, 1978 · N1726S · Substantial damageNTSB SEA78FYM33Accident · Serious injuriesClosedBell 206B · Tillamook, ORMay 15, 1978 · N4ZH · DestroyedNTSB SEA78FYM32Accident · No injuriesClosedS.N.I.A.S. SA316B · Portland, ORMay 11, 1978 · N5954 · Substantial damageNTSB SEA78DYM20Accident · No injuriesClosedCessna 150G · Aurora, ORMar 13, 1978 · N6699G · Substantial damageNTSB SEA78DYK01Accident · No injuriesClosedPiper PA-32R · Klamath Falls, OROct 22, 1977 · N812PC · Substantial damageNTSB SEA78FYM04Accident · Minor injuriesClosedErco 415-C · Scappoose, OROct 5, 1977 · N2645H · Substantial damageNTSB SEA77DYK21Accident · No injuriesClosedPiper PA-28 · Riley, ORAug 13, 1977 · N7078W · Substantial damageNTSB SEA77DYM50Accident · No injuriesClosedCessna 140 · Rufus, ORJul 28, 1977 · N89388 · DestroyedNTSB SEA77FYM53Accident · No injuriesClosedSchweizer 2-33A · North Plains, ORJul 23, 1977 · N5759S · Substantial damageNTSB SEA77DYK19Accident · No injuriesClosedBlanik L-13 · Sisters, ORJul 22, 1977 · N90846 · Substantial damageNTSB SEA77DYM47Accident · No injuriesClosedPiper PA-28 · Wakonda Beach, ORJul 21, 1977 · N33346 · Substantial damageNTSB SEA77FYM13Accident · No injuriesClosedPiper PA-24 · Yamhill, ORJan 8, 1977 · N7364H · Substantial damageNTSB SEA76DYC69Accident · No injuriesClosedChampion 7ECA · Wallowa, ORSep 2, 1976 · N11063 · Substantial damageNTSB SEA76FYC54Accident · Serious injuriesClosedPiper PA-25 · Rickreall, ORMay 26, 1976 · N7436Z · Substantial damageNTSB SEA76FYC49Accident · Minor injuriesClosedEnstrom F-28A · Prairie City, ORMay 17, 1976 · N530H · Substantial damageNTSB SEA76FYC38Accident · Minor injuriesClosedHiller Acft UH-12E · Corvallis, ORApr 4, 1976 · N5ZH · Substantial damageNTSB SEA76DYC32Accident · No injuriesClosedBell 206B · Lagrande, ORFeb 21, 1976 · N96117 · Substantial damageNTSB SEA76DYA16Accident · No injuriesClosedCessna 172 · Ontario, ORJan 27, 1976 · N1581V · Substantial damageNTSB SEA76DYI10Accident · No injuriesClosedCessna 320F · Riley Junction, OROct 17, 1975 · N6163Q · Substantial damageNTSB SEA76FYC20Accident · No injuriesClosedHiller Acft UH-12B · Brownsville, ORSep 20, 1975 · N8108H · Substantial damageNTSB SEA76DYI06Accident · No injuriesClosedPiper PA-32 · Redmond, ORSep 1, 1975 · N4092R · Substantial damageNTSB SEA76DYC14Accident · No injuriesClosedPiper PA-32 · Enterprise, ORSep 1, 1975 · N3507W · Substantial damageNTSB SEA75DYI26Accident · No injuriesClosedMooney M20C · Norway, ORMay 28, 1975 · N3445X · Substantial damageNTSB SEA75DYI24Accident · No injuriesClosedCessna 170B · Medford, ORMay 26, 1975 · N2639D · Substantial damageNTSB UNK75X0106Accident · No injuriesClosedCessna 150 · Chemult, ORApr 30, 1975 · N51321 · Substantial damageNTSB SEA75FYC27Accident · No injuriesClosedCessna A188B · Independence, ORFeb 21, 1975 · N86448 · Substantial damageNTSB SEA75DYC25Accident · No injuriesClosedCessna 180 · Wamic, ORJan 27, 1975 · N370G · Substantial damageNTSB SEA75DYI19Accident · No injuriesClosedCessna 177 · Sunriver, ORJan 1, 1975 · N35092 · Substantial damageNTSB SEA75DYC19Accident · Minor injuriesClosedBeech E35 · Lagrande, OROct 10, 1974 · N3212C · Substantial damageNTSB SEA75DYI14Accident · Minor injuriesClosedBeech 19 · Burns, OROct 6, 1974 · N1960W · Substantial damageNTSB SEA75DYC01Accident · No injuriesClosedAmer Avco AA1-A · Sandy, ORJul 3, 1974 · N6368L · Substantial damageNTSB SEA74DYI01Accident · No injuriesClosedHiller FH1100 · Cave Jctn, ORJul 9, 1973 · N427FH · Substantial damageNTSB SEA73DYC34Accident · No injuriesClosedPiper PA-12 · Sandy, ORJun 6, 1973 · N7754H · Substantial damageNTSB SEA73DYC25Accident · No injuriesClosedStinson 108 · The Dalles, ORApr 21, 1973 · N9019K · Substantial damageNTSB SEA73DYI07Accident · No injuriesClosedLuscombe 8A · Coos Bay, ORNov 17, 1972 · N45493 · Substantial damageNTSB SEA73DYI05Accident · No injuriesClosedAero Comdr 100 · Burns, OROct 7, 1972 · N4115X · Substantial damageNTSB SEA72DVC43Accident · No injuriesClosedPiper PA-24 · Lyons, ORMay 18, 1972 · N5719P · Substantial damageNTSB SEA72FYC39Accident · No injuriesClosedMooney M20F · Tigard, ORMay 3, 1972 · N9590M · Substantial damageNTSB SEA72FYC33Accident · Serious injuriesClosedBell 47G-5 · Albany, ORApr 19, 1972 · N1420 · DestroyedNTSB SEA72FYC31Accident · No injuriesClosedCessna 170 · Donald, ORMar 28, 1972 · N2772D · Substantial damage