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 DEN76FTM27Accident · Serious injuriesClosedCessna 182P · Hanksville, UTNTSB DEN76AD033Accident · 4 fatalClosedGrum Amer AA-5 · Hyrum, UTNTSB DEN76FTM17Accident · Serious injuriesClosedCessna T337C · Salt Lake City, UTNTSB DEN76DTM15Accident · No injuriesClosedCessna 172 · Ephraim, UTNTSB DEN76AD027Accident · 3 fatalClosedPiper PA-28 · Ibapah, UTNTSB DEN76AD024Accident · 4 fatalClosedCessna 172M · Beaver, UTNTSB DEN76FTM11Accident · Serious injuriesClosedPiper PA-24 · Salt Lake City, UTNTSB DEN76FTM07Accident · Minor injuriesClosedBerkshire 70 · Heber City, UTNTSB DEN76AD011Accident · 4 fatalClosedPiper PA-28 · Mountain Home, UTNTSB DEN76FTM04Accident · No injuriesClosedCessna 150 · Cedar City, UTNTSB DEN76FTM05Accident · No injuriesClosedDehavilland DHC-2 · Ogden, UTNTSB DEN7LAD007Accident · 2 fatalClosedPiper PA-22 · Fremont, UTNTSB DEN76AD001Accident · 4 fatalClosedGrum Amer AA5-B · Salt Lake City, UTNTSB DEN7KAD063Accident · 3 fatalClosedCessna 172M · Manila, UTNTSB DEN75AD060Accident · 1 fatalClosedCessna 150 · Logan, UTNTSB DEN75AD046Accident · 4 fatalClosedBellanca 17-30A · Delle, UTNTSB DEN75DTM12Accident · No injuriesClosedPiper PA-12 · Green River, UTNTSB DEN75AD027Accident · 1 fatalClosedRockwell 690A · Kingston, UTNTSB DEN75AD025Accident · 2 fatalClosedAero Comdr 560 · Bicknell, UTNTSB DEN75AD019Accident · 4 fatalClosedPiper PA-28 · St.George, UTNTSB DEN75FTM06Accident · No injuriesClosedCessna 182B · Sandy, UTNTSB DEN75AD015Accident · 2 fatalClosedCessna 182J · Indianola, UTNTSB DEN75AD009Accident · 1 fatalClosedBell 47G3B1 · Ogden, UTNTSB DEN75FTM03Accident · Serious injuriesClosedPiper PA-18 · Tremonton, UTNTSB DEN74DTM16Accident · Minor injuriesClosedCessna 150H · Nephi, UTNTSB DEN74FTM18Accident · Serious injuriesClosedPiper PA-22 · Bountiful, UTNTSB DEN74AD045Accident · 4 fatalClosedPiper PA-22 · Cedar City, UTNTSB DEN74FTM11Accident · Serious injuriesClosedCessna 210F · Cedar City, UTNTSB DEN74DTM12Accident · No injuriesClosedCessna 172D · Thistle, UTNTSB DEN74AD034Accident · 6 fatalClosedBeech 60 · Hatch, UTNTSB DEN74AD030Accident · 1 fatalClosedCessna 320 · Spanish Fork, UTNTSB DEN74AD029Accident · 2 fatalClosedBeech A35 · Plain City, UTNTSB DEN74AD026Accident · 2 fatalClosedCessna 421A · Roosevelt, UTNTSB DEN74AD020Accident · 2 fatalClosedPiper PA-34 · Levan, UTNTSB DEN74AD019Accident · 1 fatalClosedPiper PA-28R · Salt Lake City, UTNTSB DEN74AD017Accident · 2 fatalClosedCessna 177B · West Delta, UTNTSB DEN74FTM06Accident · Minor injuriesClosedWittman W-8 · Salt Lake City, UTNTSB DEN74FTM04Accident · No injuriesClosedCessna 177A · Ogden, UTNTSB DEN74FTM02Accident · Serious injuriesClosedMooney M20C · Hurricane Mesa, UTNTSB DEN74FTM01Accident · Serious injuriesClosedPiper PA-28 · Cisco, UTNTSB DEN73DTM31Accident · No injuriesClosedHiller Acft UH-12E · Richfield, UTNTSB DEN73AD054Accident · 1 fatalClosedChampion 7GCAA · Blanding, UTNTSB DEN73AD050Accident · 4 fatalClosedCessna A185F · Moab, UTNTSB DEN73FTM26Accident · No injuriesClosedCessna 182 · Sandy, UTNTSB DEN73DTM19Accident · No injuriesClosedCessna 172 · Vernal, UTNTSB DEN73AD075Accident · 1 fatalClosedBeech K35 · Salt Lake City, UTNTSB DEN73FTM18Accident · No injuriesClosedPiper PA-24 · Bountiful, UTNTSB DEN73DTM16Accident · No injuriesClosedCessna 150G · Tooele, UTNTSB DEN73DTM13Accident · Minor injuriesClosedCessna 310B · Cedar City, UTNTSB DEN73AD017Accident · 4 fatalClosedMooney M20C · Monticello, UT