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 IAD70DIG10Accident · No injuriesClosedCallair A-9 · Massey, MDNTSB FTW70DPG07Accident · No injuriesClosedErco 415-C · West Point, ARNTSB DEN70DXD11Accident · No injuriesClosedAmer Avco AA-1 · Aurora, CONTSB CHI70FBQ08Accident · No injuriesClosedPiper PA-28 · Paris, ILNTSB OAK70DVM06Accident · No injuriesClosedPiper J3C-65 · Half Mn Bay, CANTSB MKC70FCG12Accident · Minor injuriesClosedTaylorcraft BL-65 · Springfield, MONTSB MKC70FCG11Accident · Minor injuriesClosedAeronca 7AC · Smithville, MONTSB CHI70AC060Accident · 2 fatalClosedAeronca 7AC · Columbus, OHNTSB LAX70AL053Accident · 2 fatalClosedPiper PA-23 · Pacoima, CANTSB LAX70FUQ16Accident · Minor injuriesClosedBeech P35 · Goleta, CANTSB MIA70DLD07Accident · Minor injuriesClosedCessna 120 · Brooksville, FLNTSB IAD70FLJ01Accident · 1 fatalClosedPiper PA-22 · Orrum, NCNTSB FTW70DRG16Accident · No injuriesClosedCessna 310L · Matamoros, MexicoNTSB MIA70AM073Accident · Minor injuriesClosedGrumman G-21A · St Thomas, VINTSB CHI70AC057Accident · 2 fatalClosedPiper PA-28 · Kokomo, INNTSB LAX70DUJ15Accident · Minor injuriesClosedCessna 172E · Mormon Lake, AZNTSB OAK70DVW03Accident · No injuriesClosedBeech BE23 · Mesquite, NVNTSB LAX70FUG10Accident · Minor injuriesClosedCessna 172 · Rubidoux, CANTSB MIA70AM072Accident · 1 fatalClosedAeronca 7BCM · Augusta, GANTSB FTW70DRG12Accident · No injuriesClosedCessna 177 · Randolph Afb, TXNTSB MKC70DCQ08Accident · No injuriesClosedCessna 401A · Independence, KSNTSB LAX70FUG09Accident · No injuriesClosedPiper PA-28R · Big Bear City, CANTSB FTW70FRD01Accident · Minor injuriesClosedBell 47G-4A · Morgan City, LANTSB DEN70FXA05Accident · Serious injuriesClosedPiper PA-22 · Saratoga, WYNTSB CHI70FGD06Accident · Minor injuriesClosedPiper PA-22 · Painesville, OHNTSB CHI70DBD18Accident · No injuriesClosedLil Honey H0-1 · Monee, ILNTSB NYC70AN089Accident · 1 fatalClosedCessna 182 · Sterling, MANTSB LAX70FUQ09Accident · Minor injuriesClosedBell 47D1 · Van Nuys, CANTSB OAK70DXQ03Accident · No injuriesClosedRepublic RC-3 · Homewood, CANTSB DEN70FXT05Accident · No injuriesClosedCessna 320 · Ephraim, UTNTSB SEA70DWA04Accident · No injuriesClosedCessna 182 · Hillsboro, ORNTSB MKC70FCJ07Accident · No injuriesClosedPiper PA-28 · Gothenburg, NENTSB NYC70FHM06Accident · Minor injuriesClosedCessna 172D · Spring Valley, NYNTSB MKC70FDM08Accident · No injuriesClosedCessna 150G · Crystal, MNNTSB NYC70FFM04Accident · No injuriesClosedAeronca 7AC · Turner Falls, MANTSB ANC70DAG04Accident · No injuriesClosedCessna 180 · Glennallen, AKNTSB MKC70FDM07Accident · No injuriesClosedCessna 140 · Onamia, MNNTSB MKC70DDM05Accident · No injuriesClosedCessna 180 · Minneapolis, MNNTSB MIA70DLG01Accident · Minor injuriesClosedJay Tee RCB-1 · Johns Island, SCNTSB DEN70FXD08Accident · No injuriesClosedAero Comdr 520 · Broomfield, CONTSB MKC70DDQ04Accident · Minor injuriesClosedPiper PA-24 · Onida, SDNTSB FTW70DRG06Accident · No injuriesClosedCessna 182D · Mcallen, TXNTSB MKC70FCJ03Accident · Minor injuriesClosedPiper PA-22 · Mccool Jct, NENTSB MKC70DDQ12Accident · No injuriesClosedPiper PA-18 · Altamont, SDNTSB OAK70DJA04Accident · No injuriesClosedPiper PA-30 · S.Pole Station, AYNTSB MIA70FKG04Accident · No injuriesClosedCessna 177 · Villa Rica, GANTSB MIA70DKA01Accident · Serious injuriesClosedCessna 172 · Guayma, PRNTSB NYC70FFD07Accident · Minor injuriesClosedPiper PA-23 · Mansfield, MANTSB NYC70FHJ01Accident · Minor injuriesClosedCessna 172 · Farmingdale, NYNTSB CHI70FBG02Accident · No injuriesClosedHughes 269B · Farmington, MI