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 ANC72FAG41Accident · No injuriesClosedStinson 108-3 · Bethel, AKNTSB MKC72FCG11Accident · Minor injuriesClosedHiller UH-12A · Springfield, MONTSB MIA72FLG07Accident · No injuriesClosedPiper PA-28 · Greenville, SCNTSB CHI72FEV09Accident · No injuriesClosedPiper PA-25 · Perryville, MONTSB DEN72FTC03Accident · Serious injuriesClosedPiper J3C-65 · Midwest, WYNTSB DEN72FQA10Accident · No injuriesClosedPiper PA-28 · Albuquerque, NMNTSB ANC72DAG16Accident · No injuriesClosedPiper PA-18 · Glenallen, AKNTSB NYC72FNE04Accident · No injuriesClosedCessna 140 · Meriden, CTNTSB NYC72AN022Accident · 1 fatalClosedCessna 170B · Wellsboro, PANTSB MKC72FER14Accident · 1 fatalClosedPiper PA-18 · Rosemont, MNNTSB FTW72FRD04Accident · No injuriesClosedStinson 108-1 · De Quincy, LANTSB DEN72FQA09Accident · No injuriesClosedHelio Acft H-395 · Albuquerque, NMNTSB CHI72FEG04Accident · Minor injuriesClosedCessna 172 · Cincinnati, OHNTSB NYC72FGM09Accident · Serious injuriesClosedErco 415C · Irwin, PANTSB NYC72DFJ09Accident · No injuriesClosedCessna 150 · Geneseo, NYNTSB MIA72FKG19Accident · Serious injuriesClosedGlobe GC-1B · Fairburn, GANTSB LAX72FUJ25Accident · Minor injuriesClosedCessna 402A · Scottsdale, AZNTSB DEN72DTE09Accident · No injuriesClosedPiper PA-25 · Center, CONTSB NYC72FFA06Accident · No injuriesClosedCessna 150G · Gloversville, NYNTSB MKC72AK009Accident · 1 fatalClosedCessna 172 · Bridgewater, IANTSB LAX72DUJ22Accident · No injuriesClosedPiper PA-25 · Parker, AZNTSB CHI72FEP06Accident · Minor injuriesClosedDavis V-3 · Wauwatosa, WINTSB CHI72AC012Accident · No injuriesClosedBeech 99 · Ashland, WINTSB MKC72FERI2Accident · Serious injuriesClosedAeronca 15AC · Warroad, MNNTSB MKC72DCD19Accident · No injuriesClosedPiper PA-25 · Olin, IANTSB DEN72FTE07Accident · No injuriesClosedPiper PA-24 · Denver, CONTSB MIA72DLA04Accident · No injuriesClosedAero Comdr A-9 · Altha, FLNTSB FTW72DQG12Accident · No injuriesClosedCessna 320B · Lubbock, TXNTSB SEA72FYE04Accident · No injuriesClosedJeanies Teenie · Kent, WANTSB CHI72DEP10Accident · Minor injuriesClosedPiper J-3 · East Troy, WINTSB CHI72FER01Accident · Minor injuriesClosedPiper PA-28 · Trego, WINTSB NYC72AN016Accident · 1 fatalClosedGrumman G-21A · Greenville, MENTSB DCA72AZ002Accident · Serious injuriesClosedBoeing 747 · San Frncsco, CANTSB NYC72AN014Accident · 1 fatalClosedPiper PA-18 · Essex, NYNTSB DEN72FQA08Accident · No injuriesClosedMiniplane Faler · Gallup, NMNTSB MIA72RM007Accident · 1 fatalClosedPiper PA-30 · T.Cay, Burkina FasoNTSB FTW72FQG07Accident · 1 fatalClosedPiper PA-18 · Texline, TXNTSB SEA72FYC11Accident · No injuriesClosedCessna 150 · Albany, ORNTSB NYC72FNC05Accident · No injuriesClosedCessna 170B · Brookline, NHNTSB NYC72FFJ07Accident · No injuriesClosedPiper PA-20 · Palmyra, NYNTSB MKC72FER08Accident · Minor injuriesClosedPiper PA-28 · New Ulm, MNNTSB SEA72AS004Accident · No injuriesClosedDouglas DC-3C · Redmond, ORNTSB NYC72AN011Accident · 2 fatalClosedPiper PA-30 · Madison, CTNTSB OAK72FXQ03Accident · Minor injuriesClosedCessna 177 · Quincy, CANTSB NYC72FFA04Accident · Minor injuriesClosedStinson 108-2 · Malone, NYNTSB IAD72FID02Accident · Minor injuriesClosedCessna 182 · Headwaters, VANTSB ANC72DAG12Accident · No injuriesClosedStinson 108-2 · Kodiak, AKNTSB NYC72FHJ02Accident · No injuriesClosedNavion A · Bohemia, NYNTSB DEN72FQA06Accident · No injuriesClosedPiper PA-32 · Albuquerque, NMNTSB MKC72FCJ08Accident · No injuriesClosedBeech J35 · Ogallala, NE