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 FTW69A0088Accident · 1 fatalClosedCessna 310 · Somerset, TXNTSB OAK70D0026Accident · No injuriesClosedBoeing E75 · Newell, CANTSB LAX70D0004Accident · No injuriesClosedCessna A188 · Calipatria, CANTSB NYC69I0108Incident · No injuriesClosedBoeing 727-95 · Boston, MANTSB MIA70F0006Accident · Serious injuriesClosedAeronca 11CC · Glen Allan, MSNTSB FTW69D0543Accident · No injuriesClosedPiper PA-25 · Bono, ARNTSB MKC70D0046Accident · No injuriesClosedPiper PA-25 · Dupree, SDNTSB CHI70F0028Accident · Minor injuriesClosedCessna 172 · Detroit, MINTSB NYC69A0107Accident · 3 fatalClosedPiper PA-32 · Greenwich, CTNTSB FTW70F0013Accident · No injuriesClosedSnow S2C · Lake Arthur, LANTSB FTW70D0031Accident · No injuriesClosedPiper PA-18A · Stuttgart, ARNTSB SEA70D0003Accident · No injuriesClosedBell 47G · Ritzville, WANTSB NYC70F0057Accident · Serious injuriesClosedLuscombe 8A · Cross Keys, NJNTSB MIA69D0494Accident · No injuriesClosedPiper PA-28 · Gordo, ALNTSB MIA69A0083Accident · 3 fatalClosedCessna 177 · Chamblee, GANTSB LAX70D0012Accident · No injuriesClosedCessna 150 · S Fernando, CANTSB CHI70F0013Accident · Serious injuriesClosedLuscombe 8E · Beaverton, MINTSB NYC70F0001Accident · Minor injuriesClosedMonocoupe 90 · Taunton, MANTSB MKC69D0476Accident · No injuriesClosedChampion 7ECA · Atlantic, IANTSB ANC70F0008Accident · Serious injuriesClosedPiper PA-22 · Willow, AKNTSB NYC70D0016Accident · Minor injuriesClosedCessna 150G · Coventry, RINTSB FTW70F0047Accident · No injuriesClosedStinson 10A · Mineral Wells, TXNTSB FTW70F0011Accident · No injuriesClosedPiper PA-28 · Dewitt, ARNTSB CHI70F0048Accident · No injuriesClosedCessna 175A · Barberton, OHNTSB NYC70D0018Accident · No injuriesClosedGrumman G-164A · Chatsworth, NJNTSB DIA70F0002Accident · Minor injuriesClosedCessna 172E · Laurel, MDNTSB ANC69A0060Accident · No injuriesClosedCessna 177 · Goodnews Bay, AKNTSB NYC70D0006Accident · Minor injuriesClosedPiper PA-28 · Canastota, NYNTSB CHI69F0656Accident · Serious injuriesClosedAlon A-2 · Troy, MINTSB MKC70D0007Accident · No injuriesClosedCallair A-6 · Bassett, NENTSB FTW70D0076Accident · No injuriesClosedCessna 150 · Yantis, TXNTSB FTW69F0519Accident · No injuriesClosedGrumman G-164A · De Witt, ARNTSB UNK69X0122Accident · Serious injuriesClosedCessna 177 · Boston Bar, CanadaNTSB MKC69D0462Accident · No injuriesClosedCessna 170A · Reva, SDNTSB DEN70D0017Accident · Minor injuriesClosedCessna 172 · Fountain, CONTSB MKC70F0037Accident · No injuriesClosedLuscombe T-8F · Hillsboro, NDNTSB MIA69F0473Accident · Serious injuriesClosedAer-Pegaso M-100S · Boca Raton, FLNTSB SEA70D0044Accident · Minor injuriesClosedAeronca 15AC · Burns, ORNTSB NYC70D0022Accident · No injuriesClosedBeech 23 · Hastings, PANTSB NYC69F0469Accident · No injuriesClosedBeech B23 · Doylestown, PANTSB SEA69D0258Accident · No injuriesClosedFairchild M-62 · Bruneau, IDNTSB CAN69ACAIOAccident · 1 fatalClosedCessna 195 · Flat Lake, CanadaNTSB FTW70D0018Accident · No injuriesClosedBoeing A75A1 · Bay City, TXNTSB DEN70D0015Accident · No injuriesClosedAero Comdr 100 · Farson, WYNTSB FTW69D0548Accident · Minor injuriesClosedCessna 180 · New Iberia, LANTSB ANC70D0006Accident · No injuriesClosedPiper PA-18 · Kotzebue, AKNTSB NYC69F0461Accident · Serious injuriesClosedPiper PA-22 · Sharon, PANTSB FTW69F0524Accident · Serious injuriesClosedCessna 170B · Tulsa, OKNTSB NYC70F0086Accident · Minor injuriesClosedAeronca 7AC · Moorestown, NJNTSB MIA70D0012Accident · No injuriesClosedBell 47H-1 · Belle Glade, FL