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 CHI05CA006Accident · No injuriesData collectionFinal reportPiper PA-28-160 · Altoona, WIThe pilot's inadequate fuel management leading to the fuel starvation in the left tank during the flight's descent. A factor was the impacted power line.NTSB CHI04LA256Accident · 1 fatalLimited investigationFinal reportClaxton S-12 · Oconomowoc, WIThe loss of engine power due to the seizure of the pistons and the pilot's failure to maintain airspeed which resulted in an inadvertent stall. The previous damage to the pistons due to earlier seizure occurrences was a factor.NTSB CHI04LA243Accident · 1 fatalLimited investigationFinal reportBoeing A75 · Wisconsin Rapid, WIThe pilot's improper in-flight decision when he decided to fly at a low altitude over the river and failed to maintain clearance from the power lines. A factor was the wires.NTSB CHI04FA203Accident · 1 fatalField investigationFinal reportKnoepflein Lancair Legacy 2000 · Madison, WIA loss of engine power due to high engine temperatures which resulted in a failure of the number three piston, the pilot's continued operation of the airplane with known high temperature problems, the pilot's poor inflight planning, and the pilot's failure to…NTSB CHI04CA191Accident · Serious injuriesData collectionFinal reportAerostar RX-7 · Wausau, WIThe pilot's inability to compensation for the turbulence and wind conditions. Contributing factors were the variable winds and the turbulence.NTSB CHI04CA138Accident · No injuriesData collectionFinal reportMerkel Acro Sport II · Beloit, WIInaccurate fuel consumption calculations which resulted in fuel exhaustion. Factors associated with the accident were the pilot's continued flight with an inoperable fuel quantity indicator and the wires which the airplane contacted during the forced landing.NTSB CHI04LA104Accident · No injuriesLimited investigationFinal reportCessna 172M · New Munster, WIVisual lookout not maintained by the pilots of both airplanes.NTSB CHI04LA084Accident · Serious injuriesLimited investigationFinal reportBoeing 757-200 · Janesville, WIThe unexpected encounter with convective turbulence.NTSB CHI04LA027Accident · Minor injuriesLimited investigationFinal reportBell 206B · Coleman, WIThe loss of engine power for undetermined reasons and the unsuitable terrain encountered for the forced landing. The garage building was a factor.NTSB CHI03LA281Accident · Minor injuriesLimited investigationFinal reportCessna 150J · Fennimore, WIThe loss of engine power in cruise flight due to carburetor icing. A factor was the unsuitable terrain the pilot encountered during his forced landing.NTSB CHI03LA256Accident · No injuriesLimited investigationFinal reportNicoson Antares MA-33 · Palmyra, WIThe loss of engine power for undetermined reasons and the pilot misjudging the descent rate during the forced landing, which resulted in the airplane landing short of the desired landing area. A factor to the accident was the fence.NTSB CHI03LA255Accident · No injuriesLimited investigationFinal reportTaylorcraft F19 · Lakewood, WIA loss of engine power due to fuel starvation as a result of the pilot's improper use of the fuel system. A related factor was the tree.NTSB CHI03LA201Accident · 2 fatalLimited investigationFinal reportMcIlraith Sea Rey · Tomah, WIThe loss of engine power for an undetermined reason, and the pilot's failure to maintain airspeed resulting in a stall/spin during the forced landing.NTSB CHI02FA247Accident · 1 fatalField investigationFinal reportCessna 172N · Shell Lake, WIThe pilot's failure to refuel the airplane prior to departure leading to fuel exhaustion during the flight, the inadequate preflight/inflight planning, and the unsuitable terrain encountered by the pilot during the forced landing attempt. Contributing factors…NTSB CHI02LA245Accident · Minor injuriesLimited investigationFinal reportPiper PA22-150 · Sauk City, WIA loss of engine power due to a seized exhaust valve. A related factor was the mowed hay field.NTSB CHI02LA222Accident · Serious injuriesLimited investigationFinal reportWhite New Standard D-25 · Oshkosh, WIThe partial loss of engine power due to carburetor icing. Factors to the accident include the weather conditions conducive to carburetor icing and the transmission wire.NTSB CHI02LA195Accident · No injuriesLimited investigationFinal reportMeyers OTW-145 · Waupun, WIA loss of engine power due to fuel exhaustion as a result of the fuel pump failure. Contributing to the accident was the wheat field.NTSB CHI02IA116Incident · No injuriesIncident investigationFinal reportBeech 200 · Hillsdale, WIThe fatigue failure of the passenger/crew door latch pin during cruise flight.NTSB CHI02LA091Accident · Minor injuriesLimited investigationFinal reportAviat Pitts S-2B · Milwaukee, WIThe loss of engine power for undetermined reasons. A factor in the accident was the telephone pole.NTSB CHI02IA083Incident · No injuriesIncident investigationFinal reportCanadair CL-600-2B19 · Appleton, WIThe separation of the low pressure turbine module due to coke resulting in the blockage of the oil system.NTSB CHI02LA081Accident · No injuriesLimited investigationFinal reportCessna 172F · Lynxville, WIThe pilots delayed use of carburetor heat during carburetor icing conditions, and the unsuitable terrain encountered for the forced landing. The carburetor icing conditions, the powerline, the brush, and the nose gear collapse were contributing factors.NTSB CHI02FA049Accident · 1 fatalField investigationFinal reportRobinson R44 · Waukesha, WIThe pilot's continued flight into adverse weather and not maintaining altitude/clearance from the static line during cruise flight. Factors were the static wire, the darkness of night, the low ceiling, and the vehicle.NTSB CHI01FA329Accident · 3 fatalField investigationFinal reportCessna 414 Riley Super-8 · Marshfield, WIThe pilot's failure to maintain adequate airspeed (Vmc) which resulted in a loss of control. Contributing factors were the improper in-flight planning/decision not to land at a closer airport and the lack of recent experience in multiengine airplanes by the…NTSB CHI01LA267Accident · No injuriesLimited investigationFinal reportPiper PA-32-300 · Racine, WIThe total loss of engine power during cruise flight due to the number 3 cylinder thru-studs fatigue fractures, and the thru-stud's low hardness as a result improper heat treatment of the thru-studs, and the unsuitable terrain encountered during the forced…NTSB CHI01LA265Accident · No injuriesLimited investigationFinal reportCessna 172M · Milton, WIThe unsuitable terrain for landing encountered by the pilot. Factors were the high vegetation and the loss of engine power for undetermined reasons.NTSB CHI01LA276Accident · Serious injuriesLimited investigationFinal reportEfaw Casutt Sport Racer · Shell Lake, WIThe pilot's inadequate preflight planning/preparation and the misjudged flare that resulted in a hard landing. A factor to the accident was the loss of engine power due to fuel exhaustion.NTSB CHI01LA004Accident · Minor injuriesLimited investigationFinal reportCessna 172M · Sparta, WIthe pilot's inaccurate fuel consumption calculations, improper fuel management, inadequate in-flight planning/decision, flight to destination alternate not performed, and the discontinued planned approach to the initially selected runway. Contributing factors…NTSB CHI00LA268Accident · Serious injuriesLimited investigationFinal reportBoeing 777-223 · Baraboo, WIthe unexpected turbulence encountered by the airplane.NTSB CHI00FA262Accident · 2 fatalField investigationFinal reportBell OH-58C · Watertown, WIThe spatial disorientation by the pilot during continued flight into instrument conditions and the inadequate surveillance by the operator. The inoperative attitude indicator and night conditions were contributing factors.NTSB CHI00LA235Accident · No injuriesLimited investigationFinal reportPiper PA-28-160 · Burlington, WIloss of engine power for undetermined reasons and the pilot's failure to maintain clearance from the fence during the forced landing attempt. Factors relating to the accident were the pilot's improper in-flight planning and decision to turn back toward the…NTSB CHI00FA227Accident · 4 fatalField investigationFinal reportCessna 182A · Sauk City, WIVFR flight into IMC encountered by the pilot and exceeding the design limits of the aircraf. A factor was flight to a destination alternate not performed by the pilot.NTSB CHI00LA209Accident · No injuriesLimited investigationFinal reportPiper PA-28-181 · Ashland, WIthe blocked lubricating system and unsuitable terrain.NTSB CHI00LA073Accident · No injuriesLimited investigationFinal reportPiper PA-23-250 · Amery, WIthe pilots failure to maintain directional control. Factors to the accident were the icing conditions, the snow and ice covered runway and the snowbank.NTSB CHI99LA320Accident · Serious injuriesLimited investigationFinal reportCessna 172D · South Wayne, WIthe pilot's improper selection of an emergency field and failure to maintain clearance from the trees. The cause of the loss of engine power was not determined.NTSB CHI99LA270Accident · Minor injuriesLimited investigationFinal reportBeech C23 · Port Washington, WIthe loss of engine power for undetermined reasons.NTSB CHI99LA186Accident · No injuriesLimited investigationFinal reportNorth American Navion L-17A · Osceola, WIthe pilot's mismanagement of the fuel system in that he had the fuel selector positioned to a tank which did not contain sufficient fuel for the flight. Factors associated with the accident were the crop and the ditch which the airplane encountered during the…NTSB CHI99LA184Accident · Serious injuriesLimited investigationFinal reportBoeing 777-222 · Madison, WIthe pilot-in-command's inadequate evaluation of the weather conditions. Factors associated with the accident were the turbulence encountered and the seat belt sign was not illuminated.NTSB CHI99LA109Accident · No injuriesLimited investigationFinal reportPiper J5A · Wausau, WIthe loss of engine power for undetermined reasons and the lack of suitable terrain for landing. A factor to the accident was the aircraft owner not performing necessary maintenance to the aircraft.NTSB CHI99LA048Accident · No injuriesLimited investigationFinal reportBellanca 8GCBC · Burlington, WICarburetor ice, the pilot's inadequate pre-flight planning/preparation resulting in his flight into poor weather conditions, and the pilot's improper use of carburetor heat. Factors contributing to this accident were the carburetor icing conditions, the…NTSB CHI98LA367Accident · Minor injuriesLimited investigationFinal reportPiper PA-34-220T · Schofield, WIThe inadvertent deactivation of the fuel selector and the emergency procedure not followed by the pilot-in-command. The tree was a contributing factor.NTSB CHI98FA349Accident · 3 fatalField investigationFinal reportBeech 58 · Oakdale, WIThe installation of the door seal inflation system by company personnel. An additional cause was the emergency procedures not followed by the pilot-in-command. A contributing factor was the trees.NTSB CHI98FA356Accident · 2 fatalField investigationFinal reportCessna 310R · Seymour Twp., WIthe pilots of both airplanes not maintaining visual separation from the other airplane.NTSB CHI98FA343Accident · 2 fatalField investigationFinal reportPiper PA-22-150 · Beaver Dam, WIThe continued flight into adverse weather conditions and aircraft control not maintained by the pilot-in-command. Contributing factors were the improper preflight planning/preparation and lack of certification by the pilot-in-command in addition to the…NTSB CHI98FA344Accident · 2 fatalField investigationFinal reportCessna A185F · Phillips, WIThe pilot's failure to maintain adequate altitude/clearance with trees. Contributing factors were fog, rain showers, and the trees.NTSB CHI98FA287Accident · 1 fatalField investigationFinal reportNavion A · Coloma, WIFailure of the pilot to maintain proper altitude, and his failure to maintain adequate visual lookout to see and avoid obstructions. The transmission wire was a contributing factor.NTSB CHI98LA283Accident · No injuriesLimited investigationFinal reportAero Commander 685 · Middleton, WIThe fuel starvation of the aircraft in addition to the improper aircraft preflight and premature gear extension by the pilot-in-command. The blocked fuel vent system is a contributing factor.NTSB CHI98LA176Accident · Minor injuriesLimited investigationFinal reportWaters Vortex · Monroe, WIthe pilot's failure to maintain rotor RPM. A factor associated with the accident was the soft terrain on which the gyrocopter touched down.NTSB CHI98LA171Accident · No injuriesLimited investigationFinal reportStemme S10V · Plymouth, WIthe improper in-flight decision of the pilot in command to initiate a turn at a close proximity to the ground. Factors to the accident were the starting procedures not being followed and the inadequate altitude to initiate the engine restart.NTSB CHI98FA153Accident · 4 fatalField investigationFinal reportCommander 114-B · Lagrange, WIthe pilot not maintaining aircraft control during cruise flight and exceeding the airplane's design stress limits. Factors in this accident was the pilot's lack of total experience in the airplane and the clouds.NTSB CHI98IA136Incident · No injuriesIncident investigationFinal reportRobinson R22 Beta · Franklin, WIThe mechanic's failure to follow the manufacturer's maintenance rebuilding instructions.