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 SEA71FWG46Accident · Minor injuriesClosedPiper PA-28 · Creston, WANTSB NYC71IN102Incident · No injuriesClosedBoeing 727 · Jamaica, NYNTSB DEN71DXA22Accident · No injuriesClosedPiper PA-25 · Casper, WYNTSB MKC71FCG56Accident · Serious injuriesClosedCessna 150G · Albany, MONTSB LAX71FUJ85Accident · Minor injuriesClosedCessna U206D · Phoenix, AZNTSB DEN71DXA21Accident · No injuriesClosedPiper PA-18 · Seely, WYNTSB OAK71DVG91Accident · No injuriesClosedCessna 150 · Wilseyville, CANTSB MKC71FDM55Accident · No injuriesClosedPiper PA-28 · Oklee, MNNTSB MIA71FLA41Accident · Serious injuriesClosedChampion 7ECA · Bunnell, FLNTSB SEA71FTA06Accident · No injuriesClosedTaylorcraft BC12-D · Baker, MTNTSB MIA71FLA42Accident · No injuriesClosedCessna 150 · Macclenny, FLNTSB OAK71FXQ35Accident · No injuriesClosedCessna 180 · Reno, NVNTSB NYC71FGT13Accident · Minor injuriesClosedStinson 10-A · Hallam, PANTSB IAD71FIG27Accident · Minor injuriesClosedPiper J3L-65 · Edgewater, MDNTSB FTW71FPG47Accident · Minor injuriesClosedPiper J3C-65 · Tamo, ARNTSB MKC71FCJ59Accident · Minor injuriesClosedCallair A9 · Gibbon, NENTSB MKC71DCG53Accident · Minor injuriesClosedCessna 150 · Pt Lookout, MONTSB NYC71IN099Incident · No injuriesClosedBoeing 707 · Jamaica, NYNTSB MIA71FLG27Accident · No injuriesClosedPiper J3C-65 · Pageland, SCNTSB OAK71DXQ33Accident · No injuriesClosedBeech 35 · Gerlach, NVNTSB LAX71FUG68Accident · Minor injuriesClosedBriegleb BG12BD · Perris, CANTSB ANC71AA050Accident · 1 fatalClosedPiper PA-22 · Fairbanks, AKNTSB SEA71FWA32Accident · Serious injuriesClosedStarduster Stolp · Vancouver, WANTSB MKC71FCJ57Accident · Minor injuriesClosedBowers 1A375G · Norfolk, NENTSB IAD71FIG29Accident · No injuriesClosedPiper PA-28 · White Marsh, MDNTSB MIA71DKQ32Accident · No injuriesClosedPiper PA-18A · Quitman, MSNTSB SEA71DWG41Accident · No injuriesClosedPiper PA-22 · Dishman, WANTSB CHI71FLQ21Accident · No injuriesClosedBede BD-4 · Louisville, KYNTSB MKC71FCG51Accident · No injuriesClosedCessna 182L · Kansas City, KSNTSB MKC71DDQ26Accident · No injuriesClosedCessna 170 · Wsngtn Spgs, SDNTSB LAX72DUJ13Accident · No injuriesClosedBeech 35 · Coolidge, AZNTSB NYC71FGM35Accident · Minor injuriesClosedPiper PA-22 · Hawthorne, PANTSB LAX71DUG69Accident · No injuriesClosedCessna 177 · Heme, CANTSB FTW71FPJ36Accident · No injuriesClosedCallair A-4 · Terral, OKNTSB CHI71FBD59Accident · No injuriesClosedCessna 150 · Naperville, ILNTSB SEA71DXM28Accident · No injuriesClosedCessna 150 · Burley, IDNTSB ANC71DAG81Accident · No injuriesClosedPiper PA-22 · Port Alswrth, AKNTSB ANC71DAG78Accident · No injuriesClosedMaule M-4 · Glenallen, AKNTSB CHI71DBA33Accident · No injuriesClosedPiper J3 · Mechanicsburg, INNTSB FTW71FPG43Accident · 2 fatalClosedN.American P-51D · Hot Springs, ARNTSB CHI71FDJ46Accident · Minor injuriesClosedCessna 150F · Racine, WINTSB MIA71DKJ44Accident · Minor injuriesClosedCessna 182 · Toney, ALNTSB NYC71IN092Incident · No injuriesClosedDouglas DC9-10 · Newark, NJNTSB CHI71FBM18Accident · No injuriesClosedPiper PA-28 · Kendallville, INNTSB ANC71AA046Accident · No injuriesClosedCessna 180H · Bethel, AKNTSB IAD71II041Incident · No injuriesClosedBoeing 727 · Washington, DCNTSB FTW71FRG94Accident · Serious injuriesClosedCessna 172K · Killeen, TXNTSB FTW71DPG41Accident · No injuriesClosedGrumman G-164A · Earl, ARNTSB SEA71AS039Accident · 2 fatalClosedAeronca 7FC · Snohomish, WANTSB SEA71AS038Accident · Minor injuriesClosedCessna 182F · Okanogan, WA