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 UNK64X2641Accident · No injuriesClosedPiper PA22 · Faribault, MNNTSB UNK64X2638Accident · No injuriesClosedAeronca 7AC · Moore Haven, FLNTSB UNK64X2618Accident · Minor injuriesClosedPiper J3 · Deer Park, NYNTSB UNK64X2612Accident · Minor injuriesClosedTaylorcraft BC12D · Gaston, ORNTSB UNK64X2582Accident · Minor injuriesClosedBenson B8M · Valley City, NDNTSB UNK64X2556Accident · No injuriesClosedAero Cmdr 520 · Pembroke, CanadaNTSB UNK64X2555Accident · No injuriesClosedChampion 7AC · Excelsior Spgs, MONTSB UNK64X2553Accident · No injuriesClosedPiper PA18 · Atlanta, GANTSB UNK64X2549Accident · No injuriesClosedLuscombe 8A · Cushing, WINTSB UNK64X2546Accident · No injuriesClosedPiper PA12 · Mound, MNNTSB UNK64X2543Accident · Minor injuriesClosedStinson 10 · Havasu City, AZNTSB UNK64X2541Accident · No injuriesClosedPiper PA16 · Boulevard, CANTSB UNK64X2529Accident · Serious injuriesClosedCessna 182B · Wilmington, NCNTSB UNK64X2511Accident · No injuriesClosedCessna 172B · Menachemeadow, CANTSB UNK64X2509Accident · No injuriesClosedBoeing A75 · Arvin, CANTSB UNK64X2490Accident · No injuriesClosedStearman YIS · Dunlap, INNTSB UNK64X2473Accident · Serious injuriesClosedPiper J3 · Harlingen, TXNTSB UNK64X2469Accident · No injuriesClosedPiper J3C · Fairwell Lke, AKNTSB UNK64X2465Accident · Minor injuriesClosedCessna 172 · Freemason Is, LANTSB UNK64X2429Accident · Serious injuriesClosedStinson L-5 · Newcastle, VANTSB UNK64X2425Accident · No injuriesClosedCessna 195 · Huntsville, ARNTSB UNK64X2418Accident · Minor injuriesClosedCessna 172 · Ansonia, OHNTSB UNK64X2413Accident · No injuriesClosedPiper PA22 · Ft Meade, MDNTSB UNK64X2407Accident · 2 fatalClosedBeech SNB-5 · Ft Lauderdale, FLNTSB UNK64X2398Accident · No injuriesClosedPiper PA18 · Sleepy Eye, MNNTSB UNK64X2377Accident · No injuriesClosedMooney MK20 · Wolfe City, TXNTSB UNK64X2371Accident · No injuriesClosedCessna 140 · Santanta, KSNTSB UNK64X2370Accident · Minor injuriesClosedLuscombe 8A · Lancaster, CANTSB UNK64X2356Accident · No injuriesClosedPiper PA23 · Lake Tahoe, CANTSB UNK64X2353Accident · Minor injuriesClosedPiper PA22 · New Alexander, PANTSB MKC66D0051Accident · No injuriesClosedCessna 180F · Petersburg, NDNTSB UNK64X2346Accident · Serious injuriesClosedCessna 140 · Ft Lauderdale, FLNTSB UNK64X2344Accident · 3 fatalClosedBeechcraft A35 · Rawlins, WYNTSB UNK64X2329Accident · Minor injuriesClosedGlobe Swift GC1B · Texarkana, ARNTSB UNK64X2298Accident · No injuriesClosedPiper PA25 · Spearman, TXNTSB UNK64X2294Accident · No injuriesClosedHiller 12E · Jacob Lake, AZNTSB UNK64X2287Accident · No injuriesClosedPiper PA28 · Burwell, NENTSB UNK64X2278Accident · No injuriesClosedPiper PA22 · Ridgeland, SCNTSB UNK64X2270Accident · 1 fatalClosedMooney M18C · Grants, NMNTSB UNK64X2257Accident · Minor injuriesClosedCessna 150 · Akkansas Cty, KSNTSB UNK64X2249Accident · No injuriesClosedBeechcraft 35 · Piedmont, OKNTSB UNK64X2225Accident · Minor injuriesClosedTribby 1 · San Antonio, TXNTSB UNK64X2224Accident · No injuriesClosedTaylorcraft BL1265 · Rawlings, WYNTSB UNK64X2218Accident · No injuriesClosedPiper PA22 · Rockville, MDNTSB UNK64X2216Accident · Minor injuriesClosedTaylorcraft BC-12D · Slidell, LANTSB UNK64X2215Accident · No injuriesClosedPiper PA18 · Karinen, SDNTSB UNK64X2210Accident · No injuriesClosedBell 47G3 · Yack Flats, MTNTSB UNK64X2202Accident · No injuriesClosedPiper PA24 · Dimmitt, TXNTSB UNK64X2198Accident · No injuriesClosedMooney M20E · Richton Park, ILNTSB UNK64X2195Accident · 1 fatalClosedAero Cmdr 680F · Del Cabo Baja, Mexico