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 LAX72FUG06Accident · Serious injuriesClosedCessna 210A · Claremont, CANTSB OAK72AP002Accident · No injuriesClosedPiper PA-32 · Alturas, CANTSB MKC72DER07Accident · No injuriesClosedCessna 150 · Princeton, MNNTSB FTW72AF007Accident · 2 fatalClosedAerostar 601 · Fort Worth, TXNTSB OAK72DVW04Accident · No injuriesClosedPiper PA-28 · Shoshone, CANTSB LAX72AL007Accident · 2 fatalClosedRyan ST-3KR · Brmuda Duns, CANTSB DEN72DQA07Accident · No injuriesClosedPiper PA-28R · Carrizozo, NMNTSB CHI72FEC06Accident · Serious injuriesClosedPiper J3C-65 · Horton, MINTSB ANC72AA001Accident · 3 fatalClosedBell 206 · Fairbanks, AKNTSB MKC72FCA02Accident · 1 fatalClosedPiper PA-25 · Old Monroe, MONTSB IAD72AI002Accident · 4 fatalClosedPiper PA-30 · Huntington, WVNTSB ANC72DAG11Accident · Serious injuriesClosedHiller 12-E4 · Chitina, AKNTSB ANC72DAG30Accident · No injuriesClosedPiper PA-20 · King Salmon, AKNTSB OAK72FVG08Accident · Serious injuriesClosedPiper PA-28R · Reddin, CANTSB MKC72DCJ07Accident · Serious injuriesClosedCessna 172 · Oneill, NENTSB DEN72FQD03Accident · No injuriesClosedNavion G · Carlsbad, NMNTSB CHI72FEA07Accident · No injuriesClosedPiper PA-25 · Roachdale, INNTSB NYC72AN006Accident · Minor injuriesClosedDouglas DC-3 · Windsor Lks, CTNTSB NYC72FHA04Accident · Serious injuriesClosedPiper PA-23 · Sussex, NJNTSB FTW72AF004Accident · No injuriesClosedBell 47G4A · Morgan City, LANTSB DEN72FQD02Accident · No injuriesClosedBeech 35 · Carlsbad, NMNTSB DEN72FQD01Accident · Minor injuriesClosedPiper PA-28 · Las Cruces, NMNTSB MIA72FLD02Accident · No injuriesClosedCessna 150 · St.Petersburg, FLNTSB MKC72DGD40Accident · No injuriesClosedFournier RF4D · Sibley, IANTSB MKC72FTG04Accident · No injuriesClosedCessna 172 · Des Lacs, NDNTSB FTW72DPG04Accident · Minor injuriesClosedPitts Spcl S1C · West Helena, ARNTSB ANC72DAG20Accident · No injuriesClosedCessna 182 · Naknek, AKNTSB MKC72FCG04Accident · No injuriesClosedCessna 140 · Lee'S Summit, MONTSB MIA72FKJ03Accident · Serious injuriesClosedPiper J3L-65 · Dothan, ALNTSB FTW72FPG06Accident · No injuriesClosedSnow 600S2C · Waldenburg, ARNTSB OAK72DVG04Accident · No injuriesClosedCessna 150 · Weaverville, CANTSB CHI72FEG01Accident · Serious injuriesClosedCessna 150J · Hillsboro, OHNTSB CHI72FEA03Accident · Minor injuriesClosedPiper J4A · Cloverdale, INNTSB CHI72FEA01Accident · No injuriesClosedBoeing A75N1 · Greensburg, INNTSB ANC72DAG08Accident · No injuriesClosedChampion 7GCBC · Anchorage, AKNTSB LAX72AL003Accident · 2 fatalClosedCessna 310D · Apple Vally, CANTSB MKC72FTG03Accident · 1 fatalClosedN.American AT-6G · Stanley, NDNTSB FTW72AF001Accident · 1 fatalClosedCessna 177 · El Dorado, ARNTSB CHI72FEV02Accident · No injuriesClosedGrumman G-164A · Arthur, ILNTSB LAX72FUG04Accident · Serious injuriesClosedCessna 140 · Parker Dam, CANTSB IAD72DIJ25Accident · No injuriesClosedBensen B8M-AX · Hamlin, WVNTSB CHI72FEM02Accident · Serious injuriesClosedCessna 150F · Hessel, MINTSB ANC72DAG23Accident · No injuriesClosedPiper PA-18 · Willow, AKNTSB SEA72FYE02Accident · Minor injuriesClosedCessna 150 · Bellevue, WANTSB SEA72FYC02Accident · Serious injuriesClosedBeech 23 · Seaside, ORNTSB NYC72FHA03Accident · No injuriesClosedBell 47G · Long Pond, PANTSB LAX72FUG01Accident · No injuriesClosedPiper PA-24 · Laverne, CANTSB LAX72DUG03Accident · No injuriesClosedChampion 7ECA · Banning, CANTSB FTW72FPM01Accident · Serious injuriesClosedCessna 150 · Wagoner, OKNTSB DEN72FTE01Accident · Serious injuriesClosedCessna 150 · Aurora, CO