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 SEA71FWD22Accident · No injuriesClosedPiper PA-18 · Sekiu, WANTSB NYC71FHA22Accident · No injuriesClosedBeech A23 · Basking Ridge, NJNTSB FTW71DRA30Accident · Serious injuriesClosedAeronca 15AC · Clute, TXNTSB DEN71AD024Accident · 2 fatalClosedPiper PA-18 · Grand Junct, CONTSB OAK71DVG39Accident · No injuriesClosedBoeing A75 · Woodland, CANTSB NYC71FHJ16Accident · Minor injuriesClosedAmer Avco AA-1 · Bohemia, NYNTSB FTW71AF023Accident · 5 fatalClosedCessna 182M · Vidalia, LANTSB ANC71DAG48Accident · No injuriesClosedCessna 180 · Eureka, AKNTSB NYC71AN061Accident · Serious injuriesClosedCessna 310D · Warwick, RINTSB MKC71FCG35Accident · No injuriesClosedCessna 182G · Eldon, MONTSB MKC71FCG33Accident · No injuriesClosedErco 415-E · Gardner, KSNTSB LAX71FUQ36Accident · Serious injuriesClosedPiper PA-28R · Mojave, CANTSB NYC71FHM10Accident · No injuriesClosedCessna 172 · Spring Valley, NYNTSB LAX71AL028Accident · 2 fatalClosedNavion H · El Monte, CANTSB CHI71FBJ39Accident · No injuriesClosedMaule M-4 · Ludington, MINTSB DEN71FXD34Accident · Minor injuriesClosedAmer Avco AA-1 · Aurora, CONTSB MIA71FKA08Accident · No injuriesClosedCessna 150K · St.Croix, VINTSB CHI71DDJ27Accident · No injuriesClosedBensen B8M · Waterford, WINTSB CHI71DDJ21Accident · Minor injuriesClosedCessna 180A · Tony, WINTSB MKC71FDM36Accident · No injuriesClosedBensen B-8M · Lake Elmo, MNNTSB CAN71AP909Accident · No injuriesClosedBeech B35 · Tsoniah Lake, CanadaNTSB MKC71DCG29Accident · No injuriesClosedCessna 140 · Moberly, MONTSB CHI71FDM04Accident · No injuriesClosedPiper PA-28 · Chetek, WINTSB IAD71DIG20Accident · No injuriesClosedBeech 58 · Capon Sprngs, WVNTSB FTW71DRG47Accident · No injuriesClosedPiper PA-28 · Rancho S.J., MexicoNTSB FTW71AF020Accident · 1 fatalClosedPiper PA-18 · Breaux Bridge, LANTSB SEA71DWG30Accident · No injuriesClosedPiper PA-28 · Royal City, WANTSB MIA71DKT14Accident · No injuriesClosedBeech 95-B55 · Opa Locka, FLNTSB FTW71FPD24Accident · No injuriesClosedCessna 177 · Abilene, TXNTSB FTW71DRD24Accident · No injuriesClosedGrumman G-164 · Welsh, LANTSB LAX71FUM18Accident · Serious injuriesClosedBeech C35 · San Diego, CANTSB CHI71AC029Accident · Serious injuriesClosedBeech D18S · Plymouth, MINTSB NYC71DFD19Accident · No injuriesClosedAeronca 7AC · S.Middleboro, MANTSB FTW71FRA24Accident · No injuriesClosedPiper PA-18 · Conroe, TXNTSB LAX71FUQ28Accident · Serious injuriesClosedPiper PA-28 · Van Nuys, CANTSB FTW71FRG40Accident · No injuriesClosedAero Comdr A-9B · Tivoli, TXNTSB MIA71AM035Accident · 2 fatalClosedCessna 120 · Marietta, GANTSB LAX71DUJ38Accident · No injuriesClosedCessna 150H · Chandler, AZNTSB CHI71FGG17Accident · No injuriesClosedNavion NAV4 · Findlay, OHNTSB CHI71FGG16Accident · Minor injuriesClosedStinson 108 · Nelsonville, OHNTSB CHI71FBQ21Accident · No injuriesClosedCessna 172 · Manito, ILNTSB CHI71FDJ23Accident · 2 fatalClosedConvair PBY-5A · Rhinelander, WINTSB NYC71DFA18Accident · No injuriesClosedStinson 108-2 · Heuvelton, NYNTSB CHI71FGD22Accident · Serious injuriesClosedMitsubishi MU-2B · Northfield, OHNTSB NYC71AN048Accident · No injuriesClosedPiper PA-23 · Erie, PANTSB CHI71FBD30Accident · Minor injuriesClosedCessna 150H · Sandwich, ILNTSB NYC71AN046Accident · 1 fatalClosedPiper PA-28 · Hammondsport, NYNTSB NYC71DFA23Accident · No injuriesClosedPiper PA-18A · Newcomb, NYNTSB OAK71FVM19Accident · 1 fatalClosedPiper PA-28 · Calif.Vally, CANTSB NYC71FFM19Accident · Minor injuriesClosedAeronca 7AC · Lanesboro, MA