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 OAK71DVA15Accident · No injuriesClosedPiper PA-12 · Fresno, CANTSB NYC71DFD03Accident · Minor injuriesClosedCessna 150H · Stow, MANTSB CHI71FGD07Accident · Minor injuriesClosedPiper PA-28 · Bowlng Green, OHNTSB SEA71FDG07Accident · No injuriesClosedLuscombe 8F · Butte, MTNTSB NYC71FHD13Accident · Serious injuriesClosedNoorduyn UC-64 · Lakewood, NJNTSB MKC71FDM15Accident · No injuriesClosedCessna 172E · Elysian, MNNTSB MIA71FKJ07Accident · No injuriesClosedPiper PA-22 · Moundville, ALNTSB MIA71AM010Accident · 4 fatalClosedPiper PA-28 · Brunswick, GANTSB FTW71FPD08Accident · No injuriesClosedPiper PA-28 · Denton, TXNTSB CHI71FDW08Accident · Serious injuriesClosedPiper PA-28 · Chelsea, MINTSB CHI71FBJ16Accident · No injuriesClosedMeyers 200-B · Midland, MINTSB FTW71DQG20Accident · Minor injuriesClosedTed Smith 601 · Big Spring, TXNTSB DEN71FXD22Accident · Serious injuriesClosedCessna 172 · Denver, CONTSB MKC71DCJ13Accident · No injuriesClosedCessna 188A · Oxford, NENTSB CHI71FDW09Accident · Minor injuriesClosedPitts Specl S-1CBD · Pontiac, MINTSB MKC71FCA16Accident · Minor injuriesClosedStinson 108-3 · Mountain View, MONTSB MIA71DLA07Accident · Minor injuriesClosedCessna 150F · Spring Creek, FLNTSB DEN71FXD15Accident · No injuriesClosedPiper PA-28 · Ft.Collins, CONTSB OAK71FUA11Accident · No injuriesClosedGrumman TBM-3E · Columbia, CANTSB FTW71DQD03Accident · No injuriesClosedCessna 210 · Balmorhea, TXNTSB MKC71FCG09Accident · No injuriesClosedAeronca 7AC · Independence, MONTSB MIA71FKA01Accident · No injuriesClosedCessna 172D · Vega Baja, PRNTSB MIA71DKJ05Accident · No injuriesClosedPiper PA-28 · Dutton, ALNTSB DEN71FXD12Accident · 1 fatalClosedPiper PA-28R · Montrose, CONTSB CHI71FDJ08Accident · Serious injuriesClosedTaylorcraft BC12-D · Land O Lakes, WINTSB CHI71FDJ07Accident · Serious injuriesClosedBeech E18S · Delavan, WINTSB LAX71FUA04Accident · No injuriesClosedPiper PA-28 · Torrance, CANTSB DEN71DQA11Accident · No injuriesClosedCessna 150F · Gallup, NMNTSB MKC71FCG08Accident · No injuriesClosedBellanca 14-13 · Waldron, MONTSB LAX71FUM08Accident · No injuriesClosedPiper PA-28 · Chula Vista, CANTSB CHI71FBJ11Accident · Serious injuriesClosedPiper PA-22 · Kalamazoo, MINTSB CHI71DDM01Accident · No injuriesClosedCessna 150C · River Falls, WINTSB ANC71FAG09Accident · No injuriesClosedCessna U206C · Naknek, AKNTSB MKC71FCD05Accident · No injuriesClosedPietenpol AAA-1 · Ottumwa, IANTSB LAX71FUG05Accident · No injuriesClosedPiper J3C-65 · Colton, CANTSB FTW71FQG07Accident · No injuriesClosedPiper PA-25 · Shallowater, TXNTSB OAK71FVA10Accident · No injuriesClosedBell 47G-2 · Exeter, CANTSB CHI71FDM02Accident · No injuriesClosedCessna 172G · Mondovi, WINTSB NYC71AN009Accident · Serious injuriesClosedBoeing 737 · Philadelphia, PANTSB SEA71FWG08Accident · Serious injuriesClosedMaule M-4 · Daisy, WANTSB MIA71DKW01Accident · No injuriesClosedBellanca 14-13 · Lawrenceburg, TNNTSB NYC71AN007Accident · 3 fatalClosedAero Comdr 680-FL · Butler, PANTSB SEA71FDG05Accident · No injuriesClosedPiper PA-23 · Helena, MTNTSB IAD71DKM02Accident · No injuriesClosedMooney M20F · Winston-Salem, NCNTSB MKC71FCJ05Accident · No injuriesClosedMooney M20C · Davenport, NENTSB MIA71FLG02Accident · No injuriesClosedBeech H35 · Cayce, SCNTSB NYC71AN006Accident · 4 fatalClosedPiper PA-28 · Conneaut Lake, PANTSB MKC71FCA01Accident · 2 fatalClosedCessna 210H · Van Buren, MONTSB CHI71FBJ05Accident · No injuriesClosedBell 47G · Edmore, MINTSB CHI71AC003Accident · 4 fatalClosedCessna 177 · Wild Rose, WI