NTSB investigations
20,559 cases where the phase of flight was Initial climb.
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 = 350)
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."mKey" IN (SELECT case_mkey FROM case_phases WHERE main = 350)
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 MIA70DKJ45Accident · No injuriesClosedCessna 150 · Laceys Sprngs, ALNTSB LAX70DUJ45Accident · Minor injuriesClosedCessna 182 · Kayenta, AZNTSB ANC70FAG35Accident · Serious injuriesClosedTaylorcraft BC12-D · Dillingham, AKNTSB FTW70FRG52Accident · Serious injuriesClosedCessna 150 · Eagle Pass, TXNTSB CHI70FGD27Accident · Serious injuriesClosedCessna 172 · Middle Bass, OHNTSB CHI70FBA25Accident · Serious injuriesClosedPiper PA-28 · Richmond, INNTSB ANC70AA089Accident · Serious injuriesClosedCessna 185 · Glenn Allen, AKNTSB FTW70FPG37Accident · No injuriesClosedBoeing A75 · Brinkley, ARNTSB FTW70FRA30Accident · No injuriesClosedCessna 150 · Pearland, TXNTSB FTW70FRA37Accident · No injuriesClosedCessna 140 · East Bernard, TXNTSB MKC70DCJ30Accident · No injuriesClosedCessna 170B · Wolbach, NENTSB LAX70AL073Accident · 1 fatalClosedCessna 150F · New River, AZNTSB CHI70DBJ37Accident · No injuriesClosedBeech A23 · Chesaning, MINTSB ANC70FAG31Accident · No injuriesClosedPiper PA-12 · Wasilla, AKNTSB IAD71FLJ07Accident · No injuriesClosedCessna 150G · White Lake, NCNTSB CHI70FDJ23Accident · Serious injuriesClosedChampion 7ECA · Appleton, WINTSB CHI70FBD48Accident · Minor injuriesClosedCessna 177 · W.Chicago, ILNTSB IAD70FIG18Accident · Minor injuriesClosedTaylorcraft DCO-65 · Cumberland, MDNTSB CHI70FDW21Accident · No injuriesClosedPiper PA-25 · Linden, MINTSB DEN70DXD45Accident · No injuriesClosedAlon A2-A · Boulder, CONTSB CAN71AW902Accident · Minor injuriesClosedBellanca 17-30 · Livingston Ck, CanadaNTSB ANC70DAA14Accident · Minor injuriesClosedChampion 7GCBC · Circle City, AKNTSB SEA70FWD19Accident · No injuriesClosedCessna 172 · Puyallup, WANTSB NYC70FHM14Accident · Minor injuriesClosedPiper PA-28 · Spring Valley, NYNTSB CHI70DBM20Accident · Minor injuriesClosedCessna 172 · Peru, INNTSB NYC70FHG07Accident · Serious injuriesClosedCessna 172 · Forksville, PANTSB NYC70FHD34Accident · No injuriesClosedBeech K35 · Salem, NJNTSB FTW70FRA25Accident · No injuriesClosedChampion 7FC · Dayton, TXNTSB CHI70DBD43Accident · No injuriesClosedBeech A-23A · Durand, ILNTSB ANC70DAG27Accident · Minor injuriesClosedPiper PA-18 · Willow, AKNTSB MKC70FDD20Accident · No injuriesClosedPiper PA-18 · Rothsay, MNNTSB MIA70DLD29Accident · No injuriesClosedCessna T337D · St Petersburg, FLNTSB FTW70FRA27Accident · No injuriesClosedGrumman G-164 · Garwood, TXNTSB FTW71DRG26Accident · No injuriesClosedPiper PA-11 · Luling, TXNTSB MKC70AK068Accident · No injuriesClosedBeech E18S · Detroit Lake, MNNTSB SEA70DWD17Accident · No injuriesClosedCessna 172 · Oakville, WANTSB MIA70FLA22Accident · Serious injuriesClosedPiper PA-28 · Mayo, FLNTSB MKC70FDQ08Accident · No injuriesClosedMooney M20C · Miller, SDNTSB MKC70FCD33Accident · Serious injuriesClosedPiper PA-22 · Atlantic, IANTSB FTW70AF078Accident · 2 fatalClosedCessna 150 · Carrizo Sprgs, TXNTSB DEN70FQA29Accident · No injuriesClosedTibbetts M Mouz · Albuquerque, NMNTSB CAN71A0902Accident · 2 fatalClosedPiper PA-18 · Ontario, CanadaNTSB FTW70DRA29Accident · No injuriesClosedBoeing E75N1 · Beaumont, TXNTSB DEN70FXD28Accident · No injuriesClosedLuscombe T-8F · Broomfield, CONTSB FTW70FPG36Accident · No injuriesClosedBeech T-34A · Clinton, ARNTSB MKC70FCJ26Accident · No injuriesClosedPiper PA-25 · Gurley, NENTSB CHI70FGG21Accident · Minor injuriesClosedTaylorcraft BL-65 · Columbus, OHNTSB CHI70FBA20Accident · No injuriesClosedBeech 35 · Kokomo, INNTSB MKC70DCD35Accident · No injuriesClosedBeech A35 · Newton, IANTSB FTW70FRG45Accident · Minor injuriesClosedBell 47D-1 · San Antonio, TX