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 CHI70D0204Accident · No injuriesClosedBeech A-23 · Neenah, WINTSB OAK70D0083Accident · No injuriesClosedBoeing 75 · Los Banos, CANTSB LAX70F0080Accident · Serious injuriesClosedPiper PA-28 · El Monte, CANTSB FTW70F0159Accident · Serious injuriesClosedMooney M20B · Quitman, ARNTSB CHI70D0132Accident · No injuriesClosedCessna 150 · Grandville, MINTSB MKC70F0214Accident · Serious injuriesClosedPiper PA-23 · Lake Elmo, MNNTSB MIA70D0108Accident · No injuriesClosedCessna 172 · Immokalee, FLNTSB LAX70F0077Accident · No injuriesClosedPiper PA-24 · Frazier Park, CANTSB IAD70A0014Accident · No injuriesClosedBeech D18S · Martinsburg, WVNTSB CHI70F0255Accident · Minor injuriesClosedPiper PA-28 · Atwater, OHNTSB CHI70F0148Accident · No injuriesClosedChampion 7ECA · Mooresville, INNTSB ANC70F0083Accident · Serious injuriesClosedPiper PA-11S · Willow, AKNTSB MKC70F0197Accident · No injuriesClosedFly-Baby Hughs · Vinland, KSNTSB DEN70A0011Accident · 3 fatalClosedBeech 23 · Colo.Springs, CONTSB MIA70F0114Accident · No injuriesClosedGrumman G-164 · Juncos, PRNTSB DEN70A0010Accident · 2 fatalClosedHughes 369HS · American Fork, UTNTSB CHI70F0218Accident · No injuriesClosedPiper PA-24 · Valley City, OHNTSB NYC70F0085Accident · Serious injuriesClosedAeronca 7AC · Oxford, NYNTSB MIA70D0200Accident · No injuriesClosedCessna A188 · Louisville, GANTSB DEN70F0088Accident · Serious injuriesClosedCessna 170 · Worland, WYNTSB DEN70D0095Accident · Minor injuriesClosedPiper PA-22 · Arriba, CONTSB SEA70F0121Accident · No injuriesClosedPiper PA-28R · Lake O Woods, ORNTSB NYC70D0114Accident · No injuriesClosedPiper PA-22 · Oxford, PANTSB MKC70F0135Accident · No injuriesClosedCessna 180 · Ely, MNNTSB MIA70F0147Accident · Minor injuriesClosedCessna 150 · Plymouth, FLNTSB OAK70D0067Accident · Minor injuriesClosedBoeing A75N1 · Kettlman City, CANTSB FTW70D0118Accident · No injuriesClosedBell 47G-4A · Morgan City, LANTSB DEN70D0114Accident · No injuriesClosedPiper J3C-65 · Denver, CONTSB NYC70A0020Accident · 1 fatalClosedBeech E35 · Block Island, RINTSB FTW70F0144Accident · Serious injuriesClosedAero Comdr S-2D · Lockport, LANTSB DEN70F0053Accident · Minor injuriesClosedStinson 108-1 · Albuquerque, NMNTSB NYC70F0123Accident · Minor injuriesClosedPiper PA-30 · Windsr Locks, CTNTSB MIA70F0286Accident · Serious injuriesClosedNavion A · Savannah, GANTSB SEA70F0077Accident · Serious injuriesClosedPuss Moth 80A · Tekoa, WANTSB CHI70F0254Accident · Minor injuriesClosedCessna 172 · Hobart, INNTSB MKC70I0081Incident · No injuriesClosedBell 47G3B1 · Hill City, SDNTSB MKC70F0178Accident · No injuriesClosedPiper PA-28 · Macon, MONTSB MKC70D0166Accident · No injuriesClosedCessna 150 · Maysville, MONTSB IAD70F0046Accident · Serious injuriesClosedPiper PA-32 · Salisbury, MDNTSB DIA70D0037Accident · No injuriesClosedPiper PA-28 · Franklin, NCNTSB MIA70F0080Accident · Minor injuriesClosedPiper J3C · Bentonia, MSNTSB OAK70A0009Accident · Minor injuriesClosedHughes 269B · Foresthill, CANTSB SEA70D0041Accident · No injuriesClosedWaco UMF · Adrian, ORNTSB MKC70D0143Accident · No injuriesClosedBoeing A75 · Hawarden, IANTSB LAX70F0230Accident · 2 fatalClosedBell 206A · Grnd Canyon, AZNTSB OAK70D0064Accident · No injuriesClosedCessna 150 · Los Banos, CANTSB CHI70D0136Accident · No injuriesClosedPiper J3C-65 · Midland, MINTSB FTW70D0139Accident · Minor injuriesClosedPiper PA-22 · Freer, TXNTSB CHI70A0011Accident · 2 fatalClosedCessna 177 · Northfield, OHNTSB OAK70F0142Accident · Serious injuriesClosedPiper PA-28R · Sacramento, CA