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 OAK69D0306Accident · Serious injuriesClosedCessna 182M · Livermore, CANTSB FTW69F0464Accident · No injuriesClosedGrumman G-164A · Oberlin, LANTSB DIA69I0057Incident · No injuriesClosedBoeing 707 · Baltimore, MDNTSB OAK69D0307Accident · No injuriesClosedCallair A-9 · Dixon, CANTSB MKC69F0438Accident · Serious injuriesClosedPiper PA-22 · Richland, MONTSB OAK69D0310Accident · No injuriesClosedGlobe GC-1B · Redding, CANTSB MIA70F0027Accident · Serious injuriesClosedAeronca 65-TC · Cartersville, GANTSB MIA69F0474Accident · Serious injuriesClosedPiper PA-28 · Pelzer, SCNTSB CHI69F0662Accident · Minor injuriesClosedMooney M20C · Northbrook, ILNTSB MIA69F0470Accident · No injuriesClosedHiller UH-12C · Hato Rey, PRNTSB ANC69A0046Accident · 9 fatalClosedDehavilland DHC-2 · Craig, AKNTSB FTW69F0493Accident · No injuriesClosedPiper PA-28 · Okla.City, OKNTSB SEA69D0241Accident · Minor injuriesClosedKaman H-43A · Castle Rock, WANTSB MIA69A0068Accident · 1 fatalClosedCessna 172D · Summerton, SCNTSB FTW69A0074Accident · 6 fatalClosedCessna 310N · Huntsville, TXNTSB NYC69F0428Accident · Minor injuriesClosedPiper PA-22 · Titusville, PANTSB MIA69A0067Accident · 1 fatalClosedPiper PA-22 · Meadville, MSNTSB FTW70F0014Accident · No injuriesClosedPiper PA-18A · Bloomington, TXNTSB CHI69D0618Accident · No injuriesClosedPiper PA-24 · Alliance, OHNTSB SEA69D0230Accident · Serious injuriesClosedCessna 188 · Genesee, IDNTSB SEA69F0249Accident · 1 fatalClosedPiper PA-25 · Geraldine, MTNTSB SEA69D0205Accident · No injuriesClosedCessna 140 · Midvale, IDNTSB OAK69D0301Accident · No injuriesClosedPiper PA-28 · Santa Rosa, CANTSB OAK69D0270Accident · No injuriesClosedCessna 150 · Lanai City, HINTSB FTW69D0482Accident · Minor injuriesClosedAero Comdr S-2R · Garwood, TXNTSB SEA69D0219Accident · No injuriesClosedCessna 172C · Mccall, IDNTSB NYC69A0092Accident · 1 fatalClosedAeronca 65-C · Phelps, NYNTSB FTW69A0071Accident · 3 fatalClosedPiper PA-24 · Wolfe City, TXNTSB LAX69F0310Accident · No injuriesClosedBeech F35 · Burbank, CANTSB CHI70D0071Accident · No injuriesClosedCessna 150F · Burlington, WINTSB SEA69F0251Accident · Serious injuriesClosedMooney M20F · Melville, MTNTSB FTW69F0448Accident · Minor injuriesClosedStinson 108-1 · Pearland, TXNTSB FTW69A0068Accident · 1 fatalClosedRhein 3A-P75 · Morrilton, ARNTSB CHI69F0575Accident · No injuriesClosedMooney M20C · Joliet, ILNTSB SEA69D0223Accident · No injuriesClosedStinson L-5 · Heppner, ORNTSB CHI69F0563Accident · No injuriesClosedCessna 150 · Rockford, ILNTSB ANC69D0130Accident · No injuriesClosedBensen B-8M · Eagle River, AKNTSB FTW69F0436Accident · No injuriesClosedPiper PA-28 · Baton Rouge, LANTSB MKC69F0401Accident · Serious injuriesClosedCessna 177 · Jennings, KSNTSB LAX69D0330Accident · No injuriesClosedHughes 269B · Grnd Canyon, AZNTSB DIA69F0170Accident · Minor injuriesClosedPiper PA-28 · Bower, MDNTSB MKC69D0388Accident · No injuriesClosedPiper PA-25 · Colby, KSNTSB MKC69D0381Accident · No injuriesClosedCessna 150 · Odessa, MONTSB LAX69A0056Accident · 1 fatalClosedCessna 182A · Chino, CANTSB CHI69D0582Accident · No injuriesClosedCessna 150 · New Market, OHNTSB ANC69D0125Accident · No injuriesClosedAeronca 15AC · Bettles, AKNTSB MKC69D0377Accident · No injuriesClosedCessna 337 · Council Grove, KSNTSB MIA69A0058Accident · No injuriesClosedAero Comdr 560-F · Greenwood, MSNTSB LAX69F0301Accident · No injuriesClosedAmer Avco AA-1 · La Verne, CANTSB MKC69F0395Accident · No injuriesClosedCessna 310F · Columbus, NE