NTSB investigations, OR
695 cases where the phase of flight was Enroute.
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 = 400)
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.
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 = 400)
ORDER BY c."eventDate" DESC, c."ntsbNumber" DESCRun it at the DuckDB prompt, or in any tool that reads Parquet over HTTPS. More ways to query the release are on Use the data.
NTSB SEA80FYM06Accident · Minor injuriesClosedCessna 152 · Newberg, ORNTSB SEA80FYM04Accident · No injuriesClosedBell 206A · Amity, ORNTSB SEA80DYK01Accident · No injuriesClosedCessna TU206 · Klamath Falls, ORNTSB SEA79FYM68Accident · No injuriesClosedCessna 172N · Clatskanie, ORNTSB SEA79AA058Accident · 12 fatalClosedDouglas DC-7C · Klamath Falls, ORNTSB SEA79FYK31Accident · Minor injuriesClosedM.B.B. BO-209 · Grants Pass, ORNTSB SEA79FYK29Accident · Minor injuriesClosedCessna 150F · Cottage Grove, ORNTSB SEA79FA054Accident · 3 fatalClosedMooney M20C · Newberg, ORNTSB SEA79FA053Accident · 4 fatalClosedPiper PA-28 · Paisley, ORNTSB SEA79FYK27Accident · Serious injuriesClosedCessna 177RG · Redmond, ORNTSB SEA79FYK24Accident · No injuriesClosedCessna A185F · Eugene, ORNTSB SEA79FYM58Accident · No injuriesClosedPiper PA-18 · Heppner, ORNTSB SEA79FYM57Accident · Serious injuriesClosedAero Comdr S-2R · Hermiston, ORNTSB SEA79DYM55Accident · Minor injuriesClosedHiller Acft UH-12E · Warm Spgs Res, ORNTSB SEA80FYM54Accident · Minor injuriesClosedHughes 369D · Bay City, ORNTSB SEA79DYM53Accident · No injuriesClosedCessna 150 · Pilot Rock, ORNTSB SEA79FYM52Accident · Serious injuriesClosedCessna 150 · Sisters, ORNTSB SEA79FYM51Accident · Minor injuriesClosedBell 47G · Embler, ORNTSB SEA79FYM48Accident · No injuriesClosedGrumman G-164A · Hermiston, ORNTSB SEA79FYM41Accident · No injuriesClosedCessna 182 · Wilsonville, ORNTSB SEA79DYK13Accident · Minor injuriesClosedStinson 108-3 · Ashland, ORNTSB SEA79FYM31Accident · Serious injuriesClosedBaird/Beers AMFS14 · Mcminnville, ORNTSB SEA79FYK10Accident · Minor injuriesClosedPiper PA-24 · Klamath Falls, ORNTSB SEA79FA032Accident · 1 fatalClosedCessna 150 · John Day, ORNTSB SEA79FYM30Accident · Serious injuriesClosedPiper PA-28 · Sandy, ORNTSB SEA79FYM25Accident · Serious injuriesClosedCessna 172N · Pendleton, ORNTSB SEA79FYM19Accident · No injuriesClosedCessna T210N · John Day, ORNTSB SEA79DYK04Accident · No injuriesClosedCessna 152 · Burns, ORNTSB DCA79AA005Accident · 10 fatalClosedDouglas DC-8 · Portland, ORNTSB SEA79FYM08Accident · No injuriesClosedHiller Acft Soloy · Lacomb, ORNTSB SEA79FA017Accident · 1 fatalClosedCessna 150K · Astoria, ORNTSB SEA79FA016Accident · 1 fatalClosedPiper PA-14 · Eugene, ORNTSB SEA79FYK03Accident · Serious injuriesClosedCessna 210 · Triangle Lake, ORNTSB SEA79FA010Accident · 3 fatalClosedPiper PA-20 · Joseph, ORNTSB SEA78FA065Accident · 1 fatalClosedPiper PA-28 · Rainier, ORNTSB SEA78DYK22Accident · No injuriesClosedPiper PA-28 · Sun River, ORNTSB SEA78FYK21Accident · No injuriesClosedCessna 182B · Azalea, ORNTSB SEA78FA063Accident · 4 fatalClosedPiper PA-28 · Sisters, ORNTSB SEA78FA056Accident · 2 fatalClosedCessna 172XP · Eugene, ORNTSB SEA78FA055Accident · 3 fatalClosedAero Comdr 100 · Bly, ORNTSB SEA78FA039Accident · 4 fatalClosedPiper PA-28R · Baker, ORNTSB SEA78FA038Accident · 1 fatalClosedNavion A · Ashland, ORNTSB SEA78FYM39Accident · 1 fatalClosedBoeing E75N1 · John Day, ORNTSB SEA78DYK18Accident · Minor injuriesClosedCessna 150 · Millican, ORNTSB SEA78FYM28Accident · Serious injuriesClosedBeech F33A · La Grande, ORNTSB SEA78FA025Accident · 1 fatalClosedCessna 150L · Junction City, ORNTSB SEA78DYK11Accident · No injuriesClosedBell 47G3B1 · Myrtle Creek, ORNTSB SEA78FYK08Accident · Serious injuriesClosedPiper PA-18 · Ashland, ORNTSB SEA78FA019Accident · 4 fatalClosedBeech 35 · Cascade Locks, ORNTSB SEA78FYM16Accident · No injuriesClosedHiller UH-12E · Cannon Beach, OR