NTSB investigations, WI

90 cases where the phase of flight was Taxi.

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 = 250)
  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 CHI78FEP10Accident · No injuriesClosedCessna 402 · Milwaukee, WIDec 13, 1977 · N15096 · Substantial damageNTSB CHI77DEP44Accident · No injuriesClosedCessna 182 · Burlington, WIJul 16, 1977 · N2683G · Substantial damageNTSB CHI77FEP42Accident · No injuriesClosedCessna 188B · Nekoosa, WIJul 4, 1977 · N4915Q · Substantial damageNTSB CHI77DEP34Accident · No injuriesClosedAeronca 7AC · Waunakee, WIMay 6, 1977 · N1162E · Substantial damageNTSB CHI77DEP32Accident · No injuriesClosedCessna 207 · Wausau, WIApr 15, 1977 · N1567U · DestroyedNTSB CHI77DEP26Accident · No injuriesClosedCessna 182 · Kenosha, WIMar 29, 1977 · N1813X · Substantial damageNTSB CHI75DEP72Accident · No injuriesClosedPiper PA-24 · Sturgeon Bay, WIJun 20, 1975 · N5837P · Substantial damageNTSB CHI75FEP57Accident · No injuriesClosedPiper PA-28 · Milwaukee, WIMay 24, 1975 · N43041 · Substantial damageNTSB CHI74AC040Accident · No injuriesClosedBoeing 727 · Madison, WIDec 9, 1973 · N259US · Substantial damageNTSB CHI73FEP35Accident · No injuriesClosedPiper PA-32 · Racine, WIJan 12, 1973 · N4191R · Substantial damageNTSB CHI73DEP25Accident · No injuriesClosedLuscombe 8A · Waunakee, WISep 22, 1972 · N71318 · Substantial damageNTSB CHI73DEP21Accident · No injuriesClosedCessna 172 · Lake Geneva, WISep 4, 1972 · N172KA · Substantial damageNTSB CHI72FEP48Accident · No injuriesClosedCessna 180 · Menomonee Fls, WIJun 10, 1972 · N3129D · Substantial damageNTSB CHI71FDJ30Accident · Serious injuriesClosedCessna 172 · Milwaukee, WIDec 5, 1970 · N8071L · Substantial damageNTSB CHI71DDJ79Accident · No injuriesClosedCessna 172 · Milwaukee, WIDec 5, 1970 · N46024 · Substantial damageNTSB CHI71DDJ31Accident · No injuriesClosedCessna 172 · Juneau, WIDec 5, 1970 · N5686T · Substantial damageNTSB CHI71DDJ28Accident · No injuriesClosedCessna 172 · Reedsburg, WIDec 5, 1970 · N8164U · Substantial damageNTSB CHI71DDJ20Accident · No injuriesClosedCessna 172E · Madison, WIOct 6, 1970 · N5785T · Substantial damageNTSB CHI70DDJ16Accident · No injuriesClosedCessna 182 · Juneau, WIMay 1, 1970 · N3367Y · Substantial damageNTSB CHI70FDJ12Accident · No injuriesClosedCessna 182 · Stevens Point, WIMar 27, 1970 · N6498A · Substantial damageNTSB CHI70D0283Accident · No injuriesClosedCessna 310 · Tomah, WIAug 27, 1969 · N5355A · Substantial damageNTSB CHI70D0012Accident · No injuriesClosedPiper PA-22 · Madison, WIApr 18, 1969 · N3347A · Substantial damageNTSB CHI69F0659Accident · No injuriesClosedPiper PA-28 · De Pere, WIApr 12, 1969 · N1733J · Substantial damageNTSB CHI69D0300Accident · No injuriesClosedCessna 150 · Black Rvr Fls, WIAug 30, 1968 · N3679J · Substantial damageNTSB CHI68D0752Accident · No injuriesClosedPiper PA-22 · Stoughton, WIMay 8, 1968 · N6159D · Substantial damageNTSB CHI68D0696Incident · No injuriesClosedCessna 185C · Grafton, WIApr 16, 1968 · N2674Z · Substantial damageNTSB CHI68D0543Accident · No injuriesClosedPiper PA-28 · New Richmond, WIDec 31, 1967 · N7642W · Substantial damageNTSB CHI68D0192Accident · No injuriesClosedPiper J-3 · Hager City, WIAug 13, 1967 · N92477 · DestroyedNTSB CHI67D0502Accident · No injuriesClosedPiper PA-28 · Sturtevant, WIFeb 26, 1967 · N9140W · Substantial damageNTSB CHI67D0498Accident · No injuriesClosedCessna 182 · Shawano, WIFeb 14, 1967 · N2786G · Substantial damageNTSB CHI67D0429Accident · No injuriesClosedPiper PA-23 · Madison, WIDec 15, 1966 · N3139P · Substantial damageNTSB CHI67D0286Accident · No injuriesClosedCessna 172 · Waukesha, WIOct 4, 1966 · N3896L · Substantial damageNTSB CHI67D0313Accident · No injuriesClosedAeronca 11BC · Kaukauna, WISep 23, 1966 · N3948E · Substantial damageNTSB CHI67D0195Accident · No injuriesClosedCessna 150A · Stevens Point, WISep 12, 1966 · N7217X · Substantial damageNTSB CHI67D0110Accident · No injuriesClosedCessna 310B · Milwaukee, WIAug 1, 1966 · N5396A · Substantial damageNTSB MKC67D0026Accident · No injuriesClosedPiper J4A-S · Superior, WIJun 12, 1966 · N29097 · Substantial damageNTSB CHI66D0049Accident · No injuriesClosedPiper PA-22 · Milwaukee, WIJun 6, 1965 · N4677Z · Substantial damageNTSB UNK64X3935Accident · No injuriesClosedCessna 182 · Eau Claire, WISep 12, 1964 · N3758D · Substantial damageNTSB UNK64X1917Accident · No injuriesClosedCessna 310 · Milwaukee, WIMay 23, 1964 · N5226A · Substantial damageNTSB UNK64X0142Accident · No injuriesClosedPiper PA12 · Portage, WIJan 3, 1964 · N3211M · Substantial damage