NTSB investigations, CT
394 cases where the phase of flight was Landing.
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.\"eventStateOrRegion\" = 'CT' AND c.\"eventCountry\" = 'USA'
AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 550)
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."eventStateOrRegion" = 'CT' AND c."eventCountry" = 'USA'
AND c."mKey" IN (SELECT case_mkey FROM case_phases WHERE main = 550)
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 NYC78FNE04Accident · No injuriesClosedCessna 177 · Oxford, CTNTSB NYC77FNE36Accident · No injuriesClosedPiper PA-28 · Simsbury, CTNTSB NYC77FNE28Accident · Serious injuriesClosedHebel B-1 · Killingworth, CTNTSB NYC77FNE26Accident · Serious injuriesClosedBeech C23 · East Haddam, CTNTSB NYC77FNE23Accident · No injuriesClosedCessna 172M · Plainville, CTNTSB NYC77FNE14Accident · No injuriesClosedMitsubishi MU-2B · Bridgeport, CTNTSB NYC77FNE11Accident · Minor injuriesClosedCessna 150M · Danbury, CTNTSB NYC77FNE09Accident · No injuriesClosedPiper PA-32 · Plainville, CTNTSB NYC77FNE29Accident · No injuriesClosedCessna 150 · North Canaan, CTNTSB NYC77FNE08Accident · No injuriesClosedTed Smith 601P · Meriden, CTNTSB NYC77FNE01Accident · Minor injuriesClosedLapointe UP-2 · Williamantic, CTNTSB NYC76FNE32Accident · No injuriesClosedCessna 150 · Ellington, CTNTSB NYC76FNE26Accident · No injuriesClosedCessna 182E · Ellington, CTNTSB NYC76FNE21Accident · Minor injuriesClosedSchweizer SGS135 · Plymouth, CTNTSB NYC77FNE16Accident · No injuriesClosedPiper PA-28 · Ellington, CTNTSB NYC76FNE06Accident · No injuriesClosedSchweizer SGS233 · Plymouth, CTNTSB NYC76AN002Accident · No injuriesClosedCessna 150 · Marlborough, CTNTSB NYC75FNE40Accident · No injuriesClosedChampion 7GCBA · Waterbury, CTNTSB NYC75FNE35Accident · No injuriesClosedPiper PA-28 · Danielson, CTNTSB NYC75FNE33Accident · Minor injuriesClosedBellanca 8KCAB · Simsbury, CTNTSB NYC75FNE32Accident · No injuriesClosedCessna A150L · Simsbury, CTNTSB NYC75FNE29Accident · No injuriesClosedMesserschmt ME109 · Oxford, CTNTSB NYC75FNE18Accident · No injuriesClosedCessna 172 · Danielson, CTNTSB NYC75FNE03Accident · No injuriesClosedBeech 65-80 · Windsr Locks, CTNTSB NYC74FNE01Accident · No injuriesClosedPiper PA-28 · Ellington, CTNTSB NYC75FNE27Accident · Serious injuriesClosedCessna 210 · Meriden, CTNTSB NYC74FNE19Accident · No injuriesClosedAero Comdr 100 · W Goshen, CTNTSB NYC74FNE17Accident · No injuriesClosedCessna 140 · Harwinton, CTNTSB NYC74IN054Incident · No injuriesClosedBeech E18S · Windsor Lock, CTNTSB NYC74FNE13Accident · No injuriesClosedCessna 150L · Chester, CTNTSB NYC74FNE11Accident · No injuriesClosedCessna 150L · Oxford, CTNTSB NYC74FNE10Accident · No injuriesClosedCessna 140 · Simsbury, CTNTSB NYC74AN039Accident · No injuriesClosedPiper PA-28 · Hartford, CTNTSB NYC74FNE09Accident · No injuriesClosedBeech E55 · New Haven, CTNTSB NYC74FNE05Accident · No injuriesClosedTemco GC-1B · Hartford, CTNTSB NYC74IN023Incident · No injuriesClosedBeech 95-C55 · Windsor Lock, CTNTSB NYC74FNE01Accident · No injuriesClosedBoeing PT-13D · Ellington, CTNTSB NYC73FNE37Accident · No injuriesClosedCessna 150 · Hartford, CTNTSB NYC73DNE28Accident · No injuriesClosedDavis D-1K · Chester, CTNTSB NYC73FNE27Accident · No injuriesClosedPiper PA-28 · Danielson, CTNTSB NYC73FNE22Accident · No injuriesClosedPiper PA-34 · Danbury, CTNTSB NYC73FNE10Accident · Serious injuriesClosedGage B8MW · Milford, CTNTSB NYC73DNE06Accident · No injuriesClosedCessna 182P · Bridgeport, CTNTSB NYC73FNE04Accident · No injuriesClosedCessna 140 · Waterford, CTNTSB NYC72FNE38Accident · No injuriesClosedBeech M35 · Danielson, CTNTSB NYC72FNE37Accident · No injuriesClosedCessna 310F · Windsor Lock, CTNTSB NYC72FNE31Accident · No injuriesClosedCessna 172C · Simsbury, CTNTSB NYC72AN110Accident · No injuriesClosedConvair 580 · Windsor Lcks, CTNTSB NYC72FNE29Accident · No injuriesClosedPiper PA-28 · Hartford, CTNTSB NYC72FNE26Accident · No injuriesClosedBeech 100 · E.Hartford, CT