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 ANC72AA024Accident · 1 fatalClosedChampion 7GCBC · Prescott, AZNTSB ANC72FAG76Accident · No injuriesClosedPiper PA-28 · Naknek, AKNTSB MIA72AM045Accident · 4 fatalClosedPiper PA-31 · Iuka, MSNTSB ANC72AA021Accident · No injuriesClosedCessna 180H · Gulkana, AKNTSB MIA72FKG55Accident · Minor injuriesClosedPiper PA-22 · Wrens, GANTSB SEA72DYC23Accident · No injuriesClosedCessna 175 · Condon, ORNTSB ANC72FAA21Accident · No injuriesClosedCessna 170B · Livengood, AKNTSB CHI72DEG23Accident · No injuriesClosedBeech A24R · Jamestown, OHNTSB MKC72FCJ36Accident · Minor injuriesClosedPiper PA-22 · Nehawka, NENTSB CHI72FEP21Accident · Serious injuriesClosedLuscombe 8A · Stevens Pt., WINTSB NYC72IN064Incident · No injuriesClosedBoeing 747 · Jamaica, NYNTSB MIA72AM042Accident · 2 fatalClosedPiper PA-22 · Watkinsville, GANTSB FTW72FRD16Accident · No injuriesClosedBoeing E75N1 · Elton, LANTSB FTW72FPM13Accident · No injuriesClosedBeech A23A · Maramec, OKNTSB FTW72FPM12Accident · Minor injuriesClosedStinson 108-1 · Calera, OKNTSB CHI72FEE43Accident · No injuriesClosedCessna 310H · Elgin, ILNTSB LAX72FUG32Accident · Serious injuriesClosedCessna 150 · Riverside, CANTSB CHI72AC042Accident · No injuriesClosedBeech 99 · Cleveland, OHNTSB MIA72DKT26Accident · No injuriesClosedPiper PA-25 · Ft Myers, FLNTSB NYC72FGM21Accident · Minor injuriesClosedPiper PA-22 · Somerset, PANTSB LAX72FUG30Accident · Minor injuriesClosedPiper PA-24 · El Monte, CANTSB MKC72DCQ31Accident · No injuriesClosedBeech B95 · Salina, KSNTSB MIA72FLG16Accident · Serious injuriesClosedPiper PA-25 · Edisto Is, SCNTSB EU172DYZ03Accident · Minor injuriesClosedBell 47G3B1 · Bharatpur, NepalNTSB SEA72FYG18Accident · Serious injuriesClosedPiper PA-32 · College Pl, WANTSB OAK72AP012Accident · Minor injuriesClosedCessna 150 · Franklin, CANTSB DEN72FTE36Accident · Minor injuriesClosedPiper PA-28 · Denver, CONTSB DEN72AD019Accident · No injuriesClosedCessna 310B · Creede, CONTSB MKC72FCJ34Accident · Serious injuriesClosedPiper J3C-65 · Atkinson, NENTSB MKC72FCG31Accident · No injuriesClosedPiper PA-22 · Buffalo, MONTSB FTW72FRD14Accident · Minor injuriesClosedCessna 180 · Paincourtville, LANTSB MIA72AM041Accident · 4 fatalClosedBeech 95-B55 · St.Petersburg, FLNTSB LAX72FUQ37Accident · No injuriesClosedCessna 170B · Quartz Hill, CANTSB LAX72FUA18Accident · Serious injuriesClosedLuscombe 8A · Long Beach, CANTSB NYC72AN058Accident · 2 fatalClosedFairchild M-62A · Stevensville, MDNTSB SEA72FYC21Accident · No injuriesClosedDruin Turb D-31 · Corvallis, ORNTSB LAX72DUM12Accident · No injuriesClosedWaterman W-12 · Carlsbad, CANTSB ANC72AA017Accident · 1 fatalClosedPiper PA-18S · Pilot Point, AKNTSB LAX72AL025Accident · 1 fatalClosedSchemp SH-1 · Tehachapi, CANTSB ANC72DAG65Accident · No injuriesClosedPiper PA-22 · Russian Miss, AKNTSB FTW72FRG32Accident · Minor injuriesClosedCessna 150G · San Antonio, TXNTSB NYC72FIA07Accident · Minor injuriesClosedPiper PA-22 · Elkton, VANTSB SEA72DYC20Accident · No injuriesClosedPiper PA-22 · Oregon City, ORNTSB NYC72DNC15Accident · Minor injuriesClosedLuscombe 8A · Auburn, NHNTSB NYC72AN048Accident · 3 fatalClosedMooney M21 · Zelienople, PANTSB FTW72FPA07Accident · No injuriesClosedHiller UH-12C · Lorena, TXNTSB FTW72DPJ17Accident · No injuriesClosedCessna 182 · Walters, OKNTSB MKC72DCG25Accident · No injuriesClosedCessna 140 · Independence, MONTSB LAX72FUQ32Accident · Minor injuriesClosedErco 415-C · Thous.Oaks, CANTSB MKC72DCJ29Accident · No injuriesClosedPiper PA-28 · Glenvil, NE