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 DEN71AD031Accident · 4 fatalClosedBeech G35 · Aspen, CONTSB SEA71DXM19Accident · No injuriesClosedPiper PA-22 · Middleton, IDNTSB NYC71FFD32Accident · No injuriesClosedCessna 150K · Marlboro, MANTSB CHI71FBA23Accident · No injuriesClosedCessna 120 · Monticello, INNTSB CHI71DBA24Accident · No injuriesClosedCessna 150H · Richmond, INNTSB NYC71DFD30Accident · No injuriesClosedPiper PA-28 · Plymouth, MANTSB MKC71FDM42Accident · No injuriesClosedCessna 150J · Lake Elmo, MNNTSB DEN71FXD44Accident · Minor injuriesClosedPiper PA-32 · Littleton, CONTSB SEA71FWD26Accident · Minor injuriesClosedAero Comdr 100 · Vashon Isl, WANTSB MIA71FLG21Accident · Minor injuriesClosedPiper PA-22 · Calhoun Falls, SCNTSB MIA71DLD41Accident · No injuriesClosedCessna 177 · Minneola, FLNTSB SEA71FWA26Accident · Minor injuriesClosedCessna 172 · Oregon City, ORNTSB OAK71AP030Accident · 3 fatalClosedPiper PA-30 · H.Moon Bay, CANTSB NYC71DFM25Accident · Minor injuriesClosedLuscombe 11A · G.Barrington, MANTSB FTW71FPJ24Accident · No injuriesClosedCessna 150 · Wellston, OKNTSB CHI71FGD33Accident · Minor injuriesClosedCessna 150H · Akron, OHNTSB NYC71FFG09Accident · No injuriesClosedCessna 185 · Portland, MENTSB CHI71FBD47Accident · Serious injuriesClosedPiper PA-28 · Schaumburg, ILNTSB NYC71FFD25Accident · No injuriesClosedCessna 180 · Bedford, MANTSB MIA71DKT29Accident · No injuriesClosedCessna 177 · Ochopee, FLNTSB NYC71FFM24Accident · No injuriesClosedCessna 150D · Goshen, CTNTSB MIA71FKG54Accident · Serious injuriesClosedGlobe GC-1B · Stone Mountain, GANTSB FTW71FPA18Accident · No injuriesClosedErco 415-C · Plano, TXNTSB ANC71DAG60Accident · No injuriesClosedPiper PA-22 · Soldotna, AKNTSB NYC71IN070Incident · No injuriesClosedBoeing 747 · Jamaica, NYNTSB NYC71FHJ17Accident · Minor injuriesClosedPiper PA-28 · Flushing, NYNTSB CHI71FBJ47Accident · No injuriesClosedPiper PA-28 · Greenville, MINTSB MKC71DCD44Accident · No injuriesClosedCessna 150E · Johnston, IANTSB LAX71FUA33Accident · Minor injuriesClosedStinson 108-3 · Torrance, CANTSB SEA71FWD23Accident · Serious injuriesClosedCessna 177 · Eatonville, WANTSB CHI71FDJ32Accident · Serious injuriesClosedCessna 150 · Strum, WINTSB SEA71FXM17Accident · No injuriesClosedBeech G35 · Caldwell, IDNTSB MKC71FDM35Accident · Minor injuriesClosedAero Comdr 100 · Lake Elmo, MNNTSB OAK71DVM30Accident · No injuriesClosedCessna 150 · Palo Alto, CANTSB NYC71IN104Incident · No injuriesClosedBoeing 707 · Atlantic City, NJNTSB DEN71DQA36Accident · No injuriesClosedFleet Acft 7 · Cerillos, NMNTSB LAX71AL033Accident · 1 fatalClosedBell 47G3B1 · Rim Forest, CANTSB FTW71FRA32Accident · No injuriesClosedSnow 600S2D · Katy, TXNTSB NYC71FFM22Accident · 1 fatalClosedJeanies Teenie · Stratford, CTNTSB NYC71DFJ17Accident · No injuriesClosedMooney M18LA · Kirkville, NYNTSB MKC71DCD36Accident · Minor injuriesClosedPiper PA-28 · Denison, IANTSB ANC71DAG57Accident · No injuriesClosedPiper PA-11 · Skwentna, AKNTSB FTW71FRD27Accident · No injuriesClosedCessna 185D · Lafayette, LANTSB MKC71FCG34Accident · No injuriesClosedCessna 310 · Lawrence, KSNTSB CHI71FGG20Accident · No injuriesClosedBeech A23A · Hicksville, OHNTSB FTW71FQG45Accident · No injuriesClosedBoeing A75N1 · Tahoka, TXNTSB SEA71FVQ15Accident · Serious injuriesClosedPiper PA-28R · Bend, ORNTSB NYC71FGM22Accident · No injuriesClosedStits SA6B · Finleyville, PANTSB NYC71DHJ13Accident · No injuriesClosedCessna 172 · Flushing, NYNTSB LAX71FUQ37Accident · No injuriesClosedCessna 150G · Van Nuys, CA