NTSB investigations, WI
580 cases where the phase of flight was Enroute.
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 = 400)
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" = 'WI' AND c."eventCountry" = 'USA'
AND c."mKey" IN (SELECT case_mkey FROM case_phases WHERE main = 400)
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 CHI79FA072Accident · 2 fatalClosedPiper PA-24 · Bloomfield Twp, WINTSB CHI79FEP75Accident · 2 fatalClosedPiper PA-28 · Kenosha, WINTSB CHI79FEP69Accident · No injuriesClosedCessna 177 · Oshkosh, WINTSB CHI79FEP63Accident · No injuriesClosedCessna 182 · Green Bay, WINTSB CHI79FA063Accident · 1 fatalClosedPiper PA-32 · Spring Green, WINTSB CHI79FEP66Accident · Serious injuriesClosedCessna 172M · Rockwood, WINTSB CHI79FEP67Accident · Minor injuriesClosedPiper PA-17 · South Byron, WINTSB CHI79FEP53Accident · No injuriesClosedCessna 150 · Milwaukee, WINTSB CHI79FEP51Accident · Serious injuriesClosedCessna 182P · Neillsville, WINTSB CHI79FA056Accident · 1 fatalClosedPiper PA-28 · Taylor, WINTSB CHI79FEP48Accident · Minor injuriesClosedBeech V35 · Beloit, WINTSB CHI79FEP43Accident · No injuriesClosedPiper PA-32R · Iola, WINTSB CHI79FEP40Accident · No injuriesClosedCessna 150M · Appleton, WINTSB CHI79FEP39Accident · No injuriesClosedBellanca 7GCBC · Bristol, WINTSB CHI79FEP13Accident · No injuriesClosedCessna 150 · Sussex, WINTSB CHI79FEP07Accident · Minor injuriesClosedCessna P210N · Green Bay, WINTSB CHI79FEP03Accident · No injuriesClosedBeech V-35A · Richland Cntr, WINTSB CHI78FA070Accident · 1 fatalClosedCessna 150F · Portage, WINTSB CHI78FEP70Accident · No injuriesClosedPiper PA-28 · Milwaukee, WINTSB CHI78FEP68Accident · No injuriesClosedMooney M20F · Racine, WINTSB CHI78FEP65Accident · Serious injuriesClosedCessna 177B · Junction City, WINTSB CHI78FEP69Accident · No injuriesClosedCessna 172F · Sauk Prairie, WINTSB CHI78FEP61Accident · 2 fatalClosedBeech C23 · Portage, WINTSB CHI79FEP55Accident · No injuriesClosedCessna 177 · Eagle River, WINTSB CHI78FA051Accident · 2 fatalClosedBeech E-33A · Prt Washington, WINTSB CHI78FEP51Accident · No injuriesClosedPiper PA-22 · Milwaukee, WINTSB CHI78FEP40Accident · No injuriesClosedBeech B-23 · Lacrosse, WINTSB CHI78FEP37Accident · No injuriesClosedDehavilland DHC-2 · Chippewa Falls, WINTSB CHI78FA041Accident · 4 fatalClosedCessna T210M · Bloomer, WINTSB CHI78DEP33Accident · No injuriesClosedCessna 185 · Clyman, WINTSB CHI78FEP24Accident · No injuriesClosedPiper PA-28 · Lacrosse, WINTSB CHI78FEP19Accident · No injuriesClosedTaylorcraft BL-65 · Hartford, WINTSB CHI78FEP20Accident · Minor injuriesClosedPiper PA-28R · Pewaukee, WINTSB CHI78DEP12Accident · No injuriesClosedCessna 150L · Sun Prairie, WINTSB CHI78FA034Accident · 1 fatalClosedPiper PA-28 · Mellen, WINTSB CHI78FEP08Accident · No injuriesClosedGrum Amer AA1B · Racine, WINTSB CHI77FEP61Accident · No injuriesClosedCessna 172L · West Bend, WINTSB CHI77FEP63Accident · Serious injuriesClosedCessna 150L · Winneconne, WINTSB CHI78FA071Accident · 2 fatalClosedCessna 320 · Milwaukee, WINTSB CHI77FEP60Accident · No injuriesClosedCessna 170 · Mosinee, WINTSB CHI77FEP59Accident · Minor injuriesClosedBell 47J2A · Plymouth, WINTSB CHI77FA069Accident · 4 fatalClosedCessna 170 · Florence, WINTSB CHI77FA063Accident · 3 fatalClosedAero Comdr 720 · Wausau, WINTSB CHI77FA062Accident · 1 fatalClosedBellanca 7ACA · Oshkosh, WINTSB CHI77EEP48Accident · 1 fatalClosedBellanca 14-13 · Prairie Du Chn, WINTSB CHI77FER04Accident · 1 fatalClosedPiper PA-24 · Superior, WINTSB CHI77DEP40Accident · No injuriesClosedBellanca 7AC · Kaukauna, WINTSB CHI77DEP35Accident · Serious injuriesClosedReplica BE-2C · Neenah, WINTSB CHI77DEP36Accident · Minor injuriesClosedPiper PA-32 · Clam Lake, WINTSB CHI77FA046Accident · 4 fatalClosedPiper PA-24 · Evansville, WI