Final NTSB investigations, 2012
12 cases where the phase of flight was Unknown.
Cases per year
| 1962 | 1 |
|---|---|
| 1963 | 1 |
| 1964 | 29 |
| 1965 | 26 |
| 1966 | 36 |
| 1967 | 48 |
| 1968 | 59 |
| 1969 | 31 |
| 1970 | 33 |
| 1971 | 22 |
| 1972 | 30 |
| 1973 | 28 |
| 1974 | 27 |
| 1975 | 40 |
| 1976 | 15 |
| 1977 | 30 |
| 1978 | 38 |
| 1979 | 41 |
| 1980 | 30 |
| 1981 | 27 |
| 1982 | 51 |
| 1983 | 46 |
| 1984 | 47 |
| 1985 | 41 |
| 1986 | 18 |
| 1987 | 23 |
| 1988 | 24 |
| 1989 | 20 |
| 1990 | 25 |
| 1991 | 15 |
| 1992 | 22 |
| 1993 | 22 |
| 1994 | 12 |
| 1995 | 13 |
| 1996 | 19 |
| 1997 | 16 |
| 1998 | 20 |
| 1999 | 23 |
| 2000 | 13 |
| 2001 | 18 |
| 2002 | 9 |
| 2003 | 12 |
| 2004 | 6 |
| 2005 | 11 |
| 2006 | 7 |
| 2007 | 10 |
| 2008 | 4 |
| 2009 | 8 |
| 2010 | 7 |
| 2011 | 5 |
| 2012 | 12 |
| 2013 | 5 |
| 2014 | 7 |
| 2015 | 5 |
| 2016 | 6 |
| 2017 | 4 |
| 2018 | 7 |
| 2019 | 6 |
| 2020 | 11 |
| 2021 | 13 |
| 2022 | 10 |
| 2023 | 5 |
| 2024 | 11 |
| 2025 | 3 |
Where
Latest cases
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 year(c.\"eventDate\") = 2012
AND c.\"completionStatus\" = 'Completed'
AND 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.
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 year(c."eventDate") = 2012
AND c."completionStatus" = 'Completed'
AND c."mKey" IN (SELECT case_mkey FROM case_phases WHERE main = 990)
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 ANC13FAMS1Accident · 1 fatalMissing aircraftFinal reportPiper PA-18-150 · Kenai, AKUndetermined. The airplane and pilot were not found.NTSB ERA12LA537Accident · 2 fatalLimited investigationFinal reportQuad City Ultralights Challenger II · Ocala, FLThe pilot's inadvertent overstress of the airplane, which resulted in the in-flight separation of the outer section of the right wing.NTSB WPR12FAMS1Accident · 1 fatalMissing aircraftFinal reportJeffko Glasair · Tonasket, WAThe cause of the accident is undetermined as the airplane has not been located and remains missing.NTSB ERA12CA466Accident · No injuriesData collectionFinal reportCessna 172P · St. Augustine, FLUndetermined due to a lack of information about when the damage to the airplane occurred.NTSB ERA12FAMS2Accident · 1 fatalMissing aircraftFinal reportCessna 150F · Atlantic Ocean (Atlantic Ocean)Undetermined (airplane and pilot missing).NTSB CEN12FAMS1Accident · 1 fatalMissing aircraftFinal reportPiper PA-31-325 · Duluth, MNUndetermined because the airplane was not found.NTSB CEN12LA352Accident · No injuriesLimited investigationFinal reportGrumman G164 · Higginson, ARThe partial loss of engine power for reasons that could not be determined due to impact and fire damage.NTSB WPR12LA314Accident · No injuriesLimited investigationFinal reportCessna 182R · Hilo, HIThe damage to the engine firewall for a reason that was undetermined because the damage was not reported when it occurred.NTSB ERA12LA360Accident · No injuriesLimited investigationFinal reportPiper PA-28R-201 · Kingston, NYThe pilot’s failure to maintain adequate clearance from trees/foliage at an undetermined point during the flight.NTSB WPR12FA216Accident · 2 fatalField investigationFinal reportAero Vodochody L-39 · Boulder City, NVAn in-flight emergency followed by a collision with terrain for reasons that could not be determined because postaccident examination of the airframe, engine, and forward and aft canopies revealed no evidence of mechanical malfunctions or failures that would…NTSB WPR12TA445Accident · No injuriesFinal reportBeech C90 · Boise, IDExcessive loading on the left wing, which occurred over a period of time. Contributing to the accident was maintenance personnel’s failure to adequately inspect and document the left wing damage.NTSB ERA12FAMS1Accident · 1 fatalMissing aircraftFinal reportCessna 182P · Atlantic Ocean (Atlantic Ocean)Undetermined; the airplane and pilot are missing.