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 MKC71FDD12Accident · No injuriesClosedAlon A2-A · Napoleon, NDNTSB FTW71FRJ14Accident · No injuriesClosedPiper PA-25 · Wisner, LANTSB DCA71AZ002Accident · 11 fatalClosedDouglas DC-8F · Jamaica, NYNTSB NYC71FFJ13Accident · Serious injuriesClosedVolmer VJ-22 · Fulton, NYNTSB MIA71FKW09Accident · No injuriesClosedCessna 177 · Portland, TNNTSB FTW71DQG33Accident · No injuriesClosedCessna 188 · Sunray, TXNTSB CHI71FBQ14Accident · No injuriesClosedPiper J-3 · Havana, ILNTSB CHI71DDJ12Accident · Serious injuriesClosedBeech C-45H · Lakewood, WINTSB MIA71FKG38Accident · 1 fatalClosedWolf-Hirth LO-150 · Sylvania, GANTSB DEN71DXT08Accident · No injuriesClosedPiper PA-28 · Mexican Hat, UTNTSB MKC71DDD11Accident · No injuriesClosedErco 415-C · Verona, NDNTSB CHI71FGG10Accident · No injuriesClosedErco 415-C · Findlay, OHNTSB CHI71FBJ28Accident · Serious injuriesClosedCessna 310H · Houghton Lk, MINTSB NYC71AN032Accident · 4 fatalClosedPiper PA-28 · Minersville, PANTSB LAX71DUJ20Accident · No injuriesClosedPiper PA-28 · Mayer, AZNTSB CHI71FBD20Accident · No injuriesClosedMaule M-4 · Shannon, ILNTSB CHI71DDW14Accident · No injuriesClosedMooney M20C · Port Huron, MINTSB CAN72AP905Accident · 2 fatalClosedPiper PA-30 · Prince George, CanadaNTSB FTW71FQD08Accident · No injuriesClosedCessna 140 · El Paso, TXNTSB MIA71KKQ10Accident · 1 fatalClosedPiper PA-18A · Lucedale, MSNTSB LAX71FUA15Accident · No injuriesClosedCessna 150 · Torrance, CANTSB OAK71AP013Accident · 4 fatalClosedBeech A35 · Georgetown, CANTSB MIA71FKG32Accident · Serious injuriesClosedPiper PA-18 · Girard, GANTSB CAN71AP904Accident · Minor injuriesClosedCessna 175 · Mons Lake, CanadaNTSB OAK71FVA24Accident · 1 fatalClosedGrumman G-164A · Kerman, CANTSB IAD71DIG11Accident · No injuriesClosedPiper PA-28 · Fallston, MDNTSB DEN71FQD06Accident · Minor injuriesClosedCessna 172 · Sunspot, NMNTSB CHI71FGD14Accident · Minor injuriesClosedCessna 177 · Northfield, OHNTSB IAD71FLJ11Accident · No injuriesClosedCessna 177 · Raleigh, NCNTSB CHI71FBA11Accident · No injuriesClosedPiper PA-25 · Laurel, INNTSB OAK71FVM11Accident · No injuriesClosedZlin 526F · Belmont, CANTSB OAK71DVD09Accident · Minor injuriesClosedCessna 172 · Concord, CANTSB FTW71DPD14Accident · No injuriesClosedPiper PA-28 · Hico, TXNTSB ANC71DAD03Accident · No injuriesClosedCessna 170 · Haines, AKNTSB SEA71AS011Accident · 1 fatalClosedCessna 172 · Agness, ORNTSB MKC71FCD21Accident · No injuriesClosedCessna 150K · Leighton, IANTSB MIA71FLD12Accident · No injuriesClosedPiper PA-24 · St Petersburg, FLNTSB MIA71DKG28Accident · No injuriesClosedPiper PA-25 · Pinehurst, GANTSB FTW71DRG21Accident · Minor injuriesClosedPiper PA-25 · Mason, TXNTSB FTW71DRD09Accident · Minor injuriesClosedAero Cmdr 600S2D · Napoleonville, LANTSB MKC71DDM21Accident · No injuriesClosedPiper J3C-65 · Duluth, MNNTSB DEN71AD007Accident · Minor injuriesClosedLockheed 188 · Hill Af Base, UTNTSB OAK71DVA21Accident · No injuriesClosedCallair A-9 · Kettleman Cty, CANTSB MKC71FCQ14Accident · No injuriesClosedPiper PA-22 · Dighton, KSNTSB MIA71FLG08Accident · No injuriesClosedPiper J3C-65 · Hartsville, SCNTSB DEN71FQA07Accident · Minor injuriesClosedAero Comdr 680V · Taos, NMNTSB DEN71AD006Accident · No injuriesClosedCessna Wren · Fort Washakie, WYNTSB CAN71AQ905Accident · Minor injuriesClosedPiper PA-22 · Furnace Falls, CanadaNTSB MIA71FLD11Accident · Minor injuriesClosedCessna 177 · Holmes Beach, FLNTSB IAD71DKM10Accident · No injuriesClosedMonocoupe 90A · Monroe, NC