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.

NTSB NYC78FNE04Accident · No injuriesClosedCessna 177 · Oxford, CTDec 29, 1977 · N2293Y · Substantial damageNTSB NYC77FNE36Accident · No injuriesClosedPiper PA-28 · Simsbury, CTSep 19, 1977 · N5425F · Substantial damageNTSB NYC77FNE28Accident · Serious injuriesClosedHebel B-1 · Killingworth, CTJul 2, 1977 · N1BF · Minor damageNTSB NYC77FNE26Accident · Serious injuriesClosedBeech C23 · East Haddam, CTJun 24, 1977 · N1921L · DestroyedNTSB NYC77FNE23Accident · No injuriesClosedCessna 172M · Plainville, CTJun 19, 1977 · N9574H · Substantial damageNTSB NYC77FNE14Accident · No injuriesClosedMitsubishi MU-2B · Bridgeport, CTApr 6, 1977 · N234BC · Substantial damageNTSB NYC77FNE11Accident · Minor injuriesClosedCessna 150M · Danbury, CTMar 27, 1977 · N3686V · Substantial damageNTSB NYC77FNE09Accident · No injuriesClosedPiper PA-32 · Plainville, CTMar 1, 1977 · N1458T · Substantial damageNTSB NYC77FNE29Accident · No injuriesClosedCessna 150 · North Canaan, CTFeb 7, 1977 · N6930G · Substantial damageNTSB NYC77FNE08Accident · No injuriesClosedTed Smith 601P · Meriden, CTFeb 5, 1977 · N1WC · Substantial damageNTSB NYC77FNE01Accident · Minor injuriesClosedLapointe UP-2 · Williamantic, CTOct 4, 1976 · N76DL · Substantial damageNTSB NYC76FNE32Accident · No injuriesClosedCessna 150 · Ellington, CTAug 16, 1976 · N6272G · Substantial damageNTSB NYC76FNE26Accident · No injuriesClosedCessna 182E · Ellington, CTJun 27, 1976 · N3056Y · Substantial damageNTSB NYC76FNE21Accident · Minor injuriesClosedSchweizer SGS135 · Plymouth, CTMay 28, 1976 · N34189 · Substantial damageNTSB NYC77FNE16Accident · No injuriesClosedPiper PA-28 · Ellington, CTApr 13, 1976 · N6214J · Substantial damageNTSB NYC76FNE06Accident · No injuriesClosedSchweizer SGS233 · Plymouth, CTSep 28, 1975 · N33988 · Substantial damageNTSB NYC76AN002Accident · No injuriesClosedCessna 150 · Marlborough, CTJul 5, 1975 · N3874V · 2 aircraft · Substantial damageNTSB NYC75FNE40Accident · No injuriesClosedChampion 7GCBA · Waterbury, CTJun 23, 1975 · N1880G · Substantial damageNTSB NYC75FNE35Accident · No injuriesClosedPiper PA-28 · Danielson, CTMay 13, 1975 · N95011 · Substantial damageNTSB NYC75FNE33Accident · Minor injuriesClosedBellanca 8KCAB · Simsbury, CTApr 26, 1975 · N9178L · Substantial damageNTSB NYC75FNE32Accident · No injuriesClosedCessna A150L · Simsbury, CTApr 5, 1975 · N6503F · Substantial damageNTSB NYC75FNE29Accident · No injuriesClosedMesserschmt ME109 · Oxford, CTFeb 28, 1975 · N90605 · Substantial damageNTSB NYC75FNE18Accident · No injuriesClosedCessna 172 · Danielson, CTNov 9, 1974 · N46656 · DestroyedNTSB NYC75FNE03Accident · No injuriesClosedBeech 65-80 · Windsr Locks, CTAug 3, 1974 · N1725G · Substantial damageNTSB NYC74FNE01Accident · No injuriesClosedPiper PA-28 · Ellington, CTJul 6, 1974 · N5820U · Substantial damageNTSB NYC75FNE27Accident · Serious injuriesClosedCessna 210 · Meriden, CTJun 5, 1974 · N1175Q · Substantial damageNTSB NYC74FNE19Accident · No injuriesClosedAero Comdr 100 · W Goshen, CTApr 21, 1974 · N4138X · Substantial damageNTSB NYC74FNE17Accident · No injuriesClosedCessna 140 · Harwinton, CTApr 17, 1974 · N76131 · Substantial damageNTSB NYC74IN054Incident · No injuriesClosedBeech E18S · Windsor Lock, CTDec 19, 1973 · N111EH · Minor damageNTSB NYC74FNE13Accident · No injuriesClosedCessna 150L · Chester, CTDec 1, 1973 · N18130 · Substantial damageNTSB NYC74FNE11Accident · No injuriesClosedCessna 150L · Oxford, CTNov 10, 1973 · N6861G · Substantial damageNTSB NYC74FNE10Accident · No injuriesClosedCessna 140 · Simsbury, CTNov 2, 1973 · N4220N · Substantial damageNTSB NYC74AN039Accident · No injuriesClosedPiper PA-28 · Hartford, CTOct 31, 1973 · N2133T · 2 aircraft · Minor damageNTSB NYC74FNE09Accident · No injuriesClosedBeech E55 · New Haven, CTOct 21, 1973 · N4EN · Substantial damageNTSB NYC74FNE05Accident · No injuriesClosedTemco GC-1B · Hartford, CTSep 15, 1973 · N2339B · Substantial damageNTSB NYC74IN023Incident · No injuriesClosedBeech 95-C55 · Windsor Lock, CTSep 14, 1973 · N5943S · Minor damageNTSB NYC74FNE01Accident · No injuriesClosedBoeing PT-13D · Ellington, CTJul 24, 1973 · N1300V · Substantial damageNTSB NYC73FNE37Accident · No injuriesClosedCessna 150 · Hartford, CTJun 28, 1973 · N6613G · Substantial damageNTSB NYC73DNE28Accident · No injuriesClosedDavis D-1K · Chester, CTApr 14, 1973 · N158Y · Substantial damageNTSB NYC73FNE27Accident · No injuriesClosedPiper PA-28 · Danielson, CTMar 28, 1973 · N95011 · Substantial damageNTSB NYC73FNE22Accident · No injuriesClosedPiper PA-34 · Danbury, CTMar 10, 1973 · N4886T · Substantial damageNTSB NYC73FNE10Accident · Serious injuriesClosedGage B8MW · Milford, CTSep 1, 1972 · N4789 · Substantial damageNTSB NYC73DNE06Accident · No injuriesClosedCessna 182P · Bridgeport, CTAug 11, 1972 · N7341Q · Substantial damageNTSB NYC73FNE04Accident · No injuriesClosedCessna 140 · Waterford, CTJul 27, 1972 · N3572V · Substantial damageNTSB NYC72FNE38Accident · No injuriesClosedBeech M35 · Danielson, CTJun 27, 1972 · N329Z · Substantial damageNTSB NYC72FNE37Accident · No injuriesClosedCessna 310F · Windsor Lock, CTJun 2, 1972 · N6726X · Substantial damageNTSB NYC72FNE31Accident · No injuriesClosedCessna 172C · Simsbury, CTMar 18, 1972 · N1501Y · Substantial damageNTSB NYC72AN110Accident · No injuriesClosedConvair 580 · Windsor Lcks, CTMar 11, 1972 · N5803 · Substantial damageNTSB NYC72FNE29Accident · No injuriesClosedPiper PA-28 · Hartford, CTFeb 29, 1972 · N1806J · Substantial damageNTSB NYC72FNE26Accident · No injuriesClosedBeech 100 · E.Hartford, CTFeb 23, 1972 · N101UA · Substantial damage