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 CHI72FLQ28Accident · No injuriesClosedBeech H35 · Louisville, KYNTSB CHI72FEE64Accident · No injuriesClosedCessna 177 · Richton Park, ILNTSB SEA72DYA25Accident · No injuriesClosedBeech G35 · Jordan Vally, ORNTSB BAH72ATC25Accident · 2 fatalClosedCessna 207 · San Salvdor, Burkina FasoNTSB ANC72DAG99Accident · No injuriesClosedBensen B8M · Palmer, AKNTSB NYC72AN106Accident · 1 fatalClosedAero Comdr 500-B · Rochester, NYNTSB NYC72AN104Accident · 2 fatalClosedCessna 421B · East Boston, MANTSB LAX72FUQ64Accident · Minor injuriesClosedAeronca 7AC · Simi, CANTSB CHI72FEA30Accident · No injuriesClosedPiper PA-23 · Kokomo, INNTSB OAK72DVA49Accident · No injuriesClosedBoeing E75 · Tulare, CANTSB MIA72FKG74Accident · No injuriesClosedCessna 172H · Ephesus, GANTSB DEN72AD037Accident · 4 fatalClosedBeech A35 · Parker, AZNTSB OAK72FVD33Accident · Minor injuriesClosedBeech A23 · Concord, CANTSB FTW72FQG72Accident · No injuriesClosedCessna 177 · Tulia, TXNTSB NYC72FNC28Accident · Minor injuriesClosedPiper PA-32 · Nashua, NHNTSB NYC72FKM23Accident · No injuriesClosedCessna 150L · Gastonia, NCNTSB FTW72DRG50Accident · No injuriesClosedBeech E35 · Lereto, MexicoNTSB MKC72AK049Accident · 1 fatalClosedPiper PA-11 · Moberly, MONTSB FTW72DPD29Accident · Minor injuriesClosedCessna 150 · Wingate, TXNTSB MIA72DLG25Accident · Minor injuriesClosedCessna 172B · Greenville, SCNTSB SEA72DYI15Accident · Minor injuriesClosedAeronca L-3 · Grants Pass, ORNTSB OAK72FVM25Accident · No injuriesClosedCessna 182 · San Carlos, CANTSB OAK72DVA52Accident · No injuriesClosedMccullough J-2 · Porterville, CANTSB CHI72AC065Accident · 2 fatalClosedBeech D18S · Jackson, MINTSB MIA72FKQ29Accident · No injuriesClosedCessna 310K · Tupelo, MSNTSB LAX72DVA44Accident · No injuriesClosedBoeing N2S-1 · Bakersfield, CANTSB FTW72FQG30Accident · No injuriesClosedPiper PA-18A · Friona, TXNTSB OAK72DVG37Accident · No injuriesClosedPiper PA-28R · Napa, CANTSB OAK72FXQ21Accident · Serious injuriesClosedPiper PA-28 · Reno, NVNTSB MKC72DCQ41Accident · No injuriesClosedMotor Spatz SF24 · Dodge City, KSNTSB ANC72DAG92Accident · No injuriesClosedPiper PA-18 · Chitina, AKNTSB NYC72FFJ28Accident · No injuriesClosedBeech A23-24 · Penn Yan, NYNTSB MIA72DKW17Accident · Serious injuriesClosedPiper PA-28 · Dunlap, TNNTSB NYC72FHH32Accident · No injuriesClosedTaylorcraft BC12-D · Souderton, PANTSB MIA72FKG72Accident · Minor injuriesClosedStits SA3A · Stockbridge, GANTSB FTW72DPD28Accident · No injuriesClosedCessna 150 · Archer City, TXNTSB MKC72FER38Accident · No injuriesClosedPiper PA-28R · Hopkins, MNNTSB OAK72DVM23Accident · No injuriesClosedCessna 182M · San Jose, CANTSB NYC72FNE23Accident · No injuriesClosedRepublic RC-3 · Danbury, CTNTSB CHI72FEC26Accident · No injuriesClosedCessna 150J · Ann Arbor, MINTSB MKC72AK046Accident · Serious injuriesClosedPiper PA-15 · Great Bend, KSNTSB MIA72FLG24Accident · Minor injuriesClosedPiper PA-28 · Andrews, SCNTSB LAX72FUA33Accident · No injuriesClosedGlobe GC-1B · S.J.Cpstrno, CANTSB DEN72FTE57Accident · No injuriesClosedAero Comdr 560-A · Durango, CONTSB NYC72DFA33Accident · No injuriesClosedLuscombe 8A · Massena, NYNTSB SEA72FVC27Accident · Minor injuriesClosedBeech A23A · Portland, ORNTSB LAX72FUJ69Accident · Minor injuriesClosedCessna 150 · Peoria, AZNTSB NYC72FHM27Accident · Minor injuriesClosedPiper J3F-65 · Wallkill, NYNTSB NYC72FHD27Accident · No injuriesClosedAero Comdr 200-D · Vineland, NJNTSB MKC72FTK21Accident · No injuriesClosedBeech A35 · Hot Springs, SD