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 LAX72DUJ70Accident · No injuriesClosedCessna 150 · Tuba City, AZNTSB CHI72FEE56Accident · Minor injuriesClosedCessna P206 · Chicago, ILNTSB MIA72FLA29Accident · Serious injuriesClosedBeech D18S · Bunnell, FLNTSB CHI72FEP30Accident · 1 fatalClosedCessna 337 · Woodruff, WINTSB MKC72FTG16Accident · No injuriesClosedPiper J-3 · Crosby, NDNTSB DEN72DTC15Accident · No injuriesClosedPiper PA-18 · Dayton, WYNTSB NYC72AN089Accident · 1 fatalClosedAmer Avco AA1 · Plattsburgh, NYNTSB MKC72DTK20Accident · No injuriesClosedPiper J-3 · Watertown, SDNTSB LAX72FUQ55Accident · Serious injuriesClosedBeech F35 · Simi, CANTSB NYC72FLJ20Accident · Minor injuriesClosedEnstrom F-28A · Hillsborough, NCNTSB MIA72FKG65Accident · Serious injuriesClosedCessna 150 · Fayetteville, GANTSB MIA72AM066Accident · 2 fatalClosedPiper PA-23 · Ocoee, FLNTSB LAX72AL044Accident · 1 fatalClosedSchweizer SGU222 · Elsinore, CANTSB CHI72DEE52Accident · No injuriesClosedCessna 172L · Lockport, ILNTSB MKC72DTK18Accident · No injuriesClosedPiper J-3 · Arlington, SDNTSB MIA72FKQ28Accident · Serious injuriesClosedCessna 188B · Columbus, MSNTSB NYC72FGT07Accident · No injuriesClosedCessna 150F · Elysburg, PANTSB NYC72FFJ24Accident · No injuriesClosedCessna 150J · Buffalo, NYNTSB LAX72DVM19Accident · Minor injuriesClosedSnow 600S2R · San Miguel, CANTSB CHI72FEC23Accident · No injuriesClosedCessna 172E · Lake Orion, MINTSB NYC72FNA24Accident · Serious injuriesClosedAmer Avco AA5 · Tewksbury, MANTSB MIA72FKW12Accident · Serious injuriesClosedCessna 140 · Knoxville, TNNTSB ANC72AA033Accident · Minor injuriesClosedHelio H-250 · Lttl Diomede, AKNTSB FTW72FRA27Accident · No injuriesClosedCessna 310 · Tomball, TXNTSB FTW72DPG58Accident · No injuriesClosedCessna 150 · Heth, ARNTSB MIA72FLA23Accident · Minor injuriesClosedCessna 177 · Carrabelle, FLNTSB MIA72FKT36Accident · No injuriesClosedCallair A-9B · W Palm Beach, FLNTSB MIA72FKQ27Accident · No injuriesClosedHiller Acft FH1100 · Hattiesburg, MSNTSB FTW72AF039Accident · 1 fatalClosedCessna 172 · Seguin, TXNTSB OAK72FVA38Accident · No injuriesClosedCessna 150 · Selma, CANTSB ANC72FAG82Accident · No injuriesClosedCessna 180 · Northway, AKNTSB CHI72AC052Accident · 1 fatalClosedPiper PA-22 · Evansville, INNTSB SEA72DYG21Accident · Minor injuriesClosedBeech 23 · Pullman, WANTSB NYC72FGM24Accident · No injuriesClosedCessna 182K · Sharon, PANTSB FTW72AF033Accident · 7 fatalClosedBeech 65-80 · Okla.City, OKNTSB NYC72FNC16Accident · No injuriesClosedPiper J3C-65 · East Dixfield, MENTSB UNK72X0101Accident · Minor injuriesClosedLake LA-4 · Seoul, KSNTSB FTW72FPM16Accident · No injuriesClosedLuscombe 8A · Cleveland, OKNTSB MIA72FKQ26Accident · No injuriesClosedCessna 175 · Duck Hill, MSNTSB ANC72DAG77Accident · No injuriesClosedPiper J5A · Anchorage, AKNTSB CHI72FEG24Accident · No injuriesClosedPiper PA-23 · Lebanon, OHNTSB NYC72FHD22Accident · Minor injuriesClosedMooney M20F · Vineland, NJNTSB CHI72FEI23Accident · No injuriesClosedHelio Acft H-295 · Medina, OHNTSB ANC72AA023Accident · No injuriesClosedPiper PA-28 · Iliamna, AKNTSB NYC72FHD21Accident · No injuriesClosedCessna 182J · Williamstown, NJNTSB MKC72FER30Accident · Serious injuriesClosedPiper PA-18 · Gilman, MNNTSB ANC72AA022Accident · 4 fatalClosedCessna 170B · Kotzebue, AKNTSB OAK72DVA47Accident · No injuriesClosedErco 415-C · Merced, CANTSB LAX72DUJ58Accident · Minor injuriesClosedPiper J3C-65 · Pearce, AZNTSB DEN72DTE44Accident · Minor injuriesClosedCessna 172 · Fountain, CO