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 ANC72DAG06Accident · No injuriesClosedCessna 180H · Alakanuk, AKNTSB SEA72DYC01Accident · Minor injuriesClosedCessna A185E · John Day, ORNTSB MKC72FCJ01Accident · Minor injuriesClosedGrumman G-164 · Ainsworth, NENTSB MKC72DER01Accident · No injuriesClosedPiper J3C · Rapidan, MNNTSB LAX72FUJ08Accident · No injuriesClosedCessna 177 · Glendale, AZNTSB FTW72OFO70Accident · Minor injuriesClosedBell 205A-1 · Costa Rica, SerbiaNTSB FTW72FPD01Accident · Serious injuriesClosedTaylorcraft BC12-D · Southlake, TXNTSB CHI72FEV01Accident · Serious injuriesClosedChampion 7AC · Breese, ILNTSB SEA71DYC47Accident · No injuriesClosedCessna 172F · Lostine, ORNTSB MKC71FCA42Accident · No injuriesClosedPiper PA-12 · Dexter, MONTSB FTW71DQD28Accident · No injuriesClosedCessna 182 · Sierra Blanca, TXNTSB CHI71IC094Incident · No injuriesClosedCessna 414 · Detroit, MINTSB MKC71FER77Accident · Minor injuriesClosedCommonwlth 8135 · Royalton, MNNTSB IAD71DIJ24Accident · No injuriesClosedTaylorcraft BC12C · Frankford, WVNTSB FTW72DPG01Accident · No injuriesClosedCessna 172 · Rogers, ARNTSB CHI71FEK41Accident · Minor injuriesClosedCessna 150G · Columbus, OHNTSB SEA71DYA41Accident · Minor injuriesClosedPiper J-3 · Paris, IDNTSB MKC71FTG35Accident · Minor injuriesClosedCessna 172G · Alexander, NDNTSB FTW71AF086Accident · No injuriesClosedCessna 180H · Palmetto, LANTSB MIA71FKT67Accident · Minor injuriesClosedBeech C45-H · Dania, FLNTSB MKC71FTG32Accident · No injuriesClosedPiper PA-18 · Rolla, NDNTSB MIA71AM115Accident · 2 fatalClosedBeech E18S · Birmingham, ALNTSB LAX71FUG95Accident · No injuriesClosedCessna 150 · Corona, CANTSB FTW71DPM32Accident · No injuriesClosedGrumman G-164 · Dustin, OKNTSB MKC71FTK32Accident · No injuriesClosedGlobe GC-1B · Rapid City, SDNTSB DEN71DQA59Accident · No injuriesClosedBeech 35 · Eagle West, NMNTSB CHI71FEC57Accident · Minor injuriesClosedPiper PA-24 · Howell, MINTSB OAK71FXQ44Accident · Minor injuriesClosedCessna 177 · Minden, NVNTSB OAK71FVM56Accident · Serious injuriesClosedBeech 35 · San Carlos, CANTSB NYC71FHM32Accident · No injuriesClosedStinson 108 · Washingtonvlle, NYNTSB LAX71FVA90Accident · Serious injuriesClosedBoeing 75 · Buttonwillw, CANTSB CHI71FEP54Accident · No injuriesClosedPietenpol GN-1 · Menomonee Fls, WINTSB CHI71DEM88Accident · Minor injuriesClosedCessna 177 · Boyne Falls, MINTSB OAK71DVG85Accident · No injuriesClosedBeech B35 · Rio Linda, CANTSB MKC71FCD89Accident · No injuriesClosedBensen B-8M · Ames, IANTSB FTW71FPJ46Accident · 1 fatalClosedPiper PA-25 · Duncan, OKNTSB CHI71AC090Accident · 2 fatalClosedCessna 320C · Howell, MINTSB DEN71FQD26Accident · 1 fatalClosedBell 47J · Deming, NMNTSB MKC71DCQ43Accident · No injuriesClosedCessna 180C · Syracuse, KSNTSB FTW71DPG64Accident · No injuriesClosedPiper PA-25 · Tuckerman, ARNTSB LAX71FUG92Accident · No injuriesClosedFairchild M62A · Upland, CANTSB SEA71FYA35Accident · Serious injuriesClosedPiper PA-28 · Boise, IDNTSB MKC71FER72Accident · Serious injuriesClosedCessna 150 · Browerville, MNNTSB FTW71FRA67Accident · Minor injuriesClosedCessna 150L · China, TXNTSB DEN71AD048Accident · 3 fatalClosedBeech 23 · Rocky Ford, CONTSB CHI71FLQ25Accident · No injuriesClosedCessna 150K · New Haven, KYNTSB CHI71AC088Accident · 3 fatalClosedCessna 180 · Whitmore Lk, MINTSB FTW71IF082Incident · No injuriesClosedDouglas DC-3A · Okla.City, OKNTSB CHI71FLQ27Accident · Minor injuriesClosedNavion 4 · Berea, KYNTSB MIA71DKJ59Accident · No injuriesClosedCessna 150 · Muscle Shoals, AL