NTSB investigations, IA
408 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\" = 'IA' 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" = 'IA' 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 CHI06FA026Accident · 2 fatalField investigationFinal reportPiper PA-31-350 · Ankeny, IAThe pilot's failure to preflight the airplane, the pilot's improper in-flight decision not to land the airplane on the runway when he had the opportunity, and the inadvertent stall when the pilot allowed the airspeed to get too low. Factors that contributed…NTSB CHI05CA209Accident · No injuriesData collectionFinal reportNational Ballooning 751-12 · Indianola, IAThe in-flight fire due to the loose fuel line connection as a result of the pilot's inadequate preflight. A factor was the power lines.NTSB CHI05LA113Accident · No injuriesLimited investigationFinal reportMattson Zenith 601XL · Dike, IAA loss of engine power for undetermined reasons and the pilot misjudged the forced landing approach. Factors associated with the accident were the fence and the ditch which the airplane contacted.NTSB CHI05CA041Accident · No injuriesData collectionFinal reportPiper PA-30 · Atlantic, IAThe pilot's inadequate in-flight planning/decision which resulted in fuel exhaustion. The dark night light conditions was a contributing factor.NTSB CHI04LA183Accident · No injuriesLimited investigationFinal reportCessna 340A · Mason City, IAThe proper touchdown point not obtain/maintained by the pilot following a single-engine approach to landing. Contributing factors were the vacuum pump oil leak, the low oil pressure of the propeller control system, and the unfeathered propeller.NTSB CHI04LA187Accident · No injuriesLimited investigationFinal reportBeech A36 · Algona, IAThe failure of the connecting rod which resulted in the loss of engine power during cruise flight and subsequent forced landing. A contributing factor was the delayed lowering of the landing gear in order to clear the other vehicle encountered during the…NTSB CHI04FA044Accident · 4 fatalField investigationFinal reportPiper PA-32-260 · Brooklyn, IAThe pilot's inadequate planning/decision resulting in VFR flight into IMC, and his failure to maintain terrain clearance resulting in in-flight collision with terrain. Contributing factors to the accident were the pilot's lack of an instrument rating, his…NTSB CHI04LA019Accident · No injuriesLimited investigationFinal reportBeech A36 · Davenport, IAA loss of engine power due to fuel starvation as a result of the improper installation of the fuel nozzle. Also causal was the engine manufacturer's improper installation instructions for the nozzle pressure port plug on the fuel manifold valve. A factor…NTSB CHI04LA001Accident · Serious injuriesLimited investigationFinal reportCessna 182A · Shenandoah, IALoss of engine power for an undetermined reason during cruise and the unsuitable terrain for landing encountered by the pilot. The factors to the accident were the dark night, the corn crop, and the ditch.NTSB CHI03LA217Accident · No injuriesLimited investigationFinal reportEnstrom F-28C · Waverly, IAThe main rotor blade push-pull rods being corroded, sustaining fatigue, and separating in cruise and the main rotor blade contacting the tail boom during the emergency landing. Factors were the diminished aircraft control and the vibrations encountered during…NTSB CHI03FA094Accident · 3 fatalField investigationFinal reportBeech A36 · New Vienna, IAThe improper planning/decision by the pilot to fly into forecast icing conditions with an airplane not equipped with a certified deice system. The continued flight in known icing conditions and the flight to an alternate destination not performed by the pilot…NTSB CHI03LA059Accident · No injuriesLimited investigationFinal reportCessna 210 · Clarence, IAA failure of the crankshaft for undetermined reasons. Factors associated with the accident were the rough terrain on which the forced landing was made and the night conditions.NTSB CHI03LA049Accident · No injuriesLimited investigationFinal reportCessna 150J · Garner, IAThe students pilots improper in-flight fuel planning resulting in exhaustion of the fuel supply, and the collapse of the nose gear during the forced landing. Factors were unsuitable landing are encountered by the pilot, the plowed field and the fuel…NTSB CHI02LA286Accident · Serious injuriesLimited investigationFinal reportBailey DSA-1 · Washington, IAThe selection of unsuitable terrain and the failure of the oil pressure gage line. Factors were, the pilot/owners failure to obtain an annual condition inspection, and the corn crop.NTSB CHI01FA230Accident · 2 fatalField investigationFinal reportBeech K35 · Grafton, IAThe pilot's improper inflight decision to continue into known adverse weather. A factor was the thunderstorm.NTSB CHI01LA029Accident · Minor injuriesLimited investigationFinal reportFowler Varieze · Iowa City, IAthe inadequate preflight by the pilot which resulted in the fuel drain remaining partially open. Factors to the accident were the loss of engine power due to fuel starvation, the partially open fuel drain, the unsuitable terrain for landing encountered by the…NTSB CHI00LA286Accident · No injuriesLimited investigationFinal reportPiper PA-32R-300 · Mason City, IAthe unsuitable terrain for landing encountered by the pilot and the failure of the oil filter converter plate gasket. Factors to the accident were the total loss of oil and the soft terrain condition.NTSB CHI00FA171Accident · 1 fatalField investigationFinal reportCessna 210J · Sumner, IAfailure of the pilot to maintain clearance with the terrain after flight into adverse weather. Contributing factors were the thunderstorms and lack of instrument certification by the pilot.NTSB CHI00LA052Accident · Serious injuriesLimited investigationFinal reportCessna T210F · Indianola, IAloss of engine power for undetermined reason. A factor relating to this accident was the tree.NTSB CHI00LA014Accident · 1 fatalLimited investigationFinal reportHowlett KR-2 · Denison, IAthe pilot not maintaining altitude/clearance from rising terrain and the intentional low altitude flight. Factors were the known turbulence, rising terrain, and the inadequate in-flight decision to fly at low altitudes.NTSB CHI99FA356Accident · 1 fatalField investigationFinal reportPiper PA-28-140 · Clinton, IAthe pilot's continued flight into known adverse weather conditions and not maintaining altitude/clearance. Factors were the low clouds, rain, and dark night conditions.NTSB CHI99LA348Accident · No injuriesLimited investigationFinal reportCessna 172N · Des Moines, IAthe pilot's improper in-flight planning and decisions which led to fuel exhaustion. A factor relating to this accident was the telephone pole.NTSB CHI99IA136Incident · No injuriesIncident investigationFinal reportMcDonnell Douglas DC-9-32 · Dubuque, IAthe frozen elevator control. Factors to the incident were the rain and the low temperature.NTSB CHI99FA052Accident · 1 fatalField investigationFinal reportCessna 152 · Sherrill, IAthe pilot continued flying with a known discrepancy and he failed to maintain airspeed and lost control of the airplane while attempting a forced landing. Factors included the tailwind and the undetermined loss of power.NTSB CHI99LA034Accident · No injuriesLimited investigationFinal reportPiper PA-12 · Carrol, IAthe pilot's inadequate preflight inpection of the fuel tanks and filler neck caps before departure. Factors associated with this accident were the leaking right wing fuel tank filler cap and fuel exhaution.NTSB CHI99LA032Accident · No injuriesLimited investigationFinal reportPiper PA-28-180 · Iowa Falls, IAthe pilot's improper preflight planning and preparation, and his not refueling the airplane. Factors were fuel exhaustion and the fence.NTSB CHI99LA012Accident · No injuriesLimited investigationFinal reportHiller UH-12C · Clinton, IAthe failure of the 90 degree gearbox for reasons undetermined. Factors were the failure of the gearbox, and it not being possible for the pilot to maintain control of the aircraft.NTSB CHI98FA323Accident · 3 fatalField investigationFinal reportBell 222 · Spencer, IAThe below standard (loose) fit of the 'white' swashplate outer ring pin resulting in fatigue and subsequent separation of the pin, and ultimately the in-flight breakup of the rotorcraft. A factor was the manufacturer's dissemination of the pin which did not…NTSB CHI98FA309Accident · 3 fatalField investigationFinal reportPiper PA-28R-200 · New Hampton, IAThe pilot's inadequate preflight/planning inspection and his continued flight into known adverse weather conditions that led to spatial disorientation and loss of aircraft control. Also causal was the pilot exceeded the design limits of the airplane causing…NTSB CHI98LA184Accident · No injuriesLimited investigationFinal reportBalloon Works Firefly 7-15 · Waterloo, IAThe pilot's failure to maintain clearance with the power lines during climb. A factor was the power lines.NTSB CHI97LA275Accident · Minor injuriesLimited investigationFinal reportBucker Flugzeugbau Jungmann · Blakesburg, IALoss of engine power for undetermined reasons.NTSB CHI97LA229Accident · Minor injuriesLimited investigationFinal reportPiper PA-28-140 · Mapleton, IAthe loss of engine power for undetermined reasons. A factor was the cornfield in which the pilot was forced to land.NTSB CHI97LA097Accident · No injuriesLimited investigationFinal reportPiper PA-28-235 · Des Moines, IAoil contamination and starvation due to the inadequate maintenance inspection of the pilot/owner. Factors included the deterioration of the piston pin plug, the connecting rod failure, and the unsuitable terrain for landing.NTSB CHI97IA072Incident · No injuriesIncident investigationFinal reportMcDonnell Douglas DC-9-15 · Des Moines, IAthe cross tie relay shorted resulting in a fire in the left main electrical equipment rack.NTSB CHI97LA050Accident · No injuriesLimited investigationFinal reportPiper PA-46 · Red Oak, IAthe pilot's inadequate preflight and failure to make a precautionary landing. Additional factors included oil starvation and the failed connecting rod and rod bearing.NTSB CHI96TA306Accident · Minor injuriesFinal reportHughes 269A · Cedar Rapids, IApartial blocking of the fuel injection control, and a fracture of the induction air ducting for reasons undetermined. A factor was trees.NTSB CHI96IA173Incident · No injuriesIncident investigationFinal reportBritish Aerospace BAE 146 · Des Moines, IAfailure of the flight crew to follow proper procedures, which resulted in an accumulation of ice and subsequent loss of engine power. Factors relating to the incident were: thunderstorms, turbulence, icing conditions, and the inaccurate method that was…NTSB CHI96FA146Accident · 3 fatalField investigationFinal reportCessna 421 · Bernard, IAthe pilot's operation of the airplane with known deficiencies, subsequent loss of oil from the left engine, and the pilot's failure to maintain minimum controllable airspeed (VMC), while diverting to an alternate airport. Factors relating to the accident…NTSB CHI96LA020Accident · No injuriesLimited investigationFinal reportCessna 180A · Goose Lake, IAinadequate maintenance, inadequate preflight by the pilot, and a loose fuel tank cap that allowed siphoning of fuel, buckling of the right fuel bladder, fuel exhaustion, and loss of engine power. Factors relating to the accident were: darkness, and the soft…NTSB CHI96LA013Accident · No injuriesLimited investigationFinal reportCessna 150 · Peosta, IAthe pilot's inadequate aircraft preflight inspection and failure to refuel the airplane prior to fuel exhaustion.NTSB CHI96LA011Accident · No injuriesLimited investigationFinal reportMcDonnell Douglas DC-9-31 · Cedar Rapids, IAinadequate installation of a thrust reverser, driver linkage arm pivot bolt and nut, which resulted in the nut and bolt becoming disconnected. A factor relating to the accident was: the manufacturer's unclear information regarding the type and number of…NTSB CHI95FA071Accident · 2 fatalField investigationFinal reportBacon QUESTAIR-VENTURE · Des Moines, IAThe pilot's failure to maintain aircraft control during the emergency descent, resulting in a stall/spin. Factors were material defects in the material used to manufacture the crankshaft, resulting in the crankshaft fracturing after approximately 200 hours in…NTSB CHI95LA048Accident · Minor injuriesLimited investigationFinal reportPiper PA-28R-201T · La Porte City, IAa material defect which resulted in the fatigue failure of the crankshaft and the aircraft owner's failure to comply with a Mandatory Service Bulletin addressing the defect. A factor related to the accident was the rough terrain on which the forced landing…NTSB CHI94LA302Accident · Minor injuriesLimited investigationFinal reportFrith Challenger II · Fairfield, IAthe total failure and separation of the propeller system drive belt. Obstacles (powerlines and pole) were a factor in the accident.NTSB CHI94LA299Accident · Minor injuriesLimited investigationFinal reportRobert Eldon Idler TIERRA-2 · Prole, IAthe airplane's encounter with the outflow (gust front) from an approaching thunderstorm. Factors were the pilot's inaccurate evaluation of the gust front location, and the inability of the airplanes flight control system to compensate for the rolling moment.NTSB CHI94LA057Accident · Serious injuriesLimited investigationFinal reportCessna 182 · Centerville, IAthe pilot's failure to refuel the airplane during the flight. Factors related to the accident were the inaccurate fuel consumption calculations for the airplane and the fence.NTSB CHI93LA380Accident · No injuriesLimited investigationFinal reportCessna 177B · Fayette, IAthe pilot-in-command's failure to refuel the airplane. A factor was a road sign and fuel exhaustion.NTSB CHI93LA356Accident · No injuriesLimited investigationFinal reportCessna 150H · Crescent, IAImproper in-flight planning/decision by the pilot, after encountering unfavorable winds and a low fuel level, which resulted in fuel exhaustion. The unfavorable wind was a related factor.NTSB CHI93LA345Accident · No injuriesLimited investigationFinal reportBeech P35 · Ottumwa, IAan oil leak in the oil filter adapter. A factor was landing in a crop field.NTSB CHI93LA197Accident · No injuriesLimited investigationFinal reportCessna 210L · Indianola, IAA fatigue fracture of the crankshaft.