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 CHI71FEM78Accident · Minor injuriesClosedChampion 7KCAB · Kalamazoo, MINTSB CHI71DER07Accident · No injuriesClosedPiper J3C · Star Prairie, WINTSB SEA71DYG51Accident · No injuriesClosedChampion 7GCBC · Coulee Dam, WANTSB FTW71FPJ41Accident · No injuriesClosedBeech G50 · Oklahoma Cty, OKNTSB ANC71DAD07Accident · Serious injuriesClosedTaylorcraft BC12-D · Sitka, AKNTSB OAK71FVA71Accident · No injuriesClosedPiper PA-32 · Merced, CANTSB FTW71DRH24Accident · No injuriesClosedGrumman G-164A · Floresville, TXNTSB MKC71FCG64Accident · Minor injuriesClosedPiper PA-28 · Liberty, MONTSB DEN71DTM28Accident · No injuriesClosedFournier RF4D · Park City, UTNTSB CHI71DEE77Accident · No injuriesClosedGlobe GC-1B · Kankakee, ILNTSB ANC71FAG87Accident · Serious injuriesClosedStinson 108-2 · Seldovia, AKNTSB MKC71DCD81Accident · No injuriesClosedPiper PA-28 · Guthrie Ctr, IANTSB IAD71AI051Accident · No injuriesClosedPiper PA-32 · Buckhannon, WVNTSB MKC71DCG65Accident · No injuriesClosedCessna 150H · Stillwell, KSNTSB DEN71FQD21Accident · No injuriesClosedPiper PA-24 · Pinon, NMNTSB MIA71AM093Accident · 4 fatalClosedCessna 421A · Conestee, SCNTSB SEA71DYI24Accident · Minor injuriesClosedAeronca 7AC · Central Pt, ORNTSB NYC71FGM45Accident · No injuriesClosedCessna 172 · Beaver Falls, PANTSB DEN71FXT23Accident · No injuriesClosedPiper PA-32 · Provo, UTNTSB DEN71DQD19Accident · Minor injuriesClosedCessna 150 · Silver City, NMNTSB SEA71FYC39Accident · No injuriesClosedPiper PA-28 · Troutdale, ORNTSB SEA71FYC37Accident · No injuriesClosedMaule M-4 · Aurora, ORNTSB SEA71DYG47Accident · No injuriesClosedMooney M20F · Lacrosse, WANTSB MKC71FCG61Accident · No injuriesClosedStinson 108-3 · Gravois Mills, MONTSB FTW71FPD52Accident · No injuriesClosedHiller UH12B · Arlington, TXNTSB DEN71FTE05Accident · Serious injuriesClosedCessna 310C · Denver, CONTSB FTW71FRA47Accident · No injuriesClosedGrumman G-164 · Katy, TXNTSB SEA71DYG50Accident · No injuriesClosedNavion B · Spokane, WANTSB MKC71DCD78Accident · Serious injuriesClosedCessna 140 · Lamoni, IANTSB CHI71FEM73Accident · Minor injuriesClosedCessna 172 · Hillman, MINTSB IAD71FKM35Accident · No injuriesClosedCessna 150G · Mocksville, NCNTSB MKC71FCG57Accident · Minor injuriesClosedPiper PA-25 · Marshall, MONTSB MIA71FKT56Accident · No injuriesClosedPiper PA-24 · Opa Locka, FLNTSB CHI71FLQ23Accident · No injuriesClosedVolksplane VP-1 · Louisville, KYNTSB CHI71FEC46Accident · No injuriesClosedCessna 150E · Memphis, MINTSB CHI71FEC45Accident · Minor injuriesClosedKay Spcl · Sterling Hts, MINTSB CHI71FEV38Accident · No injuriesClosedPiper PA-28 · Centralia, ILNTSB NYC71AN106Accident · 4 fatalClosedBeech C35 · Manchester, NHNTSB NYC71FFA57Accident · 1 fatalClosedPiper PA-25 · Livingston, NYNTSB MKC71DCQ36Accident · No injuriesClosedBeech J35 · Plainville, KSNTSB MIA71AM091Accident · 8 fatalClosedBeech E18S · C.Amalie, VINTSB SEA71FDG28Accident · No injuriesClosedPiper PA-18 · Hamilton, MTNTSB NYC71DHD46Accident · Serious injuriesClosedBell 47G4 · Mays Ldg, NJNTSB OAK71FVG73Accident · Serious injuriesClosedPiper PA-24 · Vacaville, CANTSB CHI71FET23Accident · No injuriesClosedPiper PA-25 · Roling Prarie, INNTSB SEA71FDA18Accident · No injuriesClosedCessna A188 · Joliet, MTNTSB NYC71FNA47Accident · No injuriesClosedCessna 170B · Taunton, MANTSB SEA71FDG27Accident · No injuriesClosedMooney M20C · Santa Rita, MTNTSB NYC71FFJ32Accident · No injuriesClosedPiper PA-23 · Horseheads, NYNTSB LAX71AL060Accident · 5 fatalClosedBeech G18S · Grnd Canyon, AZ