Final NTSB investigations
2,183 cases where the phase of flight was Uncontrolled descent.
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.\"completionStatus\" = 'Completed'
AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 650)
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."completionStatus" = 'Completed'
AND c."mKey" IN (SELECT case_mkey FROM case_phases WHERE main = 650)
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 NYC81FA070Accident · 6 fatalClosedPiper PA-32 · Easton, PANTSB ATL81FA087Accident · 4 fatalClosedPiper PA-32R · Vicksburg, MSNTSB MKC81FA040Accident · 1 fatalClosedCessna 404 · Belton, KSNTSB FTW81FA105Accident · 2 fatalClosedSwearingen SA226T · Cameron, LANTSB FTW81FA121Accident · 2 fatalClosedMooney M20J · Eagletown, OKNTSB LAX81FA091Accident · 1 fatalClosedPiper PA-30 · Moreno, CANTSB SEA81FA038Accident · 3 fatalClosedPiper PA-28 · Malad City, IDNTSB MKC81FA036Accident · 2 fatalClosedCessna 172 · Carbondale, KSNTSB ATL81FA074Accident · 2 fatalClosedBell 47G2A1 · Covington, KYNTSB DEN81FA035Accident · 4 fatalClosedCessna T210N · Castle Rock, CONTSB FTW81FA089Accident · 2 fatalClosedCessna 337A · Evant, TXNTSB FTW81FA090Accident · 1 fatalClosedCessna 172A · Verda, LANTSB NYC81FA047Accident · 4 fatalClosedCessna 177B · Forked River, NJNTSB DEN81FA032Accident · 3 fatalClosedCessna T210G · Worland, WYNTSB ATL81FA071Accident · 1 fatalClosedMooney M20J · Cades, SCNTSB CHI81DEV17Accident · No injuriesClosedBalloon WKS 7B · Normal, ILNTSB FTW81FA083Accident · 3 fatalClosedPiper PA-31 · Hammond, TXNTSB LAX81FA080Accident · 1 fatalClosedCessna 170B · Steamboat, NVNTSB FTW81FA077Accident · 1 fatalClosedBeech A36 · Houston, TXNTSB FTW81FA074Accident · 4 fatalClosedBeech E35 · Fay, OKNTSB FTW81FA073Accident · 4 fatalClosedCessna 172N · Yoakum, TXNTSB NYC81FA040Accident · 1 fatalClosedPiper PA-28R · Hartford, NYNTSB MKC81FA028Accident · 1 fatalClosedPiper PA-28 · Dixon, IANTSB MKC81FA027Accident · 2 fatalClosedPiper PA-28 · Creston, IANTSB FTW81FQG22Accident · Serious injuriesClosedQuickie 01 · Abernathy, TXNTSB MKC81FA026Accident · 2 fatalClosedCessna 310R · Eldon, MONTSB FTW81EA065Accident · 1 fatalClosedCessna 152 · Hempstead, TXNTSB DEN81FA023Accident · 2 fatalClosedMooney M20G · Ryegate, MTNTSB LAX81FA063Accident · 2 fatalClosedBeech B35 · Winkelman, AZNTSB CHI81FA034Accident · 2 fatalClosedCessna 172 · Lake Geneva, WINTSB CHI81FA032Accident · 2 fatalClosedBeech P35 · Monarch Pass, CONTSB SEA81FA020Accident · 2 fatalClosedCessna 182P · Everett, WANTSB ATL81FA051Accident · 1 fatalClosedPiper PA-30 · Autaugaville, ALNTSB FTW81FA041Accident · 5 fatalClosedBeech A36TC · Nageezi, NMNTSB FTW81FA040Accident · 2 fatalClosedBeech 58P · Ozona, TXNTSB NYC81FA029Accident · 4 fatalClosedMooney M20F · Montgomery Twp, NJNTSB CHI81FA026Accident · 3 fatalClosedPiper PA-28 · Waupun, WINTSB LAX81FA072Accident · 4 fatalClosedPiper PA-28 · Truckee, CANTSB FTW81FA027Accident · 2 fatalClosedPiper PA-23 · Odessa, TXNTSB CHI81FA022Accident · 1 fatalClosedCessna 150M · Timewell, ILNTSB LAX81FA037Accident · 4 fatalClosedBeech S35 · Casa Grande, AZNTSB LAX81FA034Accident · 6 fatalClosedBeech A36 · Peach Springs, AZNTSB SEA81DYM15Accident · No injuriesClosedBell 206A · Mill City, ORNTSB FTW81FA021Accident · 2 fatalClosedBellanca 17-30A · Iraan, TXNTSB MKC81FA012Accident · No injuriesClosedBeech F35 · Paris, TNNTSB CHI81FA014Accident · 2 fatalClosedBeech G35 · Harding, WINTSB NYC81DFA09Accident · No injuriesClosedPiper PA-28 · Palenville, NYNTSB LAX81FA026Accident · 3 fatalClosedMooney M20K · Battle Mt, NVNTSB DEN81FA011Accident · 2 fatalClosedCessna 310K · Boulder, CONTSB SEA81FA012Accident · 2 fatalClosedPiper PA-34 · Stevenson, WA