NTSB investigations, UT
221 cases where the phase of flight was Approach.
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 = 500)
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 = 500)
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 DEN79FTM04Accident · Minor injuriesClosedCessna 421A · Salt Lake City, UTNTSB DEN79FTM01Accident · No injuriesClosedCessna P210N · Ogden, UTNTSB DEN78FTM31Accident · Minor injuriesClosedSchweizer SGS2 · Heber, UTNTSB DEN78DTM27Accident · Serious injuriesClosedPiper PA-28 · Oljato, UTNTSB DEN78DTM16Accident · No injuriesClosedAerospatial SA315B · Nmanila, UTNTSB DEN78FTM07Accident · No injuriesClosedBeech B33 · Salt Lake City, UTNTSB DEN78FTM04Accident · Minor injuriesClosedCessna 150E · Ogden, UTNTSB DEN77FTM21Accident · Serious injuriesClosedPiper PA-28 · Beaver, UTNTSB DEN77DTM22Accident · Minor injuriesClosedPiper PA-24 · Hite, UTNTSB DEN77FTM20Accident · No injuriesClosedPiper PA-28 · Benjamin, UTNTSB DEN77FTM08Accident · Minor injuriesClosedCessna 210 · Brigham City, UTNTSB DEN77FTM07Accident · 1 fatalClosedCessna P206B · Nephi, UTNTSB DEN77DTM01Accident · No injuriesClosedCessna 172H · Parowan, UTNTSB DEN76DTM46Accident · Minor injuriesClosedPiper PA-22 · Bullfrog Basin, UTNTSB DEN77FTM41Accident · Minor injuriesClosedCessna 206P · Lasal Junction, UTNTSB DEN76DTM32Accident · No injuriesClosedCessna T210L · Navajo Mtn, UTNTSB DEN76FTM12Accident · No injuriesClosedErco 415-C · Heber, UTNTSB DEN76FTM23Accident · Minor injuriesClosedCessna T210L · Roosevelt, UTNTSB DEN76FTM18Accident · No injuriesClosedCessna 210A · Salt Lake City, UTNTSB DEN76DTM16Accident · No injuriesClosedCessna 172A · Logan, UTNTSB DEN76FTM14Accident · Minor injuriesClosedCessna 172M · Monument Vly, UTNTSB LAX76AL025Accident · No injuriesClosedCessna 182 · Richfield, UTNTSB DEN76DTM09Accident · Minor injuriesClosedLuscombe 8E · Hanksville, UTNTSB DEN7LAD010Accident · 1 fatalClosedBeech J35 · Hanksville, UTNTSB DEN7LFTM02Accident · No injuriesClosedPiper PA-24 · Canyonlands, UTNTSB DEN75FTM05Accident · No injuriesClosedPiper PA-28 · Salt Lake City, UTNTSB DEN75AD008Accident · 1 fatalClosedCessna 172 · Parowan, UTNTSB DEN74FTM15Accident · Minor injuriesClosedCessna 210B · Logan, UTNTSB DEN74DTM14Accident · No injuriesClosedCessna 172I · Price, UTNTSB DEN74DTM17Accident · No injuriesClosedCessna 150G · Cedar City, UTNTSB DEN74FTM03Accident · No injuriesClosedCessna 305A · Ogden, UTNTSB DEN73DTM20Accident · No injuriesClosedCessna 182 · Brigham City, UTNTSB DEN73FTM15Accident · No injuriesClosedPiper PA-22 · Provo, UTNTSB DEN73FTM09Accident · No injuriesClosedCessna 150J · Ogden, UTNTSB DEN73AD004Accident · 4 fatalClosedPiper PA-23 · Ogden, UTNTSB DEN72DTM13Accident · No injuriesClosedCessna 170B · Monticello, UTNTSB DEN72AD035Accident · 2 fatalClosedMooney M20C · Dugway, UTNTSB DEN72DTM01Accident · No injuriesClosedCessna 150J · Logan, UTNTSB DEN71DTM32Accident · Minor injuriesClosedNavion A · Boulder, UTNTSB DEN71FXT19Accident · No injuriesClosedPiper PA-24 · Monument Vly, UTNTSB DEN70DVW20Accident · No injuriesClosedCessna 172 · Cedar City, UTNTSB DEN70FXT02Accident · Minor injuriesClosedAero Comdr 680-F · Ogden, UTNTSB DEN70FXT01Accident · No injuriesClosedCessna 182 · Spanish Fork, UTNTSB DEN70F0125Accident · No injuriesClosedPiper PA-28 · Salt Lake City, UTNTSB DEN70A0025Accident · 4 fatalClosedBeech V35 · St George, UTNTSB DEN70D0055Accident · No injuriesClosedBeech 19A · Salt Lake City, UTNTSB DEN69D0160Accident · No injuriesClosedPiper PA-28 · Provo, UTNTSB DEN69F0170Accident · Minor injuriesClosedCessna 172 · Ogden, UTNTSB DEN69F0132Accident · No injuriesClosedCessna 182A · Cedar City, UTNTSB DEN69F0004Accident · Serious injuriesClosedPiper PA-28 · Salt Lake City, UT