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 MIA71FKT07Accident · Serious injuriesClosedAero Comdr 680T · Pompano Beach, FLNTSB LAX71FUQ24Accident · Serious injuriesClosedAmer Avco AA1 · Agua Dulce, CANTSB FTW71FPM09Accident · Serious injuriesClosedBellanca 14-133 · Tulsa, OKNTSB MIA71FLG14Accident · Serious injuriesClosedHelio Acft H-295 · Pelion, SCNTSB ANC71AA022Accident · Serious injuriesClosedCessna 207 · Sitkinak, AKNTSB NYC71AN042Accident · 1 fatalClosedFairchild PT-19B · Ware, MANTSB MIA71DKJ21Accident · No injuriesClosedPiper J-3 · Hazel Green, ALNTSB MIA71AKJ20Accident · Minor injuriesClosedPiper PA-28 · Jasper, ALNTSB FTW71FPJ18Accident · Minor injuriesClosedAero Comdr 680V · Norman, OKNTSB FTW71DRG30Accident · Minor injuriesClosedCessna 172 · Lowake, TXNTSB MIA71FLD20Accident · No injuriesClosedCessna 140 · Eustis, FLNTSB LAX71FUQ21Accident · Serious injuriesClosedGlobe GC-1B · S Fernando, CANTSB LAX71DUQ22Accident · No injuriesClosedCessna 182 · Oxnard, CANTSB FTW71AF014Accident · 4 fatalClosedBeech C35 · Fayetteville, ARNTSB CHI71FBA13Accident · No injuriesClosedPiper PA-28 · Seymour, INNTSB FTW71FRA20Accident · No injuriesClosedMooney M20G · Fresno, TXNTSB ANC71DAG30Accident · No injuriesClosedTaylorcraft L-2 · Gulkana, AKNTSB DEN71FXA10Accident · Minor injuriesClosedCessna 182E · Rock Springs, WYNTSB ANC71DAA18Accident · Minor injuriesClosedPiper PA-18 · Mckinley Prk, AKNTSB SEA71FXM10Accident · Serious injuriesClosedCessna 182B · Sun Valley, IDNTSB FTW71FRA21Accident · No injuriesClosedMooney M20G · Palacios, TXNTSB FTW71FPG23Accident · Minor injuriesClosedPiper PA-25 · Keo, ARNTSB MIA71AM029Accident · 2 fatalClosedBeech V35A · Ft.Lauderdale, FLNTSB DEN71DXD26Accident · Serious injuriesClosedBeech 35-A33 · Salida, CONTSB CHI72DDJ17Accident · Minor injuriesClosedStinson 108-3 · Bruce, WINTSB MKC71FDQ11Accident · Minor injuriesClosedAeronca 7AC · Rapid City, SDNTSB MKC71DDQ12Accident · No injuriesClosedCessna 120 · Harrold, SDNTSB FTW71DQG36Accident · Minor injuriesClosedCessna 150H · Amarillo, TXNTSB CHI71FGD15Accident · Serious injuriesClosedCessna 177 · Painesville, OHNTSB MKC71FDM24Accident · Minor injuriesClosedCessna 150F · Isabella, MNNTSB MIA71DKG39Accident · No injuriesClosedCessna 150F · Warner Robins, GANTSB IAD71II010Incident · No injuriesClosedLear Jet 23 · Washington, DCNTSB FTW71FPD20Accident · No injuriesClosedPiper PA-28 · Saginaw, TXNTSB DEN71AD012Accident · 2 fatalClosedPiper PA-30 · Boulder, CONTSB DCA71IZ002Incident · No injuriesClosedBoeing 747 · San Francisco, CANTSB CHI71FBD22Accident · Serious injuriesClosedCessna 150 · Wheeling, ILNTSB MIA71FKQ11Accident · 1 fatalClosedPiper PA-25 · Georgetown, MSNTSB FTW71FPG21Accident · No injuriesClosedPiper PA-22 · N Little Rock, ARNTSB SEA71AS014Accident · 3 fatalClosedBeech A23A · Medford, ORNTSB NYC71FHJ08Accident · Minor injuriesClosedAlon A2-A · East Hampton, NYNTSB FTW71FRA18Accident · No injuriesClosedCessna 170A · Pearland, TXNTSB DEN71DXT09Accident · No injuriesClosedK.Twister IMP · Coalville, UTNTSB ANC71FAG23Accident · No injuriesClosedMaule M4-210 · Glennallen, AKNTSB ANC71DAG21Accident · No injuriesClosedChampion 7GCB · Anchorage, AKNTSB MIA71AM025Accident · 2 fatalClosedLake LA-4 · Hernando, MSNTSB MIA71FKD07Accident · No injuriesClosedCessna 177 · Savannah, TNNTSB CHI71FDW16Accident · No injuriesClosedBeech 35-B33 · Plymouth, MINTSB MKC71AK013Accident · 1 fatalClosedPiper PA-28 · Ewing, MONTSB CHI71FDJ15Accident · No injuriesClosedCessna 150 · East Troy, WINTSB SEA71DWD16Accident · No injuriesClosedAeronca 15AC · Olympia, WA