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 MIA69F0099Accident · Minor injuriesClosedPiper PA-25 · Eufala, ALNTSB DIA69F0040Accident · Serious injuriesClosedAeronca 7AC · Benson, NCNTSB MKC69F0058Accident · No injuriesClosedPiper PA-18 · Sutton, NENTSB SEA69F0049Accident · Minor injuriesClosedCessna 188 · Sunnyside, WANTSB DEN69D0056Accident · No injuriesClosedHughes 269B · Buffalo, WYNTSB ANC69A0003Accident · No injuriesClosedBell 47G-2 · Minchumina, AKNTSB MIA69F0155Accident · No injuriesClosedHughes 269A · Cope, SCNTSB FTW69F0194Accident · No injuriesClosedBoeing B75N1 · Muleshoe, TXNTSB FTW69F0070Accident · Serious injuriesClosedPiper PA-25 · Madisonville, TXNTSB OAK69F0049Accident · Serious injuriesClosedBriegleb BG-12B · Reno, NVNTSB MIA69D0136Accident · No injuriesClosedCallair A-9 · Estill, SCNTSB SEA69A0007Accident · 1 fatalClosedCommonwlth 9000 · Vancouver, WANTSB MIA69D0123Accident · Minor injuriesClosedAeronca 7ECA · Cedartown, GANTSB CHI69F0177Accident · No injuriesClosedPiper J3C-65 · Cynthiana, KYNTSB CHI69F0142Accident · Minor injuriesClosedPiper PA-22 · Put In Bay, OHNTSB CAN68F0499Accident · 1 fatalClosedRepublic RC-3 · Harris Lake, CanadaNTSB OAK69I0006Incident · Minor injuriesClosedBoeing 727 · Seattle, WANTSB OAK69F0105Accident · No injuriesClosedBeech A35 · Tah0e Valley, CANTSB NYC69F0105Accident · No injuriesClosedPiper PA-22 · Leechburg, PANTSB NYC69D0060Accident · No injuriesClosedPiper J-3 · Hamlin, PANTSB CHI69A0008Accident · 1 fatalClosedPiper J3L · Sullivan, ILNTSB UNK68X0157Accident · Minor injuriesClosedBeech F35 · Alberta, CanadaNTSB OAK69D0066Accident · No injuriesClosedCessna 337B · Fresno, CANTSB MKC69F0095Accident · Minor injuriesClosedPiper J3C · Austin, MNNTSB FTW69D0099Accident · No injuriesClosedTaylorcraft BC-65 · Broken Arrow, OKNTSB ANC69D0012Accident · No injuriesClosedPiper PA-12 · Soldotna, AKNTSB MKC69A0002Accident · No injuriesClosedPiper PA-32 · Denison, IANTSB MIA69F0071Accident · Minor injuriesClosedCessna 170 · Deland, FLNTSB LAX69D0061Accident · No injuriesClosedBoeing A75N1 · Blythe, CANTSB FTW69F0094Accident · Minor injuriesClosedPiper PA-25 · Amarillo, TXNTSB FTW69D0146Accident · Minor injuriesClosedGrumman G-164 · Brinkley, ARNTSB FTW69D0072Accident · No injuriesClosedPiper PA-18 · Earth, TXNTSB CHI69D0111Accident · No injuriesClosedPiper J3C-65 · Crystal Lake, ILNTSB SEA69D0037Accident · No injuriesClosedPiper PA-25 · Othello, WANTSB NYC69F0088Accident · Serious injuriesClosedScheibe SF27M · Hopewell Tnshp, NJNTSB NYC69F0048Accident · No injuriesClosedCessna 150F · Medford, NJNTSB DIA69F0151Accident · Serious injuriesClosedCessna 182L · Nitro, WVNTSB FTW69F0118Accident · No injuriesClosedPiper PA-25 · Arkadelphia, ARNTSB ANC69D0040Accident · No injuriesClosedBell 47G-2 · Minchumina, AKNTSB UNK68X0154Accident · Serious injuriesClosedPiper PA-32 · Vermilion Bay, CanadaNTSB OAK69D0048Accident · No injuriesClosedCessna 172 · Reno, NVNTSB NYC69F0084Accident · Serious injuriesClosedCessna 177 · Red Bank, NJNTSB NYC69F0061Accident · Minor injuriesClosedCessna 175 · Marlboro, MANTSB FTW69F0106Accident · No injuriesClosedPiper PA-25 · Mccrory, ARNTSB CHI69F0107Accident · Serious injuriesClosedLuscombe 8E · Watervliet, MINTSB NYC69F0035Accident · No injuriesClosedAeronca 7AC · Matamoras, PANTSB FTW69F0105Accident · Minor injuriesClosedPiper PA-25 · N.Little Rock, ARNTSB SEA69A0003Accident · 4 fatalClosedCessna 172H · Stehekin, WANTSB LAX69F0046Accident · 1 fatalClosedPiper PA-25 · Maryvale, AZNTSB FTW69F0045Accident · No injuriesClosedPiper PA-25 · Newellton, LA