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 DEN70DXD34Accident · Minor injuriesClosedPiper PA-28 · Kim, CONTSB DEN70FQD15Accident · No injuriesClosedBellanca 14-19 · Anthony, NMNTSB CHI70FBD36Accident · Serious injuriesClosedN.American SNJ-4 · Addison, ILNTSB CHI70FBA19Accident · Serious injuriesClosedBeech J35 · Greenwood, INNTSB CHI70DBJ26Accident · No injuriesClosedTaylorcraft BC1285 · Slt St Marie, MINTSB MKC70FDD15Accident · No injuriesClosedCallair B-1A · Grand Forks, NDNTSB LAX70FUQ29Accident · Minor injuriesClosedStinson 108 · Santa Paula, CANTSB OAK70AP059Accident · 2 fatalClosedBeech B95 · Sacramento, CANTSB NYC70AN120Accident · 1 fatalClosedTaylorcraft BC12-D · Vineland, NJNTSB MKC70DDD14Accident · Minor injuriesClosedPiper PA-25 · Walhalla, NDNTSB MKC70DCQ22Accident · No injuriesClosedPiper PA-22 · Bird City, KSNTSB MIA70FKG32Accident · Minor injuriesClosedAmer Avco AA-1 · Dallas, GANTSB DEN70AD056Accident · 2 fatalClosedPiper PA-28R · Boulder, CONTSB CHI70FDW14Accident · No injuriesClosedStinson 108-3 · Flushing, MINTSB MKC70FCA11Accident · No injuriesClosedBeech 95-C55 · Berkeley, MONTSB FTW70FQA21Accident · No injuriesClosedPiper PA-28R · Raton, NMNTSB LAX70FUQ33Accident · No injuriesClosedCessna 172 · Santa Barbara, CANTSB FTW70FPJ19Accident · Serious injuriesClosedCessna 170A · Midwest City, OKNTSB CHI70FDW13Accident · Minor injuriesClosedCessna 140 · Port Huron, MINTSB NYC70FFD15Accident · No injuriesClosedCessna 172F · Beverly, MANTSB MKC70DCJ22Accident · No injuriesClosedAeronca 65-TC · Fairmont, NENTSB MKC70AK061Accident · 1 fatalClosedCessna 140 · Waldron, MONTSB FTW70FPM07Accident · No injuriesClosedAeronca 11AC · Grove, OKNTSB FTW70FPJ18Accident · Serious injuriesClosedRyan ST-3KR · Overbrook, OKNTSB OAK70AP057Accident · No injuriesClosedCessna 206 · San Frncsco, CANTSB OAK70DVG23Accident · No injuriesClosedPiper PA-32 · Calistoga, CANTSB MIA70FKQ08Accident · No injuriesClosedCessna 188 · Shelby, MSNTSB FTW70FRA17Accident · No injuriesClosedGrumman G-164 · Devers, TXNTSB NYC71FHA38Accident · Minor injuriesClosedCessna 177 · Basking Ridge, NJNTSB OAK70FXQ11Accident · No injuriesClosedPiper PA-28R · Bridgeport, CANTSB CHI70FBD33Accident · Minor injuriesClosedHughes 269A · West Chicago, ILNTSB CHI70AC078Accident · 2 fatalClosedCessna 177A · Lexington, OHNTSB FTW70FQG13Accident · Serious injuriesClosedPiper PA-28 · Slaton, TXNTSB MIA70AM091Accident · 2 fatalClosedNavion A · Dalton, GANTSB LAX70FUG31Accident · No injuriesClosedHughes 269A · Arlington, CANTSB FTW70FPG25Accident · No injuriesClosedGrumman G-164A · Amagon, ARNTSB FTW70DRJ09Accident · No injuriesClosedPiper PA-28 · Lufkin, TXNTSB UNK70X0111Accident · Serious injuriesClosedCessna 150 · Gelnhausn, GeorgiaNTSB MKC70DCQ19Accident · No injuriesClosedPiper PA-28 · Hill City, KSNTSB MKC70DCJ21Accident · No injuriesClosedPiper PA-25 · Snyder, NENTSB NYC70FGM15Accident · Minor injuriesClosedCessna 150E · Punxsutawney, PANTSB CHI70DBD26Accident · No injuriesClosedCessna 150G · Naperville, ILNTSB MKC70DCJ19Accident · No injuriesClosedPiper PA-28 · Gothenburg, NENTSB MKC70FDM22Accident · No injuriesClosedRhein RW3B · Minneapolis, MNNTSB MKC70DGD40Accident · No injuriesClosedCessna 150A · Maysville, IANTSB CHI70FBJ22Accident · Minor injuriesClosedPiper PA-22 · Orangeville, MINTSB NYC70FFA18Accident · No injuriesClosedCessna 172 · Schroon Lake, NYNTSB LAX70FUG27Accident · Minor injuriesClosedCessna 140 · Rialto, CANTSB FTW70FPG19Accident · Minor injuriesClosedCessna 177 · Little Rock, ARNTSB NYC70AN115Accident · No injuriesClosedBell 47J-2 · Piscataway, NJ