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 LAX66F0052Accident · No injuriesClosedBeechcraft 95-B55 · San Diego, CANTSB FTW66F0184Accident · No injuriesClosedCessna 180 · Keene, TXNTSB MKC66D0102Accident · No injuriesClosedBeechcraft H-35 · Cresco, IANTSB CHI66F0123Accident · No injuriesClosedPiper PA-24 · Benton Harbor, MINTSB ANC65F0012Accident · Serious injuriesClosedPiper JC3 · Ketchikan, AKNTSB NYC66F0155Accident · No injuriesClosedPiper PA-22 · Niagara Falls, NYNTSB LAX66F0024Accident · No injuriesClosedCallair A-9 · Orovada, NVNTSB CHI66F0109Accident · Minor injuriesClosedPiper PA-32 · Elgin, ILNTSB NYC66F0188Accident · No injuriesClosedStinson 108-3 · Amherst, NYNTSB FTW66F0181Accident · No injuriesClosedBeechcraft C-18S · Houston, TXNTSB NYC66F0095Accident · Serious injuriesClosedWaco UPF7 · Manchester, VTNTSB NYC66F0064Accident · Serious injuriesClosedPiper J3C · Baltimore, MDNTSB MKC66F0094Accident · Minor injuriesClosedPiper PA-18 · Fremont, NENTSB MIA66A0010Accident · 8 fatalClosedDehavilland 106A · St.Thomas, VINTSB MIA66A0009Accident · 1 fatalClosedPiper PA-18A · Jacksonville, GANTSB OAK66F0007Accident · Minor injuriesClosedBoeing A75N1 · Yuba City, CANTSB NYC66A0007Accident · 5 fatalClosedBeechcraft C-45G · Parkersburg, WVNTSB MKC66F0158Accident · No injuriesClosedStinson 108-3 · Grand Portage, MNNTSB FTW66F0156Accident · No injuriesClosedLuscombe 8A · Westwego, LANTSB FTW66D0177Accident · No injuriesClosedGruman G-164 · Hunter, ARNTSB NYC66F0108Accident · Serious injuriesClosedAero Cmdr 560 · Danbury, CTNTSB MIA66F0063Accident · Minor injuriesClosedTaylorcraft BC12D · Mineral Sps, NCNTSB ANC66F0032Accident · No injuriesClosedCessna 180B · Anchorage, AKNTSB MKC66F0164Accident · No injuriesClosedPiper PA-25 · Nosseo, MNNTSB MIA66F0086Accident · No injuriesClosedPiper PA-25 · Batesville, MSNTSB MIA66A0006Accident · No injuriesClosedBell 47-D1 · Panama City, FLNTSB CHI66F0095Accident · 1 fatalClosedSmith Mini DSA-1 · Macomb, ILNTSB CHI66A0006Accident · 2 fatalClosedCessna 310 · Detroit, MINTSB MKC66F0112Accident · No injuriesClosedMooney M20C · Cainsville, MONTSB MKC66D0135Accident · Minor injuriesClosedPiper PA-16 · Jadwin, MONTSB MKC66D0128Accident · Minor injuriesClosedPiper PA-22 · Hallsville, MONTSB MIA66F0084Accident · Minor injuriesClosedBeech C-18S · Marathon, FLNTSB CHI66F0080Accident · No injuriesClosedBeechcraft A-23 · Troy, MINTSB CHI66F0166Accident · No injuriesClosedStinson 108 · Lac Duflambeu, WINTSB UNK65X1516Accident · No injuriesClosedPiper PA-22 · Casa Grande, AZNTSB MKC66F0090Accident · Minor injuriesClosedPiper PA-18 · Terre Haute, INNTSB LAX66F0019Accident · No injuriesClosedAeronca 7AC · Grass Valley, CANTSB MKC66F0111Accident · Minor injuriesClosedPiper J3 · Eagleville, MONTSB NYC66D0098Accident · No injuriesClosedPiper J-3 · Redington Pond, MENTSB MKC66D0085Accident · No injuriesClosedBeechcraft 23 · Branson, MONTSB MIA66F0080Accident · Minor injuriesClosedCessna 150 · Tiptonville, TNNTSB CHI66F0044Accident · Serious injuriesClosedCessna 170B · Herndon, KYNTSB CHI66A0004Accident · 1 fatalClosedAeronca 7AC · Pulaski, KYNTSB LAX66D0051Accident · No injuriesClosedWaco UPF-7 · Adelanto, CANTSB CHI66F0364Accident · No injuriesClosedPiper PA-12 · Dallas, WINTSB NYC66F0070Accident · No injuriesClosedPiper PA-22 · Cumberland, MDNTSB MKC66F0070Accident · Minor injuriesClosedChampion 7GCB · Imperial, NENTSB CHI66A0001Accident · 2 fatalClosedTaylorcraft BC-12D · Mineral Point, WINTSB CHI66D0105Accident · No injuriesClosedPiper J-3 · Naperville, CANTSB MIA66A0001Accident · 1 fatalClosedPiper PA-32 · Vero Beach, FL