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 NYC66F0330Accident · Minor injuriesClosedLuscombe 8A · Brewer, MENTSB MKC66F0283Accident · Minor injuriesClosedBeechcraft 35-33 · Marshall, MONTSB LAX66D0173Accident · No injuriesClosedPiper PA-22 · Fullerton, CANTSB FTW66A0038Accident · 1 fatalClosedLuscombe 8A · Guthrie, OKNTSB MIA66F0314Accident · No injuriesClosedCessna 172 · Ludowici, GANTSB OAK66F0132Accident · Serious injuriesClosedBell 47J2A · Lihue, HINTSB DEN66D0161Accident · No injuriesClosedMooney M20C · Mormon Lake, AZNTSB FTW66F0373Accident · No injuriesClosedCallair A-9 · Goodlet, TXNTSB UNK66X0105Accident · 1 fatalClosedBeechcraft 35 · La Pesca, MexicoNTSB SEA66F0156Accident · Minor injuriesClosedBriegleb BG-7 · Rexburg, IDNTSB MKC66F0275Accident · No injuriesClosedMooney M20C · Carbondale, KSNTSB MIA66S0002Accident · Minor injuriesClosedPiper PA-23 · Punta Caucedo, DRNTSB CHI66A0037Accident · 2 fatalClosedNavion A · Spencer, INNTSB ANC66D0100Accident · No injuriesClosedTaylorcraft BC12-D · Mekoryuk, AKNTSB CHI66A0039Accident · No injuriesClosedBeechcraft SNB-5 · Bainbridge, OHNTSB OAK66D0144Accident · No injuriesClosedBeech G35 · Santa Rosa, CANTSB MKC66F0297Accident · No injuriesClosedMooney M20B · Mission, SDNTSB FTW66F0343Accident · No injuriesClosedBoeing B-75N1 · Palacios, TXNTSB FTW66F0341Accident · No injuriesClosedPiper PA-24 · El Pas, TXNTSB MKC66A0026Accident · 3 fatalClosedCessna 172E · Kansas City, MONTSB FTW66F0340Accident · Minor injuriesClosedBoeing E-75 · Hereford, TXNTSB SEA66F0152Accident · No injuriesClosedCessna 170B · Silverdale, WANTSB MKC66A0025Accident · 1 fatalClosedCessna 180 · Bemidji, MNNTSB SEA66F0173Accident · Minor injuriesClosedBeechcraft M23 · Parkrose, ORNTSB MIA66F0251Accident · Minor injuriesClosedCessna 172E · Cabo Rojo, PRNTSB CHI66F0333Accident · Minor injuriesClosedPiper PA-22 · Wixom, MINTSB MKC66F0248Accident · Minor injuriesClosedPiper PA-12 · Lemars, IANTSB FTW66D0366Accident · No injuriesClosedPiper J3C · Comfort, TXNTSB DEN66D0122Accident · No injuriesClosedPiper J-3 · Woodruff, UTNTSB CHI66A0034Accident · 2 fatalClosedNavion A · Beaver Isle, MINTSB NYC66F0305Accident · Minor injuriesClosedRyan ST-3KR · Laurel, MDNTSB UNK66X0101Accident · Minor injuriesClosedPiper PA-18 · Alexis Crk, CanadaNTSB MKC66F0264Accident · No injuriesClosedCessna 310I · Wichita, KSNTSB CAN65AF386Accident · Serious injuriesClosedPiper PA-18 · Atlin Lake, CanadaNTSB ANC66D0071Accident · No injuriesClosedChampion 7GCB · Iliama, AKNTSB DEN66D0137Accident · Minor injuriesClosedBellanca 14-19 · Payson, AZNTSB CHI66F0345Accident · No injuriesClosedErcoupe 415C · Wooster, OHNTSB CHI66D0365Accident · Minor injuriesClosedCessna 195 · Fraser, MINTSB NYC66I0042Incident · No injuriesClosedBoeing B-727 · Newark, NJNTSB NYC66D0246Accident · Minor injuriesClosedTaylorcraft BC-65 · Jamaica, VTNTSB FTW66F0313Accident · No injuriesClosedAero Cmdr 500B · Addison, TXNTSB NYC66A0061Accident · 1 fatalClosedBeechcraft G-35 · Binghamton, NYNTSB NYC66A0010Accident · 3 fatalClosedPiper PA-22 · E.Hartford, CTNTSB ANC66D0069Accident · No injuriesClosedCessna 180 · Talkeetna, AKNTSB MKC66F0279Accident · Minor injuriesClosedCessna 150 · Bloomsdale, MONTSB DEN66A0022Accident · No injuriesClosedBrantly B2B · Denver, CONTSB MIA66F0306Accident · Minor injuriesClosedChampion 7AC · Tallahassee, FLNTSB NYC66F0319Accident · No injuriesClosedHelio MK2 · Shippenville, PANTSB FTW66F0312Accident · Serious injuriesClosedCessna 150 · Westwego, LANTSB CHI66D0438Accident · Minor injuriesClosedTaylorcraft BC12-D · Cedar Grove, IN