NTSB investigations, NH

247 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\" = 'NH' 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 NYC76FNC47Accident · No injuriesClosedCessna 401 · Tilton, NHApr 21, 1976 · N18PS · Substantial damageNTSB NYC76DNC27Accident · Minor injuriesClosedSchweizer SGS134 · Glen, NHOct 22, 1975 · CF-QPT · Substantial damageNTSB NYC76DNC19Accident · No injuriesClosedLuscombe 8E · Bristol, NHSep 10, 1975 · N2399K · Minor damageNTSB NYC76DNC14Accident · Minor injuriesClosedCessna 150 · Claremont, NHAug 31, 1975 · N63073 · DestroyedNTSB NYC76DNC10Accident · No injuriesClosedBeech B19 · Manchester, NHAug 14, 1975 · N1964W · Substantial damageNTSB NYC76DNC02Accident · No injuriesClosedPiper PA-28 · Errol, NHJul 22, 1975 · N7297J · Substantial damageNTSB NYC75DNC39Accident · No injuriesClosedPiper PA-28 · Keene, NHJun 25, 1975 · N7227F · Substantial damageNTSB NYC75DNC29Accident · No injuriesClosedCessna 175 · Milan, NHApr 13, 1975 · N7255M · Substantial damageNTSB NYC74DNC45Accident · No injuriesClosedPitts S-2A · Jaffrey, NHJun 8, 1974 · N388N · 2 aircraft · Substantial damageNTSB NYC74FNC35Accident · No injuriesClosedAmer Avco AA1-B · Manchester, NHJan 15, 1974 · N9998L · Substantial damageNTSB NYC74DNC75Accident · No injuriesClosedPiper PA-18 · Lebanon, NHSep 25, 1973 · N13814 · Substantial damageNTSB NYC74DNC19Accident · No injuriesClosedCessna 182K · Melvin Village, NHAug 27, 1973 · N2619Q · Substantial damageNTSB NYC74FNC10Accident · No injuriesClosedCessna 182N · Claremont, NHJul 17, 1973 · N92330 · Substantial damageNTSB NYC73FNC72Accident · No injuriesClosedAmer Avco AA-1 · Manchester, NHMay 1, 1973 · N6139L · Substantial damageNTSB NYC73DNC68Accident · No injuriesClosedMooney M20E · North Conway, NHApr 7, 1973 · N3241F · Substantial damageNTSB NYC73DNC51Incident · No injuriesClosedBeech A23A · Claremont, NHFeb 4, 1973 · N202GS · Minor damageNTSB UNK73X0105Incident · No injuriesClosedChampion 7GC · Claremont, NHJan 14, 1973 · N4832E · Minor damageNTSB NYC73AN102Accident · Minor injuriesClosedCessna 150 · Nashua, NHJan 14, 1973 · N4239U · 2 aircraft · Substantial damageNTSB NYC73FNC45Accident · No injuriesClosedPiper PA-24 · Keene, NHJan 1, 1973 · N4250 · Substantial damageNTSB NYC73ANO51Accident · No injuriesClosedPiper PA-31 · West Lebanon, NHOct 13, 1972 · N76LE · Substantial damageNTSB NYC73DNC20Accident · No injuriesClosedRepublic RC3 · Melvin Village, NHAug 9, 1972 · N451E · Substantial damageNTSB NYC73DNC08Accident · No injuriesClosedPiper PA-23 · Wolfboro, NHJul 28, 1972 · N5813Y · Substantial damageNTSB NYC73FNC16Accident · No injuriesClosedCessna 150G · Manchester, NHJul 24, 1972 · N5802G · Substantial damageNTSB NYC73DNC01Accident · No injuriesClosedSchweizer 1-26 · Dover, NHJul 2, 1972 · N679U · Substantial damageNTSB NYC72DNC44Accident · No injuriesClosedCessna 182 · Brookline, NHJun 7, 1972 · N71618 · Substantial damageNTSB NYC72DNC50Accident · No injuriesClosedPiper PA-28 · Keene, NHMay 22, 1972 · N6447R · Substantial damageNTSB NYC72DNC48Accident · No injuriesClosedNavion L-17B · Manchester, NHMay 7, 1972 · N5042K · Substantial damageNTSB NYC72DNC35Accident · Minor injuriesClosedPiper PA-28 · Lebanon, NHApr 2, 1972 · N2688 · DestroyedNTSB NYC72DNC27Accident · No injuriesClosedSchweizer 2-33 · Salem, NHJan 30, 1972 · N1236S · Substantial damageNTSB NYC72DNC23Accident · No injuriesClosedPiper PA-23 · Groveton, NHJan 19, 1972 · N874DN · Substantial damageNTSB NYC72DNC22Accident · No injuriesClosedLuscombe 8A · Laconia, NHJan 12, 1972 · N25288 · Substantial damageNTSB NYC72DNC11Accident · No injuriesClosedCessna 150 · Derry, NHOct 16, 1971 · N7118X · Substantial damageNTSB NYC72DNC07Accident · Minor injuriesClosedBellanca 14-19 · Bristol, NHAug 6, 1971 · N8834R · Substantial damageNTSB NYC71FNC25Accident · No injuriesClosedAero Comdr 100 · North Havrhill, NHApr 11, 1971 · N3864X · Substantial damageNTSB NYC71DFG16Accident · No injuriesClosedPiper PA-28 · Laconia, NHMar 17, 1971 · N9394J · Substantial damageNTSB NYC71DFG14Accident · No injuriesClosedPiper PA-24 · Twin Mountain, NHJan 17, 1971 · N5570P · Substantial damageNTSB NYC71DFG08Accident · Minor injuriesClosedCessna 185A · Nashua, NHNov 12, 1970 · N4130Y · Substantial damageNTSB NYC70FFG06Accident · No injuriesClosedCessna 172C · Rochester, NHApr 12, 1970 · N8266X · Substantial damageNTSB NYC70D0068Accident · No injuriesClosedCessna 180 · Wolfeboro, NHJul 16, 1969 · N2304C · Substantial damageNTSB NYC70D0066Accident · No injuriesClosedPiper PA-23 · Wolfeboro, NHJul 14, 1969 · N4486P · Substantial damageNTSB NYC70D0017Accident · No injuriesClosedStinson 108 · Wolfeboro, NHJun 18, 1969 · N97699 · Substantial damageNTSB NYC69F0413Accident · No injuriesClosedPiper PA-28 · Laconia, NHMay 4, 1969 · N9685J · Substantial damageNTSB NYC69D0405Accident · No injuriesClosedBeech A-23 · Manchester, NHApr 28, 1969 · N7984L · Substantial damageNTSB NYC69F0414Accident · No injuriesClosedCessna 195 · Keene, NHApr 25, 1969 · N1063D · Substantial damageNTSB NYC70D0060Accident · No injuriesClosedCessna 150 · Rochester, NHApr 4, 1969 · N7157X · Substantial damageNTSB NYC69F0369Accident · Minor injuriesClosedAero Comdr 520 · Claremont, NHMar 13, 1969 · N4164B · Substantial damageNTSB NYC69D0360Accident · No injuriesClosedCessna 150 · Nashua, NHMar 1, 1969 · N6801F · Substantial damageNTSB NYC69D0321Accident · No injuriesClosedCessna 172 · Rochester, NHFeb 1, 1969 · N9712T · Substantial damageNTSB NYC69D0313Accident · No injuriesClosedPiper PA-28 · Laconia, NHJan 27, 1969 · N6148W · Substantial damageNTSB NYC69D0267Accident · No injuriesClosedAeronca 7DC · Rye, NHNov 24, 1968 · N1113 · Substantial damage