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 LAX70FUQ27Accident · No injuriesClosedErco 415-C · Pacoima, CANTSB LAX70AL066Accident · 4 fatalClosedCessna 172G · St.Johns, AZNTSB OAK70DVD17Accident · No injuriesClosedGlobe GC-1B · Concord, CANTSB MIA70DKT17Accident · Minor injuriesClosedCessna 172 · Sanibel, FLNTSB FTW70DRG58Accident · No injuriesClosedBeech 95-B55 · Cabo Sn Lucas, MexicoNTSB IAD70FKM04Accident · No injuriesClosedChampion 7FC · Yadkinville, NCNTSB LAX70FUG26Accident · No injuriesClosedCessna 310C · Chino, CANTSB MIA70AM083Accident · 2 fatalClosedDouglas C-54D · Miami, FLNTSB ANC70AA070Accident · No injuriesClosedDehavilland DHC-2 · Prudhoe Bay, AKNTSB SEA70AS038Accident · 2 fatalClosedLuscombe 8A · Vancouver, WANTSB MIA70FKQ03Accident · No injuriesClosedPiper PA-22 · Carthage, MSNTSB MIA70DLD18Accident · No injuriesClosedMooney M20C · Gotha, FLNTSB LAX70FUQ21Accident · No injuriesClosedPiper PA-28 · Santa Susana, CANTSB FTW70FPJ11Accident · Minor injuriesClosedCessna 182 · Okeene, OKNTSB FTW70DRG34Accident · No injuriesClosedBell 47G-4A · Corpus Chrsti, TXNTSB FTW70AF063Accident · 2 fatalClosedPiper PA-28 · Fordyce, ARNTSB DEN70AD052Accident · No injuriesClosedPiper PA-32 · Cloudcroft, NMNTSB OAK70DVD14Accident · No injuriesClosedCessna 172 · Schellville, CANTSB SEA70FWD08Accident · 1 fatalClosedBensen B-8 · Arlington, WANTSB MKC70FDD11Accident · No injuriesClosedMooney M20G · Forbes, NDNTSB MIA70FKW03Accident · Minor injuriesClosedCessna 150J · College Grve, TNNTSB NYC70AN107Accident · Serious injuriesClosedBeech C-45 · Albany, NYNTSB MKC70FCA06Accident · No injuriesClosedCessna 337C · St Louis, MONTSB FTW70DPD08Accident · No injuriesClosedCessna 150 · Eagle Mtn, TXNTSB MKC70FCA05Accident · Serious injuriesClosedCessna 310G · Cape Girardeau, MONTSB MIA70FKTI3Accident · Serious injuriesClosedCessna 172G · Clewiston, FLNTSB MIA70DLD11Accident · No injuriesClosedChampion 7ECA · Silver Sprngs, FLNTSB FTW70DRD09Accident · No injuriesClosedCessna 177 · Lacassine, LANTSB LAX70DUJ30Accident · No injuriesClosedGrumman G-164 · Poston, AZNTSB FTW70DRD08Accident · No injuriesClosedBoeing B75NL · Lake Charles, LANTSB MKC70DCA04Accident · Minor injuriesClosedCessna 172K · Clarence, MONTSB MIA70DKG23Accident · No injuriesClosedCessna 172 · St Marys, GANTSB IAD70II053Incident · No injuriesClosedDouglas DC-8F · Dulles Int Apt, VANTSB CHI70AC060Accident · 1 fatalClosedBeech 65-A80 · Wheeling, ILNTSB DEN70FXD18Accident · Minor injuriesClosedCessna 172 · Hudson, CONTSB SEA70FWD06Accident · No injuriesClosedBeech 35 · Georgetown, WANTSB OAK70AP048Accident · 6 fatalClosedPiper PA-30 · Cottonwood, CANTSB FTW70DPJ09Accident · Minor injuriesClosedBeech C-50 · Sonora, MexicoNTSB NYC70AN103Accident · 3 fatalClosedBeech C-45H · Binghamton, NYNTSB CHI70FBD21Accident · No injuriesClosedBeech 35 · West Chicago, ILNTSB ANC70AA065Accident · No injuriesClosedCessna 180 · Dillingham, AKNTSB FTW70AF057Accident · 3 fatalClosedCessna 310F · Millington, TNNTSB CHI70FBM08Accident · Serious injuriesClosedCessna 140 · Roanoke, INNTSB FTW70DRG20Accident · No injuriesClosedNavion A · Mason, TXNTSB MKC70DCG14Accident · No injuriesClosedCessna 150G · Tipton, MONTSB FTW70FPD06Accident · No injuriesClosedN.American SNJ-4 · Brownwood, TXNTSB NYC70FFG03Accident · No injuriesClosedCessna 411 · Claremont, NHNTSB LAX70FUI17Accident · No injuriesClosedCessna 177 · Sta Barbara, CANTSB CHI70FBG09Accident · Minor injuriesClosedBeech 23 · Monroe, MINTSB MIA70DKT09Accident · No injuriesClosedCessna 150 · Opa Locka, FL