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 MKC72AK060Accident · 1 fatalClosedPiper J3C-65 · St.Joseph, MONTSB MIA72FKG99Accident · No injuriesClosedPiper PA-23 · Chamblee, GANTSB FTW72FPG75Accident · Minor injuriesClosedGrumman G-164 · Stuttgart, ARNTSB SEA72FYA33Accident · Serious injuriesClosedPiper PA-25 · Oakley, IDNTSB OAK72FVG57Accident · Minor injuriesClosedPiper PA-28 · Montague, CANTSB OAK72DVG60Accident · No injuriesClosedPiper PA-28 · Ruth, CANTSB NYC72FHM49Accident · No injuriesClosedMccullough J-2 · Hillburn, NYNTSB OAK72FXQ32Accident · No injuriesClosedCessna 210 · Winnemucca, NVNTSB NYC72FFH42Accident · Minor injuriesClosedPiper J-3 · New Salem, NYNTSB SEA72FTI18Accident · No injuriesClosedPiper PA-30 · Thmpson Flls, MTNTSB DEN72FTM16Accident · Minor injuriesClosedCessna 182A · Logan, UTNTSB CHI72FEV31Accident · Serious injuriesClosedBensen B8M · Bloomington, ILNTSB CHI72FEA41Accident · No injuriesClosedPiper PA-22 · Muncie, INNTSB OAK72FVD41Accident · 1 fatalClosedGen Dynamic L-13A · Lodi, CANTSB FTW72FRA54Accident · Minor injuriesClosedBoeing IB75A · Winnie, TXNTSB MIA72FKW26Accident · Serious injuriesClosedPiper PA-32 · Smithville, TNNTSB NYC72FFA37Accident · No injuriesClosedCessna 172G · New Brennan, NYNTSB FTW72FRD24Accident · No injuriesClosedBoeing A75N1 · Rayne, LANTSB DEN72FQA39Accident · No injuriesClosedPiper PA-24 · Alameda, NMNTSB MKC72FCJ59Accident · No injuriesClosedPiper PA-34 · Omaha, NENTSB NYC72FNC42Accident · Serious injuriesClosedStinson 108-3 · Princeton, MENTSB FTW72DRG79Accident · Serious injuriesClosedPiper PA-18 · Pleasanton, TXNTSB FTW72AF073Accident · 1 fatalClosedAeronca O-58B · Sulphur Sprgs, TXNTSB CHI72FEM61Accident · Serious injuriesClosedBeech C23 · Lansing, MINTSB OAK72FVW29Accident · No injuriesClosedCessna 172G · Las Vegas, NVNTSB OAK72DVG52Accident · No injuriesClosedBeech 35 · Fair Oaks, CANTSB NYC72FNA38Accident · No injuriesClosedPiper PA-28R · Plymouth, MANTSB NYC72FID25Accident · Minor injuriesClosedCessna 150G · Chesterfield, VANTSB DEN72DTE68Accident · Minor injuriesClosedPiper PA-28R · Alamosa, CONTSB FTW72DPJ38Accident · No injuriesClosedPiper PA-25 · Lindsay, OKNTSB CHI72FER03Accident · No injuriesClosedBeech 35-C33 · Star Prairie, WINTSB FTW72FPG70Accident · No injuriesClosedCessna 172 · Ashdown, ARNTSB NYC72FFJ39Accident · Serious injuriesClosedEaa Biplane P · Batavia, NYNTSB LAX72DVA68Accident · Minor injuriesClosedCessna 172 · Olancha, CANTSB NYC72FNE34Accident · Minor injuriesClosedAeronca 11AC · Easton, CTNTSB MIA72AM112Accident · No injuriesClosedDouglas DC-8 · San Juan, PRNTSB LAX72AL053Accident · 2 fatalClosedPiper PA-31P · San Diego, CANTSB SEA72DYA30Accident · Minor injuriesClosedBeech C-18S · Blackfoot, IDNTSB NYC72FKM33Accident · No injuriesClosedPiper PA-22 · Wadesboro, NCNTSB CHI72FET23Accident · No injuriesClosedCessna 120 · Watervliet, MINTSB CHI72FEM65Accident · No injuriesClosedGrumman G-164 · Sparta, MINTSB ANC72AA042Accident · No injuriesClosedPilatus PC-6 · Deadhorse, AKNTSB SEA72FYI19Accident · Serious injuriesClosedErco 415-C · Bonanza, ORNTSB NYC72IN132Incident · No injuriesClosedDouglas DC-8 · Newark, NJNTSB NYC72DIJ40Accident · No injuriesClosedPiper PA-18 · Hamlin, WVNTSB MIA72FKJ50Accident · No injuriesClosedPiper PA-22 · Lacey Springs, ALNTSB FTW72FRJ24Accident · No injuriesClosedAeronca 7AC · Atlanta, TXNTSB LAX72FUQ79Accident · No injuriesClosedPiper PA-28 · Quartz Hill, CANTSB NYC72FID26Accident · Serious injuriesClosedPiper J3C-65 · Bedford, VANTSB MKC72FCJ58Accident · Serious injuriesClosedStinson 108-2 · Seward, NE