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 CAN71AP903Accident · Serious injuriesClosedTaylorcraft L2 · Williams Lake, CanadaNTSB SEA71FWA13Accident · Minor injuriesClosedPiper PA-28 · Glenwood, WANTSB MKC71FCQ15Accident · No injuriesClosedCessna 401 · Baxter Sprgs, KSNTSB NYC71FFA09Accident · Serious injuriesClosedBeech A23 · Johnstown, NYNTSB CHI71FDW11Accident · Minor injuriesClosedPiper PA-22 · Deckerville, MINTSB FTW71FRA14Accident · No injuriesClosedCessna 172 · Alvin, TXNTSB MKC71FDD10Accident · Serious injuriesClosedAero Comdr 100 · Glenburn, NDNTSB CHI71DGD05Accident · No injuriesClosedN.American Navion · Cleveland, OHNTSB SEA71FDA05Accident · Serious injuriesClosedStinson 108-2 · Red Lodge, MTNTSB NYC71FGM09Accident · No injuriesClosedTaylor Titch · Finleyville, PANTSB CHI71FBJ19Accident · Minor injuriesClosedPiper PA-22 · Scotts, MINTSB CHI71DBA10Accident · No injuriesClosedAeronca 7EC · Odon, INNTSB MKC71FCA11Accident · Serious injuriesClosedCessna 150H · Van Buren, MONTSB CHI71FBM03Accident · Serious injuriesClosedCessna T210 · Watervliet, MINTSB CHI71FBD14Accident · Minor injuriesClosedPiper J5 · Streator, ILNTSB MKC71DDQ06Accident · No injuriesClosedCessna 170B · Wilmot, SDNTSB MIA71DKD05Accident · No injuriesClosedAeronca 11AC · Arlington, TNNTSB FTW71DPJ12Accident · No injuriesClosedGrumman G-164 · Boise City, OKNTSB DEN71DXD20Accident · No injuriesClosedPiper PA-25 · Walsh, CONTSB CHI71FBM05Accident · Serious injuriesClosedTaylorcraft 19 · Dowagiac, MINTSB ANC71DAG15Accident · No injuriesClosedCessna 150K · Anchorage, AKNTSB MIA71FLA09Accident · No injuriesClosedBell 47G-2 · Panama City, FLNTSB IAD71DLJ09Accident · No injuriesClosedBell 47G-2 · Pactolus, NCNTSB CHI71FBQ10Accident · Minor injuriesClosedBeech B23 · Macomb, ILNTSB NYC71FFD08Accident · No injuriesClosedAlon A2-A · Pepperell, MANTSB MIA71FLG04Accident · No injuriesClosedBeech 35 · Jackson, SCNTSB MIA71FKQ07Accident · Minor injuriesClosedPiper PA-25 · Bolton, MSNTSB CHI71DBQ07Accident · No injuriesClosedAero Comdr A-9 · Harrisburg, ILNTSB OAK71AP008Accident · 2 fatalClosedChampion 7EC · Vacaville, CANTSB FTW71DPD13Accident · No injuriesClosedCessna 177 · Iowa Park, TXNTSB CHI71FBQ11Accident · Minor injuriesClosedPiper PA-18A · Gibson City, ILNTSB ANC71AA012Accident · 1 fatalClosedPiper PA-18 · Seward, AKNTSB OAK71FVG07Accident · Minor injuriesClosedPiper PA-24 · Redding, CANTSB MIA71FKG20Accident · Serious injuriesClosedWittman W-8 · La Grange, GANTSB OAK71FVD04Accident · Serious injuriesClosedGlobe GC-1B · San Lorenzo, CANTSB ANC71DAG14Accident · Minor injuriesClosedPiper PA-12 · Chistochina, AKNTSB SEA71FVQ05Accident · No injuriesClosedPiper PA-22 · Junction Cty, ORNTSB OAK71DVD03Accident · No injuriesClosedInterstate S-1B1 · Tracy, CANTSB MIA71DLD08Accident · No injuriesClosedCessna 172D · Bradenton, FLNTSB FTW71DQG26Accident · No injuriesClosedGlobe GC-1B · Big Spring, TXNTSB DEN71DXD19Accident · Minor injuriesClosedVolksplane WE-1 · Aurora, CONTSB MIA71FKQ05Accident · No injuriesClosedPiper PA-25 · Quitman, MSNTSB NYC71DFD05Accident · Minor injuriesClosedPiper J-3 · Eastham, MANTSB MKC71FCQ10Accident · Serious injuriesClosedSchultz BB · Harper, KSNTSB MKC71DCG14Accident · No injuriesClosedCessna 210 · Protem, MONTSB CHI71FGA05Accident · No injuriesClosedCessna 150 · Oxford, OHNTSB IAD71FIA02Accident · No injuriesClosedCessna 177 · Clinton, MDNTSB FTW71FPJ09Accident · No injuriesClosedPiper PA-24 · Oklahoma Cty, OKNTSB FTW71FPD09Accident · No injuriesClosedPop Rivet 1 · Newark, TXNTSB CHI71FBA03Accident · No injuriesClosedBeech 35 · Marion, IN