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 SEA72FYC15Accident · No injuriesClosedCessna A188 · St.Johns, ORAug 2, 1971 · N8073V · Substantial damageNTSB SEA72DYC09Accident · No injuriesClosedChampion 7FC · Sandy, ORJul 25, 1971 · N9051B · Substantial damageNTSB SEA72DYC05Accident · No injuriesClosedPiper J3C-65 · Beaverton, ORJul 5, 1971 · N33551 · Substantial damageNTSB SEA71FYC43Accident · No injuriesClosedCessna 182B · Enterprise, ORJun 12, 1971 · N2513G · Substantial damageNTSB SEA71DVQ22Accident · No injuriesClosedBell 47G3B · Days Creek, ORApr 7, 1971 · N8455E · Substantial damageNTSB SEA71DWA31Accident · No injuriesClosedPiper PA-32 · Condon, ORFeb 25, 1971 · N3623W · Substantial damageNTSB SEA71DWA18Accident · Minor injuriesClosedCessna 150 · Gaston, ORAug 31, 1970 · N60306 · Substantial damageNTSB SEA71DVQ06Accident · No injuriesClosedCessna 150 · Christmas V, ORAug 8, 1970 · N4024J · Substantial damageNTSB SEA71DWA03Accident · No injuriesClosedPiper PA-24 · Cove, ORJul 5, 1970 · N6256P · Substantial damageNTSB SEA71DWA02Accident · Minor injuriesClosedPiper PA-28 · Halfway, ORJul 2, 1970 · N7049R · Substantial damageNTSB SEA70D0148Accident · No injuriesClosedStinson SR-7C · Troutdale, OROct 12, 1969 · N16106 · Substantial damageNTSB SEA70D0009Accident · No injuriesClosedChampion 7EC · Eagle Point, ORJun 2, 1969 · N7547E · Substantial damageNTSB SEA70D0010Accident · No injuriesClosedCessna 180H · Days Creek, ORMay 18, 1969 · N3480Y · Substantial damageNTSB SEA69D0188Accident · No injuriesClosedPiper PA-28 · Astoria, ORJan 25, 1969 · N7052R · Substantial damageNTSB SEA69F0147Accident · No injuriesClosedMooney M-18LA · Mount Angel, OROct 16, 1968 · N4097 · Substantial damageNTSB SEA69I0023Incident · No injuriesClosedPiper PA-31 · Portland, OROct 8, 1968 · N1132 · Minor damageNTSB SEA69F0143Accident · No injuriesClosedCessna 185 · Portland, ORAug 9, 1968 · N9808X · Substantial damageNTSB SEA69D0054Accident · No injuriesClosedNo.American P-51 · Medford, ORJul 7, 1968 · N66111 · DestroyedNTSB SEA68D0311Accident · No injuriesClosedHughes 269B · Tigard, ORApr 20, 1968 · N9477F · Substantial damageNTSB SEA68D0280Accident · No injuriesClosedCulver LFA · Eugene, ORMar 16, 1968 · N41722 · Substantial damageNTSB SEA68F0228Accident · No injuriesClosedCessna 172 · Yamhill, OROct 16, 1967 · N1621F · Substantial damageNTSB SEA68D0168Accident · No injuriesClosedCessna 310 · Aurora, ORAug 12, 1967 · N1110Q · Substantial damageNTSB SEA68D0087Accident · No injuriesClosedMooney M20E · Silverton, ORJul 24, 1967 · N5783Q · Substantial damageNTSB SEA68D0118Accident · No injuriesClosedCessna 150F · Sutherlin, ORJul 16, 1967 · N6533F · Substantial damageNTSB SEA67F0299Accident · No injuriesClosedCessna 172G · Sutherlin, ORApr 17, 1967 · N3635L · Substantial damageNTSB SEA67A0049Accident · No injuriesClosedHughes 269A · Hillsboro, ORMar 12, 1967 · N8729F · Substantial damageNTSB SEA68F0009Accident · No injuriesClosedPiper PA-23 · Scappoose, ORMar 7, 1967 · N3171P · Substantial damageNTSB SEA67D0279Accident · No injuriesClosedPiper PA-22 · Silver Lake, ORFeb 21, 1967 · N8276C · Substantial damageNTSB SEA67A0015Accident · Serious injuriesClosedDouglas A-26B · La Grande, ORAug 18, 1966 · N7719C · Substantial damageNTSB SEA67A0012Accident · No injuriesClosedPiper PA-28 · Mitchell, ORAug 7, 1966 · N7409W · Substantial damageNTSB SEA67A0010Accident · Minor injuriesClosedHughes 269B · Eaverton, ORAug 7, 1966 · N9427F · Substantial damageNTSB SEA67A0005Accident · No injuriesClosedBell 47G-3B · Lakeview, ORJul 19, 1966 · N8471E · Substantial damageNTSB SEA67F0083Accident · No injuriesClosedCessna 172 · Madras, ORJul 3, 1966 · N5784R · Substantial damageNTSB SEA67D0074Accident · No injuriesClosedCessna 182 · Frenchglen, ORJun 12, 1966 · N6462A · Substantial damageNTSB SEA67F0007Accident · No injuriesClosedCessna 140 · Medford, ORApr 27, 1966 · N2172V · Substantial damageNTSB SEA66D0292Accident · No injuriesClosedCessna 140 · Paisley, ORApr 15, 1966 · N3713V · Substantial damageNTSB SEA66F0297Accident · No injuriesClosedGlobe Swift GC-1B · Mulino, ORApr 6, 1966 · N3366K · Substantial damageNTSB SEA66D0206Accident · No injuriesClosedCessna 150 · Scappoose, ORJan 18, 1966 · N7024X · Substantial damageNTSB SEA66A0039Accident · 1 fatalClosedCessna 170B · Pendleton, ORDec 30, 1965 · N2382D · Substantial damageNTSB SEA66F0194Accident · Minor injuriesClosedStinson 108-1 · Springfield, ORDec 28, 1965 · N8641K · Substantial damageNTSB SEA66F0164Accident · Minor injuriesClosedCessna 172F · Springfield, ORNov 4, 1965 · N8467U · Substantial damageNTSB SEA66F0116Accident · No injuriesClosedCessna 180 · Prairie City, ORAug 22, 1965 · N2956C · DestroyedNTSB SEA66A0015Accident · No injuriesClosedHughes 269A · Delake, ORAug 15, 1965 · N8762F · Substantial damageNTSB UNK65X0103Accident · No injuriesClosedStinson 1083 · Burns, OROct 1, 1964 · N6804M · Substantial damageNTSB UNK64X2448Accident · No injuriesClosedPiper PA22 · Bend, ORJun 23, 1964 · N3316B · Substantial damageNTSB UNK64X2378Accident · No injuriesClosedPiper PA24 · Aurora, ORJun 19, 1964 · N6297P · Substantial damageNTSB UNK64X2336Accident · No injuriesClosedCessna 310 · Mcnary, ORJun 17, 1964 · N3045D · Substantial damageNTSB UNK64X1554Accident · No injuriesClosedCessna 170B · Prineville, ORMay 2, 1964 · N2850C · Substantial damageNTSB UNK64X1279Accident · No injuriesClosedCessna 140A · Burns, ORApr 12, 1964 · N5309C · Substantial damageNTSB SEA62A0003Accident · No injuriesClosedBoeing 720 · Portland, ORFeb 1, 1962 · N7206U · Substantial damage