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 CHI76FEP16Accident · No injuriesClosedCessna 150H · Lake Geneva, WIOct 12, 1975 · N50127 · Substantial damageNTSB CHI76DER04Accident · No injuriesClosedBensen B-8M · New Richmond, WIAug 28, 1975 · N8322 · Substantial damageNTSB CHI75FEP65Accident · No injuriesClosedBeech E18S · Burlington, WIJul 25, 1975 · N402FW · Substantial damageNTSB CHI75FEP68Accident · Serious injuriesClosedGrum Amer AA1 · Delavan, WIJun 12, 1975 · N6543L · Substantial damageNTSB CHI75EEP50Accident · No injuriesClosedErco 415-C · Oconto, WIMar 16, 1975 · N99836 · Substantial damageNTSB CHI75FEP31Accident · Serious injuriesClosedCessna 182P · Racine, WIOct 28, 1974 · N21534 · Substantial damageNTSB CHI75FEP25Accident · No injuriesClosedStinson V-77 · Belleville, WISep 28, 1974 · N69856 · Substantial damageNTSB CHI75FEP23Accident · Serious injuriesClosedCessna 150E · Milwaukee, WISep 14, 1974 · N4069U · Substantial damageNTSB CHI75FEP22Accident · 1 fatalClosedCessna 182H · West Bend, WISep 13, 1974 · N8533S · Substantial damageNTSB CHI75FEP21Accident · Serious injuriesClosedFairchild M-62A · Oostburg, WIAug 18, 1974 · N192P · DestroyedNTSB CHI75AC017Accident · 1 fatalClosedAmer Avco AA-1A · New Richmond, WIAug 18, 1974 · N9262L · DestroyedNTSB CHI75FEP04Accident · Minor injuriesClosedPiper PA-18 · Hazelhurst, WIJul 29, 1974 · N7092D · Substantial damageNTSB CHI75AC003Accident · No injuriesClosedCessna 310C · Middleton, WIJul 9, 1974 · N1799H · Substantial damageNTSB CHI74FEP52Accident · Minor injuriesClosedCessna 210 · Watertown, WIJun 15, 1974 · N6545X · Substantial damageNTSB CHI74DER04Accident · Minor injuriesClosedPiper PA-28 · Superior, WIJun 15, 1974 · N56303 · Substantial damageNTSB CHI74DEP45Accident · No injuriesClosedAmer Avco AA1-A · Brodhead, WIApr 27, 1974 · N9394L · Substantial damageNTSB CHI74FEP41Accident · No injuriesClosedAmer Avco AA-1B · Waunakee, WIApr 15, 1974 · N9621L · Substantial damageNTSB CHI74DEP24Accident · No injuriesClosedCessna 150 · Crivitz, WIDec 10, 1973 · N10772 · Substantial damageNTSB CHI74DEP23Accident · Minor injuriesClosedAeronca 11AC · Neshkoro, WIDec 1, 1973 · N9353E · Substantial damageNTSB CHI74AC043Accident · 4 fatalClosedPiper PA-28 · Delavan, WINov 23, 1973 · N16327 · DestroyedNTSB CHI74FEP19Accident · No injuriesClosedCessna 175A · Milwaukee, WIOct 14, 1973 · N7017E · Substantial damageNTSB CHI74AC033Accident · 1 fatalClosedBeech D35 · Aurora, WIOct 9, 1973 · N2964B · DestroyedNTSB CHI74FEP11Accident · Minor injuriesClosedCessna 170B · Kenosha, WIAug 12, 1973 · N4670C · Substantial damageNTSB CHI74FEP10Accident · Serious injuriesClosedCessna 172 · Cadott, WIAug 10, 1973 · N8595U · DestroyedNTSB CHI74FEP04Accident · Serious injuriesClosedStits SA3B · Oshkosh, WIAug 3, 1973 · N3849 · Substantial damageNTSB CHI74FEP03Accident · No injuriesClosedBede BD-5J · Oshkosh, WIAug 1, 1973 · N5BD · Substantial damageNTSB CHI74DEP02Accident · Minor injuriesClosedCessna 150L · Stoughton, WIJul 25, 1973 · N1328Q · DestroyedNTSB CHI73FEP60Accident · Serious injuriesClosedPiper PA-24 · Madison, WIMay 24, 1973 · N7068D · Substantial damageNTSB CHI73DEP56Accident · Minor injuriesClosedCessna 177 · Rhinelander, WIApr 30, 1973 · N3174T · DestroyedNTSB CHI73FEP51Accident · No injuriesClosedPiper PA-28 · Sturtevant, WIApr 28, 1973 · N5024T · Substantial damageNTSB CHI73FEP33Accident · No injuriesClosedCessna 172C · New Holstein, WIDec 14, 1972 · N1535Y · Substantial damageNTSB CHI73FEP30Accident · Minor injuriesClosedCessna 120 · Hartford, WINov 5, 1972 · N2425N · Substantial damageNTSB CHI73DER01Accident · No injuriesClosedCessna 150G · New Richmond, WIJul 31, 1972 · N3998J · Substantial damageNTSB CHI72FEP51Accident · Minor injuriesClosedPiper J3C-65 · Fond Du Lac, WIJun 27, 1972 · N40609 · Substantial damageNTSB CHI72FEP45Accident · 1 fatalClosedBensen B-8 · Wiscnsin Rpds, WIMay 16, 1972 · N2362 · DestroyedNTSB CHI73AC085Accident · 5 fatalClosedBeech 65-A90 · Racine, WIMay 1, 1972 · N295X · DestroyedNTSB CHI72AC083Accident · 1 fatalClosedShort Bros SC-7-3 · Lacrosse, WIApr 14, 1972 · N725R · DestroyedNTSB CHI72FEP24Accident · No injuriesClosedMitsubishi MU2B20 · East Troy, WIDec 11, 1971 · N304L · Substantial damageNTSB CHI72FEP23Accident · Minor injuriesClosedPiper PA-32 · Waukesha, WIDec 5, 1971 · N3449W · DestroyedNTSB CHI72DEP19Accident · No injuriesClosedDoers B8M · Tichigan, WIOct 2, 1971 · N7195 · Substantial damageNTSB CHI71DEP50Accident · Minor injuriesClosedCessna 337 · Land O Lakes, WIMay 7, 1971 · N2201X · Substantial damageNTSB CHI71AC060Accident · 1 fatalClosedCessna 411 · Germantown, WIFeb 26, 1971 · N3283R · DestroyedNTSB CHI71DDJ43Accident · Minor injuriesClosedCessna A150K · Appleton, WIFeb 9, 1971 · N8336M · Substantial damageNTSB CHI71AC053Accident · 1 fatalClosedPiper PA-22 · Dyckesville, WIJan 16, 1971 · N4651Z · DestroyedNTSB CHI71AC031Accident · Serious injuriesClosedDehavilland DHC-6 · Lacrosse, WINov 9, 1970 · N956SM · DestroyedNTSB CHI71FDJ24Accident · Minor injuriesClosedCessna 195 · Franklin, WINov 8, 1970 · N3878V · Substantial damageNTSB CHI71FDJ19Accident · Serious injuriesClosedCessna 150J · Eau Claire, WIOct 5, 1970 · N61357 · DestroyedNTSB CHI71DDJ16Accident · No injuriesClosedCessna 177 · Black Rvr Fls, WISep 12, 1970 · N2870X · Substantial damageNTSB CHI71DDJ14Accident · No injuriesClosedPiper PA-28 · Delavan, WISep 5, 1970 · N7864N · Substantial damageNTSB CHI71FDJ13Accident · No injuriesClosedLuscombe 8E · Cottage Grove, WIAug 19, 1970 · N39028 · Substantial damage