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 FTW71AF081Accident · Serious injuriesClosedBell 206A · Gulf Of Mexico (Gulf of Mexico)NTSB OAK71DVG87Accident · Serious injuriesClosedGrumman G164 · Robbins, CANTSB FTW71FRD51Accident · No injuriesClosedBoeing B75N1 · Elton, LANTSB NYC71DNC31Accident · No injuriesClosedAeronca 7DC · Berwick, MENTSB FTW71DQG74Accident · No injuriesClosedCessna A188A · Amarillo, TXNTSB MIA71AM107Accident · 2 fatalClosedLuscombe 8A · Odessa, FLNTSB LAX71FVA81Accident · Minor injuriesClosedPiper PA-24 · Glenville, CANTSB FTW71FPM30Accident · No injuriesClosedPiper PA-20 · Wagoner, OKNTSB MKC71FER68Accident · No injuriesClosedPiper PA-28S · Roseville, MNNTSB NYC71FGM51Accident · Serious injuriesClosedPiper PA-28R · Bedford, PANTSB MKC71FCA39Accident · No injuriesClosedCessna U206 · Cape Girardeau, MONTSB SEA71FTA22Accident · No injuriesClosedBeech A23-19 · Terry, MTNTSB MIA71FKH02Accident · No injuriesClosedCessna 182B · Warm Springs, GANTSB FTW71FRD49Accident · No injuriesClosedCessna A185E · New Orleans, LANTSB MKC71FER66Accident · Serious injuriesClosedBeech E35 · Park Rapids, MNNTSB MIA71FKQ36Accident · No injuriesClosedStearman A75 · Cleveland, MSNTSB LAX71FUQ83Accident · Serious injuriesClosedBeech 95-C55 · Van Nuys, CANTSB IAD71DKM36Accident · Minor injuriesClosedTaylorcraft BC12-D · Ramsuer, NCNTSB CHI71FEM81Accident · No injuriesClosedCessna 120 · Holland, MINTSB CHI71DEE79Accident · No injuriesClosedAmer Avco AA1 · Pecatonica, ILNTSB NYC71FHA53Accident · Serious injuriesClosedCessna 180 · Tamiment, PANTSB FTW71FPG59Accident · Minor injuriesClosedBellanca 7ACA · Marion, ARNTSB MKC71FTG29Accident · No injuriesClosedPiper PA-25 · Fairmount, NDNTSB SEA71FYE32Accident · No injuriesClosedNavion A · Silverdale, WANTSB FTW71FPD59Accident · No injuriesClosedCessna 150F · Fort Worth, TXNTSB OAK71DVA84Accident · Minor injuriesClosedCessna 210 · Tulare, CANTSB MIA71IM102Incident · No injuriesClosedBoeing 727 · Miami, FLNTSB MIA71DKA13Accident · No injuriesClosedCessna 150J · Ponce, PRNTSB DEN71DTC06Accident · No injuriesClosedPiper PA-11 · Aladdin, WYNTSB LAX71FUG85Accident · 1 fatalClosedBede BD4 · Chino, CANTSB NYC71FFJ33Accident · No injuriesClosedStinson 108-1 · Skaneateles, NYNTSB MKC71FCJ64Accident · No injuriesClosedSnow 600S2C · Falls City, NENTSB SEA71FTA21Accident · No injuriesClosedCessna 150J · Sidney, MTNTSB MKC71DTG27Accident · No injuriesClosedPiper PA-25 · Mountain, NDNTSB MKC71DCQ40Accident · No injuriesClosedMooney M20F · Montezuma, KSNTSB FTW71FQD22Accident · No injuriesClosedAvro 19 · El Paso, TXNTSB LAX71FUJ93Accident · Minor injuriesClosedBeech 23 · Yuma, AZNTSB ANC71DAG86Accident · No injuriesClosedCessna 170A · Anchorage, AKNTSB IAD71FIA21Accident · Minor injuriesClosedCessna 150 · Lusby, MDNTSB FTW71FRA55Accident · Minor injuriesClosedCessna 170B · Angleton, TXNTSB FTW71DRD46Accident · No injuriesClosedBoeing A75 · Vinton, LANTSB CHI71FEV44Accident · Serious injuriesClosedAeronca 65-TAL · Abingdon, ILNTSB MKC71DCG70Accident · No injuriesClosedAeronca 7AC · Huntsville, MONTSB FTW71DPJ42Accident · No injuriesClosedPiper PA-17 · Hammon, OKNTSB ANC71AA058Accident · No injuriesClosedHiller FH1100 · Port Heiden, AKNTSB MKC71FCJ62Accident · No injuriesClosedAero Comdr A-9 · St Libory, NENTSB FTW71FRA56Accident · No injuriesClosedBeech J35 · Spring Branch, TXNTSB DEN71FTM26Accident · Serious injuriesClosedBeech 35 · Provo, UTNTSB DEN71FTM25Accident · Serious injuriesClosedGlobe GC-1B · Spanish Fork, UTNTSB DCA71I0004Incident · No injuriesClosedBoeing B747 · Honolulu, HI