OpenAirDocs.org

NTSB investigations

1,978 cases where the phase of flight was Unknown.

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.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 990)
  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 DFW07LA170Accident · 1 fatalLimited investigationFinal reportHazen Fly Baby · Page, OKThe airplane's collision with wooded and mountainous terrain for undetermined reasons.Jul 20, 2007 · N6156Y · Substantial damageNTSB LAX07FAMS1Accident · 1 fatalMissing aircraftFinal reportCub Crafters CC11-100 · Aurora, ORThe airplane is missing.Jul 7, 2007 · N222TB · DestroyedNTSB DFW07LA160Accident · 1 fatalLimited investigationFinal reportIngraham RV-6 · Carlsbad, NMThe pilot's loss of control for undetermined reasons.Feb 28, 2007 · N320TX · DestroyedNTSB SEA07LA050Accident · No injuriesLimited investigationFinal reportPiper PA-28R-180 · Fresno, CAThe design stress limits of the airframe were exceeded by an unknown pilot.Dec 21, 2006 · N4666J · Substantial damageNTSB LAX06LA293Accident · 2 fatalLimited investigationFinal reportChampion 7EC · Clipper Mills, CAThe pilot failed to maintain clearance from mountainous terrain. A contributing factor was the pilot's impairment from ingestion of marijuana.Sep 10, 2006 · N9575S · DestroyedNTSB CHI06LA258Accident · 1 fatalLimited investigationFinal reportGarner Garner Falcon · Newton, IAAn inflight collision with terrain during an unknown phase of flight. A factor was the pilot's lack of experience.Sep 9, 2006 · N6194Y · DestroyedNTSB ANC06FAMS1Accident · 3 fatalMissing aircraftFinal reportAero Commander 690A · Anchorage, AKUndetermined; the airplane and its occupants are missing.Jul 29, 2006 · N57096 · DestroyedNTSB CHI06LA178Accident · 1 fatalLimited investigationFinal reportMoon Rans S-14 · Berlin, ILThe pilot's failure to maintain control of the airplane for undetermined reasons.Jul 7, 2006 · N43293 · DestroyedNTSB DFW06CA175Accident · No injuriesData collectionFinal reportCessna C340A · San Antonio, TXThe collapse of the left main landing gear as result of the failure of the gear pivot bolt for undetermined reasons.Jul 5, 2006 · N2745F · Substantial damageNTSB LAX06LA123Accident · 1 fatalLimited investigationFinal reportSchweizer 269C-1 · Valencia, CAThe certified flight instructor's failure to maintain an adequate clearance from objects.Feb 25, 2006 · N61466 · DestroyedNTSB LAX06FA059Accident · 1 fatalField investigationFinal reportCessna 172S · Hana, HICollision with mountainous terrain for undetermined reasons.Dec 16, 2005 · N542SP · Substantial damageNTSB LAX06CA039Accident · No injuriesData collectionFinal reportCessna 182T · Livermore, CAsubstantial damage to the airplane during unknown phase of flight for undetermined reasons.Oct 30, 2005 · N51732 · Substantial damageNTSB DFW06FAMS1Accident · 3 fatalMissing aircraftFinal reportBell 206-L3 · Gulf of MexicoUndetermined. The aircraft is missing.Oct 6, 2005 · N6560K · DestroyedNTSB CHI05FA274Accident · 3 fatalField investigationFinal reportRobinson R44 · Drummond, WIClearance not obtained/maintained by the pilot during an unknown phase of flight. Contributing factors were the improper use of procedures by the pilot, the inadequate surveillance of the operation by company/management, and the wire.Sep 24, 2005 · N9158U · Substantial damageNTSB SEA05FA180Accident · 1 fatalField investigationFinal reportMaule Bee Dee M-4 · Ritter, ORThe loss of control for an undetermined reason which resulted in a ground collision.Aug 24, 2005 · N11WG · DestroyedNTSB ATL05LA140Accident · 2 fatalLimited investigationFinal reportAllegro 2000 · Supply, NCThe instructor pilot's failure to maintain airspeed for unknown reasons, resulting in an aerodynamic stall and subsequent collision with the ground.Aug 7, 2005 · N9164M · Substantial damageNTSB SEA05IA115Incident · No injuriesIncident investigationFinal reportPiper PA-46-350P · Tacoma, WAThe initiation and propagation of a fatigue crack, over 5,273 hours of cyclic in-flight usage, through approximately 80 percent of the aircraft's right wing spar lower cap.Jun 2, 2005 · N27PG · Minor damageNTSB CHI05LA125Accident · 1 fatalLimited investigationFinal reportHurley Searey · South Beloit, ILIn-flight collision with the ground for undetermined reasons.May 24, 2005 · N60667 · DestroyedNTSB LAX05LA227Accident · No injuriesLimited investigationFinal reportPiper PA-28R-200 · Phoenix, AZsubstantial damage to the airplane during an unknown phase of flight for undetermined reasons.Mar 11, 2005 · N2882R · Substantial damageNTSB NYC05IA052Incident · No injuriesIncident investigationFinal reportCessna 402C · Hyannis, MAThe mechanic's inadequate inspection, which failed to discover cracks in the left wing spar around wing station 114.Feb 11, 2005 · N403BK · Minor damageNTSB DFW05LA069Accident · 1 fatalLimited investigationFinal reportNolen Lite Squared · Holdenville, OKUndeterminedJan 14, 2005 · N222JN · DestroyedNTSB ANC04FAMS2Accident · 5 fatalMissing aircraftFinal reportde Havilland DHC-2 · Sitka, AKReason for occurrence is undetermined. The airplane is missing.Sep 20, 2004 · N712TS · DestroyedNTSB LAX04LA313Accident · 2 fatalLimited investigationFinal reportRedgate Europa Classic · Marble Canyon, AZan in-flight collision with terrain for undetermined reasons.Sep 2, 2004 · N912EE · DestroyedNTSB SEA04LA162Accident · No injuriesLimited investigationFinal reportUniversal Moulded Pdts. Monocoach · Pullman, WAThe failure of a landing gear oleo retaining nut safety clip (snap ring) at an unknown point in time, leading to the partial separation of the landing gear strut while in flight, and the full collapse of the strut during the subsequent landing. Factors…Aug 17, 2004 · N11872 · Substantial damageNTSB ATL04LA143Accident · No injuriesLimited investigationFinal reportCessna 172S · Winder, GAThe improper maintenance (over-pressurization) of the nose gear strut, which resulted in an overload failure of the nose gear upper torque link bolt. The airplane subsequently nosed over during an uncontrollable landing roll.Jul 20, 2004 · N2069S · Substantial damageNTSB SEA04LA110Accident · 1 fatalLimited investigationFinal reportQuicksilver MXL · Mountain Home, IDThe pilot's failure to maintain aircraft control during an undetermined in-flight operational phase resulting in an uncontrolled descent into the terrain. Factors include strong gusty winds in the area of the accident.Jun 18, 2004 · UNREG · Substantial damageNTSB FTW04LA085Accident · No injuriesLimited investigationFinal reportCessna 172K · Angleton, TXThe unqualified person's failure to maintain clearance with the power lines. A contributing factor was the night light conditions.Feb 29, 2004 · N79502 · DestroyedNTSB SEA04LA027Accident · 2 fatalLimited investigationFinal reportErickson SHA Glasair TD · Vansycle, ORThe pilot's failure to maintain adequate clearance with the anemometer pole/wires during low altitude flight. Contributing factors were the pole and the pilot's low altitude flight.Dec 15, 2003 · N434SW · DestroyedNTSB MIA04FAMS1Accident · 1 fatalMissing aircraftFinal reportPiper PA-32-300 · Missing, BahamasUndetermined.Nov 13, 2003 · N8224C · DestroyedNTSB DEN03FAMS1Accident · 2 fatalMissing aircraftFinal reportBeech F33A · Greer, AZThe airplane is missing for unknown reason.Sep 21, 2003 · N927JL · DestroyedNTSB CHI04LA134Accident · No injuriesLimited investigationFinal reportRobinson R22 Beta · Argusville, NDThe altitude/clearance not maintained from transmission wire(s) by the pilot during an unknown phase of flight. The transmission wire(s) were a contributing factor.Sep 5, 2003 · N2301F · Substantial damageNTSB ANC03FAMS1Accident · 2 fatalMissing aircraftFinal reportAlon A-2 (Aircoupe) · Ketchikan, AKUndetermined. .Aug 17, 2003 · N6520Q · DestroyedNTSB CHI03LA269Accident · 2 fatalLimited investigationFinal reportHolden Pulsar · Willmar, MNThe pilot's failure to maintain adequate airspeed resulting in an inadvertent stall/spin.Aug 16, 2003 · N78DT · DestroyedNTSB SEA03FA148Accident · 1 fatalField investigationFinal reportRobinson R22 Beta · Tulalip, WAA loss of control for unknown reasons.Jul 22, 2003 · N7190K · DestroyedNTSB ANC03LA067Accident · 3 fatalLimited investigationFinal reportCessna 180 · Anchor Point, AKAn in-flight collision with water for an undetermined reason.Jun 25, 2003 · N91303 · DestroyedNTSB LAX03FAMS3Accident · 1 fatalMissing aircraftFinal reportCessna 172B · Little River, CAAircraft is missing.May 26, 2003 · N7555X · DestroyedNTSB FTW03LA094Accident · No injuriesLimited investigationFinal reportCessna 210L · Morrilton, ARThe failure of the main landing gear to extend and lock, as a result of a hydraulic fluid leak caused by a chaffed and ruptured flex line.Feb 4, 2003 · N22432 · Substantial damageNTSB DEN03FA034Accident · 2 fatalField investigationFinal reportYakovlev YAK 52 · Midway, UTThe malfunction of the elevator control as a result of a jammed elevator control bell crank, rendering control of the airplane impossible. A contributing factor was the foreign object (nut) damage.Jan 16, 2003 · N2256J · DestroyedNTSB CHI03LA236Accident · 1 fatalLimited investigationFinal reportPolsgrove Bluegrass Express · Grafton, ILUndetermined.Jan 9, 2003 · N113YE · DestroyedNTSB LAX03FAMS1Accident · 1 fatalMissing aircraftFinal reportPiper PA-34-200T · Pacific Ocean (Pacific Ocean)undetermined, missing aircraft.Dec 15, 2002 · N101YK · DestroyedNTSB ANC03LA022Accident · 1 fatalLimited investigationFinal reportCessna 180 · Petersburg, AKAn in flight collision with ocean water for an undetermined reason.Dec 13, 2002 · N9701G · DestroyedNTSB CHI03FAMS1Accident · 2 fatalMissing aircraftFinal reportCessna 175 · MIUndetermined. Missing aircraft.Nov 11, 2002 · N7091M · Substantial damageNTSB MIA03LA012Accident · 2 fatalLimited investigationFinal reportPiper PA-46-350P · Memphis, TNThe cause of the accident is unknown as the airplane has not been located. A finding in the investigation was the fact that the pilot conducted an unstabilized approach; the flight was right, left, then right of the final approach course during the approach…Nov 3, 2002 · N167MA · DestroyedNTSB MIA02FAMS3Accident · 1 fatalMissing aircraftFinal reportPiper PA-36-300 · MissingThe falure of the pilot to arrive at the intended destination for undetermined reasons. A finding in the investigation was the fact that clouds with tops to 44,000 feet were noted 38 miles south of the airplane's last position.Sep 6, 2002 · N59684 · DestroyedNTSB ANC02FA070Accident · 2 fatalField investigationFinal reportZawada Super Cuby · Cantwell, AKThe pilot's failure to maintain adequate airspeed which resulted in an inadvertent stall, and subsequent collision with terrain. A contributing factor was the pilot's impairment from the effects of prescription painkilling drugs.Jul 4, 2002 · N43Z · Substantial damageNTSB SEA02LA098Accident · 1 fatalLimited investigationFinal reportUnknown Quicksilver MX II · Cook, WAFailure to maintain aircraft control. High winds were a factor.Jun 9, 2002 · UNREG · Substantial damageNTSB CHI02FA140Accident · 1 fatalField investigationFinal reportSchutte Ms. Ryan Special · Anderson, INThe pilot's failure to properly install the canard attach bolts prior to the flight and the resulting inflight separation of the canard.May 23, 2002 · N827CM · DestroyedNTSB FTW02LA134Accident · 1 fatalLimited investigationFinal reportRobinson Tierra II · Hooker, OKthe loss of control for an undetermined reason, which resulted in an in-flight collision with terrain.May 1, 2002 · N3960D · DestroyedNTSB SEA02LA037Accident · 2 fatalLimited investigationFinal reportMooney M20C · Netarts, ORReason for occurrence undetermined.Dec 11, 2001 · N5774Q · DestroyedNTSB LAX02LA027Accident · No injuriesLimited investigationFinal reportCessna 210K · North Las Vegas, NVThe failure of maintenance personnel to properly diagnose the cause of a reported maintenance discrepancy resulting in the airplane being released from an annual inspection with a worn hydraulic pump which lacked sufficient output to operate the landing gear.…Nov 10, 2001 · N8232M · Substantial damage