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 MIA72FLG14Accident · No injuriesClosedPiper J3C-65 · Clio, SCNTSB DEN72DTC08Accident · No injuriesClosedPiper PA-24 · Gillette, WYNTSB CHI72FLQ15Accident · Serious injuriesClosedCessna A188 · Utica, KYNTSB CHI72DEA14Accident · No injuriesClosedStinson 108-3 · Acton, INNTSB CHI72FEI18Accident · No injuriesClosedCessna 175A · Montville, OHNTSB CHI72FEP16Incident · No injuriesClosedPiper PA-28 · Dodgeville, WINTSB MIA72FKG45Accident · Minor injuriesClosedAero-Comdr S2R · Albany, GANTSB FTW72FRA10Accident · No injuriesClosedCessna 150E · Pearland, TXNTSB DEN72DTE32Accident · No injuriesClosedMooney M20F · Flagler, CONTSB CHI72AC027Accident · 6 fatalClosedBeech 95-B55 · Milwaukee, WINTSB MIA72AM029Accident · 1 fatalClosedPiper PA-28 · Augusta, GANTSB NYC72FHA16Accident · No injuriesClosedSchweizer SGS233 · Erwinna, PANTSB MIA72FLD11Accident · Minor injuriesClosedPiper PA-24 · St.Petersburg, FLNTSB FTW72AF016Accident · 2 fatalClosedPiper J3C-65 · Springdale, ARNTSB CHI72FEM33Accident · Minor injuriesClosedMccullough J-2 · Greenville, MINTSB ANC72DAG47Accident · No injuriesClosedPiper J3C-65 · Anchorage, AKNTSB NYC72FNE12Accident · Minor injuriesClosedBeech A-45 · Monroe, CTNTSB LAX72DUJ44Accident · Minor injuriesClosedBeech B23 · Kearny, AZNTSB CHI72FEG15Accident · Serious injuriesClosedStinson 10A · Phillipsburg, OHNTSB SEA72AS013Accident · Minor injuriesClosedHughes 369HS · Shelton, WANTSB FTW72FRD10Accident · No injuriesClosedBell 47G4A · Morgan City, LANTSB MKC72DER22Accident · Minor injuriesClosedCessna 150J · Bemidji, MNNTSB MIA72FLG11Accident · No injuriesClosedCessna 177 · Spartanburg, SCNTSB CHI72FET08Accident · Minor injuriesClosedLuscombe 8A · Elkhart, INNTSB CHI72FEG16Accident · Minor injuriesClosedPiper PA-22 · Peebles, OHNTSB FTW72DRJ08Accident · No injuriesClosedBell 47G4A · Jonesville, LANTSB MKC72DCJ26Accident · No injuriesClosedPiper PA-24 · Boelus, NENTSB DEN72AD011Accident · No injuriesClosedBell 47G3B1 · Sandy, UTNTSB MKC72FCG16Accident · Serious injuriesClosedGlobe GC-1B · Brunswick, MONTSB MKC72DCG21Accident · Minor injuriesClosedCessna 172 · Topeka, KSNTSB FTW72FPM09Accident · Minor injuriesClosedErco 415-C · Tulsa, OKNTSB NYC72FFA14Accident · No injuriesClosedSmith Mini DSA-1 · Ravena, NYNTSB LAX72FUJ43Accident · No injuriesClosedPiper PA-22 · Casa Grande, AZNTSB DEN72DTM06Accident · No injuriesClosedCessna 177 · Lehi, UTNTSB SEA72DYE13Accident · No injuriesClosedBeech A35 · Prevost, WANTSB SEA72AS010Accident · 4 fatalClosedBellanca 17-30A · Seattle, WANTSB OAK72DVD21Accident · No injuriesClosedPiper PA-24 · Hayward, CANTSB NYC72FKM05Accident · No injuriesClosedMonocoupe 90A · Monroe, NCNTSB MIA72DKJ21Accident · No injuriesClosedPiper PA-25 · Ardmore, ALNTSB NYC72FHD12Accident · Serious injuriesClosedBeech A23 · Atlantic City, NJNTSB FTW72FPD12Accident · No injuriesClosedPop Rivets 4 · Arlington, TXNTSB NYC72DHD16Accident · No injuriesClosedLuscombe 8A · Cross Keys, NJNTSB FTW72FPM08Accident · No injuriesClosedPiper PA-22 · Tulsa, OKNTSB ANC72DAG48Accident · No injuriesClosedCessna 180 · Skwentna, AKNTSB SEA72FYG09Accident · Minor injuriesClosedLuscombe 8A · Newport, WANTSB MKC72FGD29Accident · No injuriesClosedAmer Avco AA-1 · Blakesburg, IANTSB MIA72FLA09Accident · Minor injuriesClosedCessna 177 · Destin, FLNTSB FTW72DPG37Accident · Minor injuriesClosedCessna 150G · Abbott, ARNTSB NYC72FIJ29Accident · Serious injuriesClosedCessna 150 · Abingdon, VANTSB MKC72FCQ24Accident · No injuriesClosedCessna 182J · Arkansas Cty, KS