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 CHI77FA039Accident · 3 fatalClosedNa Rockwell 112 · Hudson, WINTSB CHI77FEP28Accident · Serious injuriesClosedCessna 210D · Boscobel, WINTSB CHI77FEP19Accident · Minor injuriesClosedBeech K35 · Clintonville, WINTSB CHI77FEP16Accident · No injuriesClosedCessna 150 · Mosinee, WINTSB CHI77FEP09Accident · No injuriesClosedCessna 310 · Janesville, WINTSB CHI77FEP08Accident · No injuriesClosedStinson 108-1 · Burlington, WINTSB CHI76DEP48Accident · No injuriesClosedCessna 210 · Big Falls, WINTSB CHI76FEP38Accident · Minor injuriesClosedHughes 269A · Abbotsford, WINTSB CHI76FEP37Accident · No injuriesClosedSnow 600S2C · Plainfield, WINTSB CHI76FEP35Accident · Serious injuriesClosedCessna 210 · Schofield, WINTSB CHI76FEP31Accident · Serious injuriesClosedLuscombe 8A · West Bend, WINTSB CHI76FER05Accident · No injuriesClosedCessna 150G · Rice Lake, WINTSB CHI76FEP18Accident · Serious injuriesClosedBeech 95-B55 · Crandon, WINTSB CHI76AC008Accident · 1 fatalClosedCessna 170B · Iron Ridge, WINTSB CHI76DEP12Accident · No injuriesClosedCessna 188 · Gillman, WINTSB CHI76DEP10Accident · Minor injuriesClosedGrum Amer AA1-A · Portage, WINTSB CHI76FEP05Accident · Serious injuriesClosedBeech S3S · Franklin, WINTSB CHI75FEP69Accident · No injuriesClosedCessna 182H · Gratiot, WINTSB CHI75FEP46Accident · Minor injuriesClosedLuscombe 8A · Burlington, WINTSB CHI75FEP62Accident · Serious injuriesClosedSchweizer SGS2 · West Bend, WINTSB CHI75DEP59Accident · No injuriesClosedGlobe GC-1B · Milwaukee, WINTSB CHI75FEP53Accident · Minor injuriesClosedFairchild 24W-41 · Union Grove, WINTSB CHI75AC086Accident · No injuriesClosedPiper PA-16 · Janesville, WINTSB CHI75DER06Accident · No injuriesClosedCessna 180 · River Falls, WINTSB CHI75AC073Accident · Serious injuriesClosedDouglas DC-10 · Lacrosse, WINTSB CHI75FEP42Accident · Serious injuriesClosedCessna 337A · Kenosha, WINTSB CHI75AC061Accident · 2 fatalClosedBeech 95 · Waterford, WINTSB CHI75DER05Accident · Minor injuriesClosedCessna 182C · Superior, WINTSB CHI75AC054Accident · 1 fatalClosedBeech V35BTC · Waukesha, WINTSB CHI75FEP36Accident · No injuriesClosedCessna 170B · Waukesha, WINTSB CHI75FEP33Accident · 2 fatalClosedCessna 150L · Slinger, WINTSB CHI75AC036Accident · 1 fatalClosedBellanca 17-30A · Lakewood, WINTSB CHI75FEP12Accident · Minor injuriesClosedWalts Wings S1 · Hartford, WINTSB CHI75FER02Accident · No injuriesClosedN. American P-51 · River Falls, WINTSB CHI75DEP17Accident · No injuriesClosedPiper PA-30 · Madison, WINTSB CHI75FER01Accident · Minor injuriesClosedVollmer Sprts · Webster, WINTSB CHI75FEP10Accident · No injuriesClosedAcro Duster SA-700 · Fond Du Lac, WINTSB CHI75FEP05Accident · Minor injuriesClosedCessna 180 · Three Lakes, WINTSB CHI75AC008Accident · 3 fatalClosedCessna 310L · Land O' Lakes, WINTSB CHI74FEP50Accident · Minor injuriesClosedPiper PA-24 · Madison, WINTSB CHI74AC083Accident · 1 fatalClosedBellanca 7ACA · Rhinelander, WINTSB CHI74AC079Accident · 1 fatalClosedCessna 150F · Glen Flora, WINTSB CHI74AC078Accident · 1 fatalClosedEnstrom F-28A · Superior, WINTSB CHI74DEP34Accident · No injuriesClosedBensen B8MV · Wind Lake, WINTSB CHI74AC024Accident · 6 fatalClosedPiper PA-30 · Florence, WINTSB CHI74AC002Accident · 1 fatalClosedCessna 170A · Superior, WINTSB CHI73FER09Accident · 1 fatalClosedMoonraker 100 · Prescott, WINTSB CHI73FER08Accident · Serious injuriesClosedTaylorcraft BC12-D · Menomonie, WINTSB CHI73FEP61Accident · 1 fatalClosedCassutt 111M · Bloomer, WINTSB CHI73AC085Accident · 1 fatalClosedAeronca 7AC · Rice Lake, WI