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 MIA71FKQ27Accident · Serious injuriesClosedCessna A188A · Carrollton, MSNTSB MKC71DCJ54Accident · No injuriesClosedPiper PA-24 · Rushville, NENTSB LAX71DVA62Accident · No injuriesClosedPiper J3C · Turlock, CANTSB LAX71FUA50Accident · Serious injuriesClosedHughes 269B · Long Beach, CANTSB DEN71DXD54Accident · No injuriesClosedCessna 182 · Aspen, CONTSB FTW71AF056Accident · 1 fatalClosedPiper J-3 · Rio Grand Cty, TXNTSB ANC71DAG70Accident · No injuriesClosedPiper PA-18 · Skwentna, AKNTSB NYC71FFD39Accident · Minor injuriesClosedCessna 140 · Mrstns Mills, MANTSB FTW71DRG89Accident · 1 fatalClosedCessna 206 · Lagunitas, MexicoNTSB MIA71FKT43Accident · No injuriesClosedChampion 7ECA · Punta Gorda, FLNTSB NYC71FHM20Accident · No injuriesClosedPiper PA-12 · Monticello, NYNTSB MKC71DCQ30Accident · Minor injuriesClosedPiper J-3C · Russell, KSNTSB LAX71FUG44Accident · Serious injuriesClosedCessna 150 · Murrieta, CANTSB DEN71FXD51Accident · No injuriesClosedCessna 150F · Aurora, CONTSB CHI71FBJ57Accident · No injuriesClosedCessna 172K · Montague, MINTSB MIA71FKD12Accident · Minor injuriesClosedPiper PA-22 · Union City, TNNTSB MKC71DDD19Accident · No injuriesClosedPiper J3C-65 · Crosby, NDNTSB OAK71DVG55Accident · No injuriesClosedPiper PA-24 · Red Bluff, CANTSB OAK71AP033Accident · 1 fatalClosedCulver LCA · Dos Palos, CANTSB OAK72DJY04Accident · 2 fatalClosedDouglas DC-3 · Phnom Penh, CBNTSB NYC71FHJ21Accident · No injuriesClosedPiper PA-28 · White Plains, NYNTSB NYC71FFA49Accident · No injuriesClosedPiper PA-28 · West Dover, VTNTSB LAX71FUM29Accident · Serious injuriesClosedBeech C-45H · Lakeside, CANTSB CHI71FLQ17Accident · Minor injuriesClosedAeronca 7AC · Stanton, KYNTSB FTW71FQG55Accident · No injuriesClosedPiper PA-30 · Big Spring, TXNTSB SEA71FYM24Accident · Serious injuriesClosedCessna 421B · Gannett, IDNTSB MKC71FCG43Accident · Minor injuriesClosedAlon A-2 · Topeka, KSNTSB DEN71FQA46Accident · Serious injuriesClosedCessna 150F · Albuquerque, NMNTSB FTW71FRG74Accident · No injuriesClosedPiper PA-24 · San Antonio, TXNTSB MIA71AM064Accident · 2 fatalClosedPiper PA-23 · Daytona Beach, FLNTSB FTW71FPG35Accident · No injuriesClosedMooney M20E · Little Rock, ARNTSB FTW71DPJ27Accident · Minor injuriesClosedCessna 172 · Taloga, OKNTSB MKC71FCJ43Accident · No injuriesClosedPiper PA-18 · Hyannis, NENTSB NYC71FGM27Accident · No injuriesClosedBeech A23 · Belle Vernon, PANTSB OAK71FJA09Accident · Minor injuriesClosedCessna 150 · Honolulu, HINTSB MIA71FKQ24Accident · Minor injuriesClosedPiper PA-22 · Raymond, MSNTSB NYC71FHM15Accident · Serious injuriesClosedMooney M20F · Morristown, NJNTSB FTW71AF041Accident · 2 fatalClosedPiper PA-30 · New Orleans, LANTSB NYC71FFG15Accident · Minor injuriesClosedCessna 310N · Auburn, MENTSB NYC71AN082Accident · 1 fatalClosedCessna 411 · Little Ferry, NJNTSB LAX71FUJ58Accident · Minor injuriesClosedAmer Avco AA1 · Flagstaff, AZNTSB CHI71AC054Accident · 1 fatalClosedBeech M-35 · Boyne, MINTSB DEN71DXD46Accident · No injuriesClosedCessna 170B · Aspen, CONTSB ANC71AA037Accident · No injuriesClosedCessna 180 · Fairbanks, AKNTSB NYC71FFJ23Accident · No injuriesClosedBeech V35A · Endicott, NYNTSB DEN71FQA37Accident · No injuriesClosedCessna 310I · Albuquerque, NMNTSB FTW71FRA36Accident · Minor injuriesClosedCessna 172F · Bryan, TXNTSB FTW71FRA33Accident · No injuriesClosedPiper PA-12 · Pearland, TXNTSB FTW71FRA34Accident · No injuriesClosedCessna 172K · Katy, TXNTSB DEN71FXD45Accident · No injuriesClosedCessna 150 · Aurora, CO