NTSB investigations, UT
544 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\" = 'UT' 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" = 'UT' 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 DCA80AA007Accident · 3 fatalClosedLockheed 188C · Salt Lake City, UTNTSB DEN80DTM04Accident · No injuriesClosedBeech 19 · Cedar City, UTNTSB DEN80FA004Accident · 2 fatalClosedRockwell 114 · Hanksville, UTNTSB DEN80DTM03Accident · Serious injuriesClosedCessna 172 · Ogden, UTNTSB DEN80FTM02Accident · Minor injuriesClosedCessna P210 · Fillmore, UTNTSB DEN79FA059Accident · 2 fatalClosedAero Comdr 114 · Green River, UTNTSB DEN99FA057Accident · 4 fatalClosedPiper PA-28 · Fairview, UTNTSB DEN79FA044Accident · No injuriesClosedCessna U206 · Milford, UTNTSB DEN79FTM14Accident · Minor injuriesClosedPiper PA-22 · Panguitch, UTNTSB DEN79FTM17Accident · Serious injuriesClosedMooney M20E · Hanksville, UTNTSB DEN79FTM13Accident · No injuriesClosedGrumman G44A · Delta, UTNTSB DEN79FA029Accident · 2 fatalClosedPiper PA-24 · Corinne, UTNTSB DEN79FA016Accident · 5 fatalClosedCessna 421C · Spanish Forks, UTNTSB DEN79FTM08Accident · Serious injuriesClosedCessna 172 · Cedar Fort, UTNTSB DEN79FTM06Accident · Minor injuriesClosedAerospatial SA315B · Magna, UTNTSB DEN79FA011Accident · Serious injuriesClosedAerospatial SA316B · Ogden, UTNTSB DEN79DTM02Accident · Minor injuriesClosedCessna 150 · Price, UTNTSB DEN79FA004Accident · 1 fatalClosedCessna 172 · Provo, UTNTSB DEN78FTM32Accident · Minor injuriesClosedCessna 150 · Heber, UTNTSB DEN78FA061Accident · Serious injuriesClosedCessna 206 · Salina, UTNTSB DEN78FA059Accident · 6 fatalClosedBeech 55 · Fairfield, UTNTSB DEN78FTM28Accident · 1 fatalClosedClark Focke 190A3 · Cedar City, UTNTSB DEN78FTM24Accident · Serious injuriesClosedErco 415-C · Eureka, UTNTSB DEN78FA041Accident · No injuriesClosedBell 206B3 · Westwater, UTNTSB DEN78DTM19Accident · Minor injuriesClosedMooney M20C · Kanab, UTNTSB DEN78FTM18Accident · No injuriesClosedCessna 150 · Fairview, UTNTSB DEN78FA035Accident · 1 fatalClosedPiper PA-38 · Park City, UTNTSB DEN78FTM14Accident · Serious injuriesClosedPiper PA-28 · Brigham City, UTNTSB DEN78FTM11Accident · 1 fatalClosedTurner T-40 · Ogden, UTNTSB DEN78FA021Accident · 4 fatalClosedCessna 177B · Salt Lake City, UTNTSB DCA78AA005Accident · 3 fatalClosedDouglas DC-8F · Kaysville, UTNTSB DEN77DTM32Accident · No injuriesClosedPiper PA-18 · Ogden, UTNTSB DEN77FTM32Accident · No injuriesClosedPiper PA-22 · Ogden, UTNTSB DEN77FTM28Accident · Serious injuriesClosedPiper PA-32R · Heber City, UTNTSB DEN77DTC24Accident · Minor injuriesClosedLuscombe 8F · Dutch John, UTNTSB DEN77FTM17Accident · Serious injuriesClosedCessna 172M · Serviers, UTNTSB DEN77FA038Accident · 2 fatalClosedBellanca 7KCAB · Morgan, UTNTSB DEN77FTM13Accident · Minor injuriesClosedGrum Amer AA-1B · American Fork, UTNTSB DEN77FA035Accident · 2 fatalClosedCessna 182H · Tooele, UTNTSB DEN77FTM12Accident · Serious injuriesClosedNa Rockwell 114 · Vernal, UTNTSB DEN77FA027Accident · 3 fatalClosedCessna U206D · Hanna, UTNTSB DEN77FTM10Accident · No injuriesClosedCessna 150L · Price, UTNTSB DEN77DTM09Accident · No injuriesClosedAerospatle SA315B · Thistle, UTNTSB DEN77FTM06Accident · Minor injuriesClosedBell 47G · Beaver, UTNTSB LAX77IA003Incident · Serious injuriesClosedBoeing 737 · Salt Lake City, UTNTSB DEN76AD073Accident · 2 fatalClosedBell 47G3B2 · Mantua, UTNTSB DEN76AD061Accident · 1 fatalClosedPiper PA-30 · Kings Peak, UTNTSB DEN76AD055Accident · 1 fatalClosedSchweizer SGU222 · Heber City, UTNTSB DEN76AD056Accident · 4 fatalClosedStinson 108-2 · Kamas, UTNTSB DEN76DTM37Accident · No injuriesClosedPiper PA-28 · Hatch, UT