NTSB investigations, WI

424 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\" = 'WI' 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 CHI67D0080Accident · No injuriesClosedCessna 172 · Richland Cntr, WIJun 24, 1966 · N6202E · DestroyedNTSB CHI67D0121Accident · No injuriesClosedLuscombe 8A · Mukwonago, WIJun 19, 1966 · N71157 · Substantial damageNTSB CHI66F0758Accident · No injuriesClosedAeronca 7FC · Palmyra, WIJun 4, 1966 · N7683E · Substantial damageNTSB CHI67F0022Accident · Minor injuriesClosedPiper PA-22 · Middleton, WIJun 2, 1966 · N9964D · Substantial damageNTSB CHI66F0745Accident · No injuriesClosedBeech A23A · Cable, WIMay 24, 1966 · N2360W · Substantial damageNTSB CHI66F0744Accident · Serious injuriesClosedCessna 170 · Franklin, WIMay 17, 1966 · N4209V · Substantial damageNTSB CHI66F0550Accident · Minor injuriesClosedCessna 140 · Pelican Lake, WIJan 31, 1966 · N76910 · Substantial damageNTSB CHI66D0593Accident · No injuriesClosedPiper PA-22 · Janesville, WIJan 30, 1966 · N5815Z · Substantial damageNTSB CHI66A0026Accident · No injuriesClosedDouglas DC-3 · Stevens Pt., WISep 14, 1965 · N28341 · Substantial damageNTSB CHI66D0411Accident · No injuriesClosedBeechcraft F35 · Baraboo, WIAug 16, 1965 · N3844B · Substantial damageNTSB CHI66A0015Accident · 4 fatalClosedBeech B35 · Boulder Jct, WIAug 7, 1965 · N5091C · DestroyedNTSB CHI66F0047Accident · Minor injuriesClosedNo American SNJ-5 · Hales Corner, WIJun 28, 1965 · N3262G · Substantial damageNTSB CHI66F0141Accident · Serious injuriesClosedComonwealth 7000 · Sparta, WIJun 26, 1965 · N20734 · Substantial damageNTSB MKC65F0094Accident · No injuriesClosedCessna 150D · Kenosha, WIApr 2, 1965 · N6020T · Substantial damageNTSB CHI66F0028Accident · No injuriesClosedPiper PA-20 · Cable, WIMar 6, 1965 · N1145C · 2 aircraft · Substantial damageNTSB UNK65X1343Accident · Serious injuriesClosedPiper PA-18 · Tustin, WIJan 24, 1965 · N1021A · DestroyedNTSB UNK65X0242Accident · No injuriesClosedCessna 140 · Palmyra, WIOct 11, 1964 · N2050V · DestroyedNTSB UNK64X3963Accident · No injuriesClosedLuscombe 8A · Wis Rapids, WISep 13, 1964 · N2704K · Substantial damageNTSB UNK64X3185Accident · No injuriesClosedHughes 269A · Oconomowoc Lk, WIAug 2, 1964 · N8924F · Substantial damageNTSB UNK64X2764Accident · Minor injuriesClosedPiper PA22 · Mukwonago, WIJul 11, 1964 · N2713D · Substantial damageNTSB UNK64X2280Accident · Minor injuriesClosedBeechcraft D35 · West Bend, WIJun 14, 1964 · N2953B · Substantial damageNTSB UNK64X1290Accident · 1 fatalClosedCessna 150 · Solon Springs, WIApr 12, 1964 · N7829Z · DestroyedNTSB UNK64X0293Accident · Minor injuriesClosedCessna 172 · Lafayette Tsp, WIJan 19, 1964 · N1694Y · DestroyedNTSB UNK64X0292Accident · No injuriesClosedPiper PA28 · Hales Corners, WIJan 19, 1964 · N7132W · Substantial damage