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 NYC72FHD10Accident · Minor injuriesClosedPiper PA-28 · Berlin, NJNTSB MIA72FKQ19Accident · Minor injuriesClosedCessna 177 · Vicksburg, MSNTSB LAX72FUG11Accident · Serious injuriesClosedAeronca 7AC · 29 Palms, CANTSB LAX72DUJ35Accident · Minor injuriesClosedStinson 108 · Page, AZNTSB ANC72AA015Accident · No injuriesClosedPiper PA-14 · Homer, AKNTSB LAX72AL018Accident · Minor injuriesClosedHiller Acft UH12L4 · Grnd Canyon, AZNTSB FTW72DPG34Accident · No injuriesClosedPiper PA-25 · Tyronza, ARNTSB CHI72DET07Accident · No injuriesClosedPiper PA-25 · Colon, MINTSB ANC72IA014Incident · No injuriesClosedCessna 185 · Kodiak, AKNTSB NYC72FNE08Accident · Serious injuriesClosedPiper PA-28 · Danbury, CTNTSB NYC72FHA14Accident · No injuriesClosedErco 415-C · Doylestown, PANTSB ANC72DAA08Accident · Minor injuriesClosedPiper PA-12 · Big Delta, AKNTSB MKC72DER19Accident · No injuriesClosedCessna 172I · Crosby, MNNTSB DEN72DTC07Accident · Minor injuriesClosedForney F1 · Hulett, WYNTSB ANC72DAA13Accident · No injuriesClosedPiper PA-18 · Fairbanks, AKNTSB FTW72FRA06Accident · Minor injuriesClosedBeech P35 · Alief, TXNTSB DEN72FQA17Accident · No injuriesClosedCessna 150 · Albuquerque, NMNTSB NYC72FLJ07Accident · Minor injuriesClosedAero Comdr A9 · Rocky Mount, NCNTSB MIA72FKQ11Accident · Minor injuriesClosedAeronca 7FC · Collins, MSNTSB MIA72DKT12Accident · Minor injuriesClosedCessna 172 · Bokeelia, FLNTSB FTW72DQG16Accident · Minor injuriesClosedPiper PA-25 · Seminole, TXNTSB MKC72FTK06Accident · No injuriesClosedAmer Avco AA1-A · Rapid City, SDNTSB DEN72FTM05Accident · Serious injuriesClosedCessna 210-5 · Monticello, UTNTSB MIA72IM026Incident · No injuriesClosedDouglas DC-9 · Bwi, BarbadosNTSB LAX72IL014Incident · No injuriesClosedBoeing 747 · Los Angeles, CANTSB CHI72FEV14Accident · No injuriesClosedPiper PA-25 · Fisher, ILNTSB SEA72DYA12Accident · No injuriesClosedCessna 150 · Rexburg, IDNTSB MIA72FKW04Accident · No injuriesClosedPiper PA-25 · Murfreesboro, TNNTSB MKC72FCA07Accident · No injuriesClosedPiper PA-28 · Augusta, MONTSB CHI72FEP14Accident · Minor injuriesClosedPiper PA-28 · Wauwatosa Br, WINTSB SEA72DYA11Accident · No injuriesClosedPiper PA-18 · Burley, IDNTSB MKC72DCD27Accident · No injuriesClosedCessna 150G · Calamus, IANTSB MIA72IM020Incident · No injuriesClosedCessna 310 · Peachtree City, GANTSB MIA72FLD08Accident · Serious injuriesClosedCessna 180 · Brandon, FLNTSB MIA72FKG26Accident · Serious injuriesClosedPiper PA-28 · Lithonia, GANTSB LAX72FVA12Accident · 1 fatalClosedBoeing A75 · Kettleman, CANTSB ANC72DAG22Accident · No injuriesClosedPiper PA-12 · Cordova, AKNTSB NYC72FFA07Accident · No injuriesClosedPiper PA-22 · Cobleskill, NYNTSB NYC72FHM03Accident · No injuriesClosedBeech 23 · Morganville, NJNTSB MIA72FKJ17Accident · No injuriesClosedPiper PA-28 · Attalla, ALNTSB CHI72DEP12Accident · Minor injuriesClosedCessna 150 · Gays Mills, WINTSB ANC72AK014Accident · 3 fatalClosedFairchild 24W46 · Lino Lakes, MNNTSB FTW72DPG26Accident · No injuriesClosedGrumman G-164 · Truman, ARNTSB NYC72FHA08Accident · No injuriesClosedCessna 172 · Collegeville, PANTSB MKC72DCD22Accident · No injuriesClosedPiper J3C-65 · Bennett, IANTSB MIA72DKG21Accident · No injuriesClosedAero Comdr A-9B · Aragon, GANTSB FTW72FPA04Accident · No injuriesClosedCessna 120 · Waco, TXNTSB MKC72DCD23Accident · No injuriesClosedPiper PA-25 · Manchester, IANTSB MIA72FKQ09Accident · No injuriesClosedGrumman G-164A · Swan Lake, MSNTSB MIA72FKQ05Accident · No injuriesClosedCessna 188 · Shaw, MS