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.
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" 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 SEA81DYK12Accident · No injuriesClosedCessna 180 · Florence, ORNTSB SEA80DYM55Accident · No injuriesClosedHiller Acft UH-12E · Tillamook, ORNTSB SEA80DYM35Accident · No injuriesClosedStinson 108-3 · Dayton, ORNTSB SEA80DYK08Accident · Minor injuriesClosedBell 214B-1 · Bend, ORNTSB SEA80DYK06Accident · Minor injuriesClosedErco 415-C · Glide, ORNTSB SEA80DYM22Accident · No injuriesClosedPiper PA-18 · Maupin, ORNTSB SEA79DYM40Accident · No injuriesClosedBellanca 8GCBC · The Dalles, ORNTSB SEA79DXK12Accident · No injuriesClosedAmer Avco AA1-B · Chiloquin, ORNTSB SEA79FYM06Accident · No injuriesClosedHiller Acft UH-12E · Salem, ORNTSB SEA78FYM51Accident · Minor injuriesClosedCessna 182 · Oregon City, ORNTSB SEA78FYK17Accident · No injuriesClosedSnow 600S2D · Malin, ORNTSB SEA78FYM33Accident · Serious injuriesClosedBell 206B · Tillamook, ORNTSB SEA78FYM32Accident · No injuriesClosedS.N.I.A.S. SA316B · Portland, ORNTSB SEA78DYM20Accident · No injuriesClosedCessna 150G · Aurora, ORNTSB SEA78DYK01Accident · No injuriesClosedPiper PA-32R · Klamath Falls, ORNTSB SEA78FYM04Accident · Minor injuriesClosedErco 415-C · Scappoose, ORNTSB SEA77DYK21Accident · No injuriesClosedPiper PA-28 · Riley, ORNTSB SEA77DYM50Accident · No injuriesClosedCessna 140 · Rufus, ORNTSB SEA77FYM53Accident · No injuriesClosedSchweizer 2-33A · North Plains, ORNTSB SEA77DYK19Accident · No injuriesClosedBlanik L-13 · Sisters, ORNTSB SEA77DYM47Accident · No injuriesClosedPiper PA-28 · Wakonda Beach, ORNTSB SEA77FYM13Accident · No injuriesClosedPiper PA-24 · Yamhill, ORNTSB SEA76DYC69Accident · No injuriesClosedChampion 7ECA · Wallowa, ORNTSB SEA76FYC54Accident · Serious injuriesClosedPiper PA-25 · Rickreall, ORNTSB SEA76FYC49Accident · Minor injuriesClosedEnstrom F-28A · Prairie City, ORNTSB SEA76FYC38Accident · Minor injuriesClosedHiller Acft UH-12E · Corvallis, ORNTSB SEA76DYC32Accident · No injuriesClosedBell 206B · Lagrande, ORNTSB SEA76DYA16Accident · No injuriesClosedCessna 172 · Ontario, ORNTSB SEA76DYI10Accident · No injuriesClosedCessna 320F · Riley Junction, ORNTSB SEA76FYC20Accident · No injuriesClosedHiller Acft UH-12B · Brownsville, ORNTSB SEA76DYI06Accident · No injuriesClosedPiper PA-32 · Redmond, ORNTSB SEA76DYC14Accident · No injuriesClosedPiper PA-32 · Enterprise, ORNTSB SEA75DYI26Accident · No injuriesClosedMooney M20C · Norway, ORNTSB SEA75DYI24Accident · No injuriesClosedCessna 170B · Medford, ORNTSB UNK75X0106Accident · No injuriesClosedCessna 150 · Chemult, ORNTSB SEA75FYC27Accident · No injuriesClosedCessna A188B · Independence, ORNTSB SEA75DYC25Accident · No injuriesClosedCessna 180 · Wamic, ORNTSB SEA75DYI19Accident · No injuriesClosedCessna 177 · Sunriver, ORNTSB SEA75DYC19Accident · Minor injuriesClosedBeech E35 · Lagrande, ORNTSB SEA75DYI14Accident · Minor injuriesClosedBeech 19 · Burns, ORNTSB SEA75DYC01Accident · No injuriesClosedAmer Avco AA1-A · Sandy, ORNTSB SEA74DYI01Accident · No injuriesClosedHiller FH1100 · Cave Jctn, ORNTSB SEA73DYC34Accident · No injuriesClosedPiper PA-12 · Sandy, ORNTSB SEA73DYC25Accident · No injuriesClosedStinson 108 · The Dalles, ORNTSB SEA73DYI07Accident · No injuriesClosedLuscombe 8A · Coos Bay, ORNTSB SEA73DYI05Accident · No injuriesClosedAero Comdr 100 · Burns, ORNTSB SEA72DVC43Accident · No injuriesClosedPiper PA-24 · Lyons, ORNTSB SEA72FYC39Accident · No injuriesClosedMooney M20F · Tigard, ORNTSB SEA72FYC33Accident · Serious injuriesClosedBell 47G-5 · Albany, ORNTSB SEA72FYC31Accident · No injuriesClosedCessna 170 · Donald, OR