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 LAX72FUG69Accident · Serious injuriesClosedCessna 172E · Hesperia, CANTSB FTW72FPA21Accident · No injuriesClosedCessna 310D · Addison, TXNTSB LAX72FUM23Accident · Serious injuriesClosedLuscombe 8E · El Cajon, CANTSB MKC72DCQ49Accident · No injuriesClosedCessna 172 · Norton, KSNTSB CHI72FEE73Accident · No injuriesClosedMooney M-20C · Northbrook, ILNTSB MIA72IM109Incident · No injuriesClosedBoeing 727 · Jacksonville, FLNTSB FTW72DRG78Accident · Minor injuriesClosedCessna 172 · S.Luis Potosi, MexicoNTSB CHI72DEE71Accident · No injuriesClosedMGT Mustang MM1 · Lockport, ILNTSB NYC72FGM39Accident · No injuriesClosedCessna 150H · Butler, PANTSB MIA72DKG92Accident · No injuriesClosedTaylorcraft BC12-D · Lake Park, GANTSB NYC72AN121Accident · 3 fatalClosedBeech E18S · Skaneateles, NYNTSB MKC72DCD66Accident · No injuriesClosedCessna 170 · Paullina, IANTSB LAX72FUG66Accident · Minor injuriesClosedPiper PA-18 · Elsinore, CANTSB MIA72AM106Accident · 1 fatalClosedBeech 95 · Miami, FLNTSB DEN72FTE65Accident · No injuriesClosedPiper PA-25 · Merino, CONTSB SEA72AS044Accident · 1 fatalClosedCessna 170A · Medford, ORNTSB MIA72FLA38Accident · Serious injuriesClosedPiper PA-25 · Palatka, FLNTSB CHI72FEC34Accident · Minor injuriesClosedCessna 170B · Troy, MINTSB LAX72FUQ72Accident · Serious injuriesClosedFunk B85C · Pacoima, CANTSB LAX72FUJ89Accident · Minor injuriesClosedCessna 150C · Chandler, AZNTSB FTW72FRA49Accident · Minor injuriesClosedPiper PA-28 · Kennard, TXNTSB DEN72DTM14Accident · No injuriesClosedTemco 11A · Modena, UTNTSB MIA72DKG85Accident · No injuriesClosedGrumman G-164 · Dublin, GANTSB NYC72FGM38Accident · No injuriesClosedCessna 182 · Sharon, PANTSB NYC72AN119Accident · 1 fatalClosedPiper PA-28 · Laytonsville, MDNTSB NYC72DHM37Accident · No injuriesClosedBeech A23A · Rhinebeck, NYNTSB NYC72AN118Accident · 2 fatalClosedPiper PA-22 · Larrysville, PANTSB ANC72DAH04Accident · No injuriesClosedPiper PA-18A · Glenallen, AKNTSB OAK72DXQ25Accident · Minor injuriesClosedCessna 172 · Wells, NVNTSB MKC72FCD64Accident · No injuriesClosedLuscombe 8E · Adel, IANTSB MKC72DER50Accident · No injuriesClosedCessna 170B · Lake Elmo, MNNTSB MKC72DER46Accident · No injuriesClosedAmer Avco AA1 · Stanton, MNNTSB LAX72FUG57Accident · No injuriesClosedCessna 120 · Elsinore, CANTSB SEA72DTA14Accident · Minor injuriesClosedPiper PA-25 · Brusett, MTNTSB MIA72IM095Incident · No injuriesClosedPiper J3-S · St Petersburg, FLNTSB NYC72DKM26Accident · Serious injuriesClosedJeanies Teenie · Marion, NCNTSB ANC72FAA25Accident · 1 fatalClosedCessna 180H · Delta Junctn, AKNTSB NYC72FID22Accident · No injuriesClosedCessna 150K · Hopewell, VANTSB MIA72DLD55Accident · Minor injuriesClosedCessna 177 · Lake Wales, FLNTSB MIA72DKQ32Accident · No injuriesClosedCallair A-9 · Avon, MSNTSB CHI72FCA02Accident · Serious injuriesClosedBeech H35 · East St.Louis, ILNTSB SEA72FYC30Accident · Minor injuriesClosedPiper PA-18 · North Plains, ORNTSB SEA72DYG30Accident · No injuriesClosedSnow 600S2R · Sunnyside, WANTSB NYC72DKM25Accident · No injuriesClosedPiper PA-28 · Brevard, NCNTSB NYC72FNA37Accident · Serious injuriesClosedPiper PA-24 · Marshfield, MANTSB NYC72FHJ17Accident · No injuriesClosedAeronca 7AC · Amityville, NYNTSB LAX72DUJ86Accident · No injuriesClosedPiper J3C-65 · Scottsdale, AZNTSB SEA72AS037Accident · 1 fatalClosedPiper PA-32 · Tacoma, WANTSB LAX72FUJ81Accident · Serious injuriesClosedAeronca 7AC · Tucson, AZNTSB CHI72FEE60Accident · No injuriesClosedCessna 140 · Wheeling, IL