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 DEN67F0062Accident · No injuriesClosedCessna 188 · Center, CONTSB MIA67F0006Accident · Minor injuriesClosedPiper PA12 · China Grove, NCNTSB MKC67F0019Accident · No injuriesClosedPiper PA-18 · Carrington, NDNTSB DEN67D0063Accident · Minor injuriesClosedPiper PA-22 · Conifer, CONTSB MKC66D0586Accident · No injuriesClosedBoeing A75 · Berryton, KSNTSB NYC66A0148Accident · 2 fatalClosedPiper PA-24 · Shirley, NYNTSB NYC66A0147Accident · 1 fatalClosedMooney M-18C · Slate Run, PANTSB MIA67F0095Accident · Serious injuriesClosedStinson 108-2 · Atlanta, GANTSB FTW67F0013Accident · No injuriesClosedCessna 172 · Hackberry, LANTSB CHI67F0032Accident · No injuriesClosedAeronca 11AC · Kalamo, MINTSB OAK66A0056Accident · 2 fatalClosedCessna 172 · Seigler Sprgs, CANTSB DEN66D0309Accident · No injuriesClosedCessna 182 · Santa Rosa, NMNTSB MKC67D0013Accident · No injuriesClosedPiper PA-22 · Manson, IANTSB MIA66D0613Accident · No injuriesClosedAeronca 7ECA · Manati, PRNTSB LAX66A0097Accident · Minor injuriesClosedBell 47G3B1 · Glendora, CANTSB FTW67D0034Accident · Minor injuriesClosedBoeing E75N1 · Nome, TXNTSB FTW67D0012Accident · No injuriesClosedPiper PA-18 · Honey Grove, TXNTSB CHI67F0017Accident · Serious injuriesClosedPiper PA-24 · Chetek, WINTSB MIA67F0158Accident · Serious injuriesClosedCessna 172 · Palatka, FLNTSB LAX66F0380Accident · Serious injuriesClosedCessna 172G · Fullerton, CANTSB CHI67F0027Accident · No injuriesClosedMooney M20 · Jackson, MINTSB CHI67F0013Accident · Serious injuriesClosedBeech B35 · Indianapolis, INNTSB NYC67F0067Accident · No injuriesClosedAmer Eagle 231 · East Amherst, NYNTSB MIA66F0595Accident · Minor injuriesClosedMooney MK20C · Pascagoula, MSNTSB CHI66F0793Accident · No injuriesClosedPiper PA-23 · Madison, INNTSB NYC66F0678Accident · No injuriesClosedCessna 205 · E.Stroudsburg, PANTSB NYC66A0138Accident · 1 fatalClosedCessna 150F · Fairfield, NJNTSB MIA66F0562Accident · Minor injuriesClosedPiper PA-22 · Alexis, NCNTSB DEN66D0355Accident · No injuriesClosedCessna 150 · Douglas, AZNTSB SEA67F0060Accident · Serious injuriesClosedChampion 7GCBC · Prineville, ORNTSB MKC67D0045Accident · No injuriesClosedMooney M20C · Wellington, KSNTSB LAX66D0368Accident · No injuriesClosedCessna 172 · Apple Valley, CANTSB FTW66A0110Accident · 1 fatalClosedBeech 35 · Pt Mansfield, TXNTSB CHI66F0786Accident · Minor injuriesClosedCessna 182 · Antioch, ILNTSB SEA67D0011Accident · Minor injuriesClosedCessna 180A · Albion, IDNTSB OAK66F0285Accident · Serious injuriesClosedPiper PA-24 · Elk Grove, CANTSB CHI67D0356Accident · Minor injuriesClosedPiper PA-20 · No.Jackson, OHNTSB CHI66F0767Accident · No injuriesClosedPiper PA-22 · Napoleon, MINTSB ANC67D0003Accident · No injuriesClosedAeronca 7GC · Anchorage, AKNTSB MIA66F0567Accident · No injuriesClosedBeech A45 · Ocean Sprngs, MSNTSB FTW66F0681Accident · No injuriesClosedPiper PA-18 · Stuttgart, ARNTSB MKC67F0005Accident · Minor injuriesClosedPiper PA-28 · Wichita, KSNTSB LAX66A0091Accident · No injuriesClosedGrumman G-21 · Catalina Is, CANTSB DEN66F0343Accident · Serious injuriesClosedPiper PA-28 · Albuquerque, NMNTSB CHI66A0091Accident · 1 fatalClosedBeech S35 · Monticello, ILNTSB UNK66X0142Accident · Minor injuriesClosedCessna 180 · Prince Albert, CanadaNTSB ANC66A0045Accident · 2 fatalClosedCessna 172F · Galena, AKNTSB SEA66D0288Accident · No injuriesClosedBellanca 14-13 · Boulder, MTNTSB OAK66F0307Accident · 1 fatalClosedBeech SNB-5 · Lihue, HINTSB SEA67D0061Accident · No injuriesClosedPiper PA-22 · Eugene, OR