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 OAK71DVA09Accident · No injuriesClosedCessna 195 · Lone Pine, CANTSB CHI71FDW04Accident · Serious injuriesClosedRepublic RC-3 · Port Huron, MINTSB CHI71FBJ04Accident · No injuriesClosedBell 47GMK3 · Stanton, MINTSB ANC71FAA07Accident · Minor injuriesClosedCessna 120 · Fairbanks, AKNTSB MIA71AM004Accident · 4 fatalClosedBeech 23 · Ocala, FLNTSB MIA71AM003Accident · 1 fatalClosedAeronca 65-TL · Lavonia, GANTSB MIA71DKG07Accident · No injuriesClosedPiper PA-25 · Fort Valley, GANTSB DEN71DXD13Accident · Minor injuriesClosedPiper PA-28 · Littleton, CONTSB MKC71FCD02Accident · No injuriesClosedBeech A35 · Mediapolis, IANTSB LAX71FUQ01Accident · No injuriesClosedHelio Acft H-295 · Tehachapi, CANTSB LAX70FUJ01Accident · No injuriesClosedPiper PA-23 · Mesa, AZNTSB CHI71FBA02Accident · Serious injuriesClosedPiper PA-22 · Crawfordsvlle, INNTSB SEA71FWD02Accident · No injuriesClosedBrantly B-2 · Bellevue, WANTSB NYC71FFD02Accident · Minor injuriesClosedPiper PA-18 · Amesbury, MANTSB MIA71AM001Accident · 1 fatalClosedCessna 120 · Ringgold, GANTSB CHI71DGD01Accident · No injuriesClosedAmer Avco AA-1 · Painesville, OHNTSB SEA71FWD01Accident · No injuriesClosedHughes 269B · Bellingham, WANTSB FTW71FPM01Accident · Minor injuriesClosedPiper PA-22 · Wagoner, OKNTSB ANC71AA001Accident · 5 fatalClosedDouglas DC-3 · Mcgrath, AKNTSB NYC71FGM01Accident · Minor injuriesClosedAeronca 65-TAC · Belle Vernon, PANTSB IAD71FIG02Accident · Serious injuriesClosedPiper PA-28 · Sykesville, MDNTSB FTW71DQD04Accident · No injuriesClosedHelio Acft H-250 · Durango, MexicoNTSB LAX71DUJ10Accident · No injuriesClosedGlobe GC-1B · Showlow, AZNTSB FTW71FRA01Accident · No injuriesClosedGrumman G-164 · Raywood, TXNTSB CHI71FBD01Accident · No injuriesClosedAmer Avco AA1 · Sycamore, ILNTSB IAD71AI001Accident · No injuriesClosedSikorsky S-55B · Davis, WVNTSB IAD70FIJ08Accident · Serious injuriesClosedBeech 65S · Dublin, VANTSB CHI70FDW24Accident · Minor injuriesClosedPiper PA-22 · Richmond, MINTSB CHI70DGD20Accident · No injuriesClosedPiper PA-28 · Milan, OHNTSB CAN71AQ904Accident · Minor injuriesClosedGrumman G-44 · Chibougamau, CanadaNTSB MIA70DLD37Accident · No injuriesClosedAmer Avco AA-1 · Sanford, FLNTSB SEA70DDG13Accident · No injuriesClosedPiper PA-28 · Fairfield, MTNTSB MKC70DCA18Accident · No injuriesClosedCessna 172E · Richwoods, MONTSB SEA70FDA16Accident · Serious injuriesClosedPiper PA-18 · Wolf Point, MTNTSB MKC70FCG35Accident · Serious injuriesClosedPiper J5A · Vinland, KSNTSB FTW71DPG04Accident · No injuriesClosedAero Comdr S-2R · Eudora, ARNTSB IAD70AI065Accident · 3 fatalClosedCessna 310D · West Union, WVNTSB FTW70FRD23Accident · Minor injuriesClosedBell 47J · Cameron, LANTSB FTW70FRA36Accident · Minor injuriesClosedGrumman G-164A · Rosharon, TXNTSB LAX70FUG50Accident · No injuriesClosedBell 47G-3B · Idyllwild, CANTSB ANC70DAG34Accident · Minor injuriesClosedCessna 170B · Kenai, AKNTSB SEA70FDA15Accident · No injuriesClosedCessna 182 · Joliet, MTNTSB FTW70FQG21Accident · Serious injuriesClosedCessna 177 · San Angelo, TXNTSB FTW70AF084Accident · 1 fatalClosedPiper PA-28 · Mooreland, OKNTSB CHI70FBD46Accident · Serious injuriesClosedGlobe GC-1B · Hinsdale, ILNTSB MIA70OKA07Accident · No injuriesClosedCessna 185A · La Romana, DRNTSB FTW70FRA34Accident · No injuriesClosedCessna 150F · Madisonville, TXNTSB CHI70FDM08Accident · Minor injuriesClosedAeronca 11CC · Cumberland, WINTSB CHI70AC093Accident · 2 fatalClosedPiper PA-12 · Somerset, KYNTSB CHI70AC092Accident · 2 fatalClosedN.American AT6-F · Ann Arbor, MI