NTSB investigations, ME

87 cases where the phase of flight was Takeoff.

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\" = 'ME' AND c.\"eventCountry\" = 'USA'
    AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 300)
  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 NYC77DNC15Accident · No injuriesClosedPiper PA-28 · Bar Harbor, MEJan 2, 1977 · N8388C · Substantial damageNTSB NYC77IA025Incident · No injuriesClosedBoeing 727 · Bangor, MEDec 27, 1976 · N473DA · Minor damageNTSB NYC77DNC03Accident · No injuriesClosedPiper PA-24 · Bangor, MEOct 22, 1976 · CF-NVO · Substantial damageNTSB NYC76FNC39Accident · No injuriesClosedCessna 120 · Wiscasset, MEApr 14, 1976 · N89925 · Substantial damageNTSB NYC76DNC26Accident · No injuriesClosedPiper PA-18 · Brooklin, MEOct 19, 1975 · N7068D · Substantial damageNTSB NYC76DNC08Accident · Minor injuriesClosedStinson 108 · Bangor, MEAug 10, 1975 · N9368K · Substantial damageNTSB NYC75FNC35Accident · Minor injuriesClosedGrumman TBM-3E · Millinocket, MEMay 29, 1975 · N4168A · DestroyedNTSB NYC75DNC11Accident · Minor injuriesClosedErco 415-D · Cambridge, MESep 29, 1974 · N1084 · Substantial damageNTSB NYC75FNC05Accident · Minor injuriesClosedPiper PA-16 · Bowdoinham, MEJul 16, 1974 · N5260H · Substantial damageNTSB NYC74AN011Accident · No injuriesClosedCessna 402 · Bar Harbor, MEAug 2, 1973 · N4526Q · Substantial damageNTSB NYC74FNC06Accident · No injuriesClosedLuscombe 8F · Trenton, MEJul 14, 1973 · N9903C · Substantial damageNTSB NYC73AN153Accident · Serious injuriesClosedDouglas DC-8 · Bangor, MEJun 20, 1973 · N863F · Minor damageNTSB NYC73DNC66Accident · No injuriesClosedCessna 172L · Carrabassett, MEApr 15, 1973 · N3821Q · Substantial damageNTSB NYC73DNC60Accident · No injuriesClosedCessna 150 · Eastport, MEFeb 24, 1973 · N8949S · Substantial damageNTSB NYC73FNC14Accident · No injuriesClosedCessna 210D · Oxford, MEJul 29, 1972 · N3940Y · Substantial damageNTSB NYC73DNC05Accident · Minor injuriesClosedBoeing A-75 · Minot, MEJul 23, 1972 · N57849 · Substantial damageNTSB NYC71FNC32Accident · Minor injuriesClosedTaylorcraft DCO-65 · Gorham, MEJun 17, 1971 · N46260 · DestroyedNTSB NYC71DNC26Accident · No injuriesClosedCessna 180 · Baring, MEApr 23, 1971 · N2926A · Substantial damageNTSB NYC71FFG18Accident · No injuriesClosedCessna 150G · Turner, MEMar 26, 1971 · N3604J · Substantial damageNTSB NYC71DFG06Accident · No injuriesClosedPiper PA-18 · Sebago Lake, MESep 5, 1970 · N7501K · DestroyedNTSB NYC70AN128Accident · Serious injuriesClosedDouglas DC-8F · Bangor, MEJun 9, 1970 · N8787R · Minor damageNTSB NYC70FFG04Accident · Serious injuriesClosedCessna 190 · Pownal, MEMar 22, 1970 · N4314N · Substantial damageNTSB NYC70D0157Accident · No injuriesClosedAero Comdr 720 · Loring Afb, MESep 4, 1969 · CF-SJA · Substantial damageNTSB NYC69F0090Accident · No injuriesClosedCessna 150 · Portland, MEJul 26, 1968 · N8602S · Substantial damageNTSB NYC68D0400Accident · No injuriesClosedPiper PA-28 · Waterville, MEMar 14, 1968 · N5716W · Substantial damageNTSB NYC68F0367Accident · No injuriesClosedMooney M20C · Augusta, MEFeb 4, 1968 · N7874V · Substantial damageNTSB NYC68D0117Accident · No injuriesClosedPiper PA-22 · Augusta, MEAug 11, 1967 · N5042Z · Substantial damageNTSB NYC68D0016Accident · Minor injuriesClosedCessna 172G · Sherman, MEJun 13, 1967 · N3653L · Substantial damageNTSB NYC67D0633Accident · No injuriesClosedAeronca 65-TAC · Turner, MEApr 21, 1967 · N36857 · Substantial damageNTSB NYC67F0523Accident · No injuriesClosedCessna 140 · Old Town, MEFeb 17, 1967 · CF-UWH · Substantial damageNTSB NYC67F0496Accident · No injuriesClosedLuscombe 8A · Auburn, MEFeb 15, 1967 · N2984K · Substantial damageNTSB NYC67D0180Accident · No injuriesClosedErco 415-C · Sanford, MEJul 10, 1966 · N99158 · Substantial damageNTSB NYC67F0072Accident · No injuriesClosedPiper PA-30 · Auburn, MEJul 1, 1966 · N7673Y · Substantial damageNTSB NYC66D0350Accident · No injuriesClosedCessna 180 · Mariaville, MENov 4, 1965 · N2620Y · Substantial damageNTSB NYC66D0269Accident · No injuriesClosedAeronca 65-TAC · Ashland, MESep 19, 1965 · N36823 · Substantial damageNTSB NYC66F0237Accident · No injuriesClosedTaylorcraft BC-12D · Island Falls, MESep 5, 1965 · N43479 · Substantial damageNTSB UNK64X3816Accident · Minor injuriesClosedPiper PA22 · Patten, MESep 6, 1964 · N8858D · Substantial damage