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 ANC68F0088Accident · No injuriesClosedPiper PA-18 · Gulkana, AKNTSB SEA68D0144Accident · Minor injuriesClosedTaylorcraft BC12-D · Harrison, MTNTSB CHI68F0278Accident · No injuriesClosedBeech 35-33 · Grove City, OHNTSB LAX68D0147Accident · No injuriesClosedPiper PA-15 · Agua Dulce, CANTSB SEA68F0173Incident · Minor injuriesClosedBeech A23 · Woodburn, ORNTSB NYC68A0047Accident · Serious injuriesClosedMooney M20C · Jamaica, NYNTSB DEN68F0098Accident · No injuriesClosedCessna 150 · Lamar, CONTSB OAK68A0028Accident · Minor injuriesClosedBell 47G3B1 · S.Lak Tahoe, CANTSB MKC68D0297Accident · No injuriesClosedGlobe GC-1B · Harrisonville, MONTSB NYC68F0190Accident · No injuriesClosedHelio Acft H-295 · Topsfield, MANTSB MIA68D0194Accident · No injuriesClosedBeech 35 · Kissimee, FLNTSB MIA68A0029Accident · No injuriesClosedBell 47H-1 · Miami, FLNTSB ANC68A0018Accident · No injuriesClosedHiller UH-12E · Chicken, AKNTSB DEN68D0115Accident · No injuriesClosedPiper PA-24 · Grady, NMNTSB OAK68A0027Accident · Serious injuriesClosedGrumman TBM-3E · Hollister, CANTSB MIA68O0003Accident · No injuriesClosedCurtiss-Wrt C-46F · Kingston, JamaicaNTSB OAK68D0139Accident · No injuriesClosedTaylorcraft L-2 · Castroville, CANTSB CHI68F0336Accident · Serious injuriesClosedPiper PA-22 · Greenfield, INNTSB FTW68F0221Accident · No injuriesClosedLuscombe 8F · Lafayette, LANTSB FTW68D0220Accident · No injuriesClosedPiper PA-22 · Marshall, OKNTSB DEN68F0084Accident · Minor injuriesClosedGlobe GC-1B · Littleton, CONTSB DEN68A0018Accident · 3 fatalClosedBeech C-45H · Farmington, NMNTSB OAK68A0026Accident · 2 fatalClosedPiper PA-22 · Crescent City, CANTSB DIA68F0077Accident · Serious injuriesClosedPiper PA-28 · Federalsburg, MDNTSB CHI68F0264Accident · Serious injuriesClosedStinson 108-3 · Lore City, OHNTSB CHI68A0025Accident · 2 fatalClosedErco 415C · Hillsdale, ILNTSB SEA68F0131Accident · Serious injuriesClosedChampion 7KCAB · North Bend, ORNTSB NYC68F0132Accident · No injuriesClosedBeech 95-C55 · Lehighton, PANTSB SEA68D0153Accident · Minor injuriesClosedStinson 108-2 · Bellingham, WANTSB DEN68D0093Accident · Serious injuriesClosedStinson 108-3 · Gallup, NMNTSB OAK68F0206Accident · No injuriesClosedBoeing A75N1 · Sacramento, CANTSB OAK68A0024Accident · 2 fatalClosedCessna 120 · Tracy, CANTSB SEA68A0022Accident · Serious injuriesClosedEnstrom F-28 · Santiam Jct, ORNTSB NYC68A0040Accident · 2 fatalClosedLuscombe 8A · Morgan, PANTSB MKC68F0207Accident · No injuriesClosedTaylorcraft BC12-D · Crane Lake, MNNTSB MIA68D0240Accident · No injuriesClosedCessna 172 · Pachuca, MexicoNTSB CHI68A0023Accident · 2 fatalClosedCessna 170B · Ludington, MINTSB ANC68F0097Accident · Serious injuriesClosedPiper PA-18 · Chitina, AKNTSB NYC68F0124Accident · No injuriesClosedPiper PA-20S · Bucksport, MENTSB FTW68F0257Accident · No injuriesClosedGrumman G-164 · Reserve, LANTSB DIA68F0079Accident · No injuriesClosedStinson HW-75 · Clinton, NCNTSB SEA68A0020Accident · Serious injuriesClosedBoeing B-17 · Kalispell, MTNTSB NYC68A0034Accident · No injuriesClosedSikorsky S-51 · Wndsor Locks, CTNTSB ANC68D0049Accident · Minor injuriesClosedPiper PA-12S · Ketchikan, AKNTSB DIA68F0060Accident · Minor injuriesClosedPiper J-3 · Greensboro, NCNTSB OAK68F0126Accident · Serious injuriesClosedBoeing A75 · Selma, CANTSB MIA68F0163Accident · No injuriesClosedPiper J3 · Bushnell, FLNTSB FTW68F0213Accident · No injuriesClosedPiper PA-22 · Quinton, OKNTSB FTW68D0192Accident · Minor injuriesClosedPiper PA-22 · Amarillo, TXNTSB DIA68A0009Accident · 1 fatalClosedAeronca 11CC · Enoch, WV