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.

NTSB DEN79FTM04Accident · Minor injuriesClosedCessna 421A · Salt Lake City, UTNov 29, 1978 · N38J · Substantial damageNTSB DEN79FTM01Accident · No injuriesClosedCessna P210N · Ogden, UTOct 14, 1978 · N4781P · Substantial damageNTSB DEN78FTM31Accident · Minor injuriesClosedSchweizer SGS2 · Heber, UTJul 29, 1978 · N65812 · Substantial damageNTSB DEN78DTM27Accident · Serious injuriesClosedPiper PA-28 · Oljato, UTJul 1, 1978 · N75103 · Substantial damageNTSB DEN78DTM16Accident · No injuriesClosedAerospatial SA315B · Nmanila, UTMar 22, 1978 · N220RM · Substantial damageNTSB DEN78FTM07Accident · No injuriesClosedBeech B33 · Salt Lake City, UTOct 27, 1977 · N6837Q · Substantial damageNTSB DEN78FTM04Accident · Minor injuriesClosedCessna 150E · Ogden, UTOct 26, 1977 · N3547J · Substantial damageNTSB DEN77FTM21Accident · Serious injuriesClosedPiper PA-28 · Beaver, UTJul 4, 1977 · N490FL · DestroyedNTSB DEN77DTM22Accident · Minor injuriesClosedPiper PA-24 · Hite, UTJul 4, 1977 · N9447P · Substantial damageNTSB DEN77FTM20Accident · No injuriesClosedPiper PA-28 · Benjamin, UTJun 22, 1977 · N9627K · Substantial damageNTSB DEN77FTM08Accident · Minor injuriesClosedCessna 210 · Brigham City, UTNov 12, 1976 · N1840F · Substantial damageNTSB DEN77FTM07Accident · 1 fatalClosedCessna P206B · Nephi, UTOct 19, 1976 · N4764F · DestroyedNTSB DEN77DTM01Accident · No injuriesClosedCessna 172H · Parowan, UTOct 4, 1976 · N521SA · Substantial damageNTSB DEN76DTM46Accident · Minor injuriesClosedPiper PA-22 · Bullfrog Basin, UTAug 22, 1976 · N5927D · Substantial damageNTSB DEN77FTM41Accident · Minor injuriesClosedCessna 206P · Lasal Junction, UTJul 27, 1976 · N2541X · Substantial damageNTSB DEN76DTM32Accident · No injuriesClosedCessna T210L · Navajo Mtn, UTMay 30, 1976 · N2239S · Substantial damageNTSB DEN76FTM12Accident · No injuriesClosedErco 415-C · Heber, UTApr 11, 1976 · N2335H · Substantial damageNTSB DEN76FTM23Accident · Minor injuriesClosedCessna T210L · Roosevelt, UTMar 18, 1976 · N59100 · Substantial damageNTSB DEN76FTM18Accident · No injuriesClosedCessna 210A · Salt Lake City, UTFeb 23, 1976 · N349CP · Substantial damageNTSB DEN76DTM16Accident · No injuriesClosedCessna 172A · Logan, UTJan 30, 1976 · N6844X · Substantial damageNTSB DEN76FTM14Accident · Minor injuriesClosedCessna 172M · Monument Vly, UTJan 13, 1976 · N9937Q · Substantial damageNTSB LAX76AL025Accident · No injuriesClosedCessna 182 · Richfield, UTOct 17, 1975 · N14MD · 2 aircraft · Minor damageNTSB DEN76DTM09Accident · Minor injuriesClosedLuscombe 8E · Hanksville, UTOct 4, 1975 · N1797K · Substantial damageNTSB DEN7LAD010Accident · 1 fatalClosedBeech J35 · Hanksville, UTAug 12, 1975 · N7204B · Substantial damageNTSB DEN7LFTM02Accident · No injuriesClosedPiper PA-24 · Canyonlands, UTJul 27, 1975 · N8642P · DestroyedNTSB DEN75FTM05Accident · No injuriesClosedPiper PA-28 · Salt Lake City, UTSep 8, 1974 · N7511W · Substantial damageNTSB DEN75AD008Accident · 1 fatalClosedCessna 172 · Parowan, UTAug 31, 1974 · N5114A · DestroyedNTSB DEN74FTM15Accident · Minor injuriesClosedCessna 210B · Logan, UTMay 9, 1974 · N9606X · Substantial damageNTSB DEN74DTM14Accident · No injuriesClosedCessna 172I · Price, UTApr 28, 1974 · N35686 · Substantial damageNTSB DEN74DTM17Accident · No injuriesClosedCessna 150G · Cedar City, UTApr 25, 1974 · N8333J · Substantial damageNTSB DEN74FTM03Accident · No injuriesClosedCessna 305A · Ogden, UTJul 28, 1973 · N5271G · Substantial damageNTSB DEN73DTM20Accident · No injuriesClosedCessna 182 · Brigham City, UTFeb 13, 1973 · N8489T · Substantial damageNTSB DEN73FTM15Accident · No injuriesClosedPiper PA-22 · Provo, UTJan 14, 1973 · N3929P · Substantial damageNTSB DEN73FTM09Accident · No injuriesClosedCessna 150J · Ogden, UTSep 20, 1972 · N51369 · Substantial damageNTSB DEN73AD004Accident · 4 fatalClosedPiper PA-23 · Ogden, UTJul 30, 1972 · N5449Y · DestroyedNTSB DEN72DTM13Accident · No injuriesClosedCessna 170B · Monticello, UTMar 18, 1972 · N8065A · Substantial damageNTSB DEN72AD035Accident · 2 fatalClosedMooney M20C · Dugway, UTFeb 4, 1972 · N9412V · DestroyedNTSB DEN72DTM01Accident · No injuriesClosedCessna 150J · Logan, UTJul 1, 1971 · N61148 · Substantial damageNTSB DEN71DTM32Accident · Minor injuriesClosedNavion A · Boulder, UTJun 26, 1971 · N4022K · Substantial damageNTSB DEN71FXT19Accident · No injuriesClosedPiper PA-24 · Monument Vly, UTJan 28, 1971 · N9010P · Substantial damageNTSB DEN70DVW20Accident · No injuriesClosedCessna 172 · Cedar City, UTJun 18, 1970 · N3927F · Substantial damageNTSB DEN70FXT02Accident · Minor injuriesClosedAero Comdr 680-F · Ogden, UTJan 23, 1970 · N345PS · Substantial damageNTSB DEN70FXT01Accident · No injuriesClosedCessna 182 · Spanish Fork, UTJan 8, 1970 · N1951X · Substantial damageNTSB DEN70F0125Accident · No injuriesClosedPiper PA-28 · Salt Lake City, UTNov 10, 1969 · N9759J · Substantial damageNTSB DEN70A0025Accident · 4 fatalClosedBeech V35 · St George, UTOct 10, 1969 · N424V · DestroyedNTSB DEN70D0055Accident · No injuriesClosedBeech 19A · Salt Lake City, UTAug 7, 1969 · N6067N · Substantial damageNTSB DEN69D0160Accident · No injuriesClosedPiper PA-28 · Provo, UTJan 20, 1969 · N5794F · Substantial damageNTSB DEN69F0170Accident · Minor injuriesClosedCessna 172 · Ogden, UTJan 16, 1969 · N7978B · Substantial damageNTSB DEN69F0132Accident · No injuriesClosedCessna 182A · Cedar City, UTOct 16, 1968 · N4968D · Substantial damageNTSB DEN69F0004Accident · Serious injuriesClosedPiper PA-28 · Salt Lake City, UTJun 17, 1968 · N9513W · Substantial damage