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.

NTSB LAX72FUG69Accident · Serious injuriesClosedCessna 172E · Hesperia, CAApr 15, 1972 · N5532T · Substantial damageNTSB FTW72FPA21Accident · No injuriesClosedCessna 310D · Addison, TXApr 15, 1972 · N212X · Substantial damageNTSB LAX72FUM23Accident · Serious injuriesClosedLuscombe 8E · El Cajon, CAApr 14, 1972 · N2886K · DestroyedNTSB MKC72DCQ49Accident · No injuriesClosedCessna 172 · Norton, KSApr 13, 1972 · N5339R · Substantial damageNTSB CHI72FEE73Accident · No injuriesClosedMooney M-20C · Northbrook, ILApr 13, 1972 · N6750U · Substantial damageNTSB MIA72IM109Incident · No injuriesClosedBoeing 727 · Jacksonville, FLApr 11, 1972 · N4613 · Minor damageNTSB FTW72DRG78Accident · Minor injuriesClosedCessna 172 · S.Luis Potosi, MexicoApr 10, 1972 · N5352T · Substantial damageNTSB CHI72DEE71Accident · No injuriesClosedMGT Mustang MM1 · Lockport, ILApr 9, 1972 · N8710 · Substantial damageNTSB NYC72FGM39Accident · No injuriesClosedCessna 150H · Butler, PAApr 8, 1972 · N23210 · Substantial damageNTSB MIA72DKG92Accident · No injuriesClosedTaylorcraft BC12-D · Lake Park, GAApr 8, 1972 · N96984 · Substantial damageNTSB NYC72AN121Accident · 3 fatalClosedBeech E18S · Skaneateles, NYApr 6, 1972 · N50W · Substantial damageNTSB MKC72DCD66Accident · No injuriesClosedCessna 170 · Paullina, IAApr 6, 1972 · N3101B · Substantial damageNTSB LAX72FUG66Accident · Minor injuriesClosedPiper PA-18 · Elsinore, CAApr 6, 1972 · N3788P · Substantial damageNTSB MIA72AM106Accident · 1 fatalClosedBeech 95 · Miami, FLApr 5, 1972 · N738CB · DestroyedNTSB DEN72FTE65Accident · No injuriesClosedPiper PA-25 · Merino, COApr 5, 1972 · N7712Z · Substantial damageNTSB SEA72AS044Accident · 1 fatalClosedCessna 170A · Medford, ORApr 4, 1972 · N1474D · DestroyedNTSB MIA72FLA38Accident · Serious injuriesClosedPiper PA-25 · Palatka, FLApr 4, 1972 · N6774Z · DestroyedNTSB CHI72FEC34Accident · Minor injuriesClosedCessna 170B · Troy, MIApr 4, 1972 · N3177A · Substantial damageNTSB LAX72FUQ72Accident · Serious injuriesClosedFunk B85C · Pacoima, CAApr 2, 1972 · N21DC · Substantial damageNTSB LAX72FUJ89Accident · Minor injuriesClosedCessna 150C · Chandler, AZApr 2, 1972 · N1932Z · Substantial damageNTSB FTW72FRA49Accident · Minor injuriesClosedPiper PA-28 · Kennard, TXApr 2, 1972 · N6007U · Substantial damageNTSB DEN72DTM14Accident · No injuriesClosedTemco 11A · Modena, UTApr 2, 1972 · N1665B · DestroyedNTSB MIA72DKG85Accident · No injuriesClosedGrumman G-164 · Dublin, GAApr 1, 1972 · N950X · Substantial damageNTSB NYC72FGM38Accident · No injuriesClosedCessna 182 · Sharon, PAMar 29, 1972 · N4774D · Substantial damageNTSB NYC72AN119Accident · 1 fatalClosedPiper PA-28 · Laytonsville, MDMar 29, 1972 · N4353T · DestroyedNTSB NYC72DHM37Accident · No injuriesClosedBeech A23A · Rhinebeck, NYMar 26, 1972 · N7962L · Substantial damageNTSB NYC72AN118Accident · 2 fatalClosedPiper PA-22 · Larrysville, PAMar 26, 1972 · N3348Z · DestroyedNTSB ANC72DAH04Accident · No injuriesClosedPiper PA-18A · Glenallen, AKMar 26, 1972 · N9444D · Substantial damageNTSB OAK72DXQ25Accident · Minor injuriesClosedCessna 172 · Wells, NVMar 25, 1972 · N1655F · Substantial damageNTSB MKC72FCD64Accident · No injuriesClosedLuscombe 8E · Adel, IAMar 25, 1972 · N1306B · Substantial damageNTSB MKC72DER50Accident · No injuriesClosedCessna 170B · Lake Elmo, MNMar 25, 1972 · N8214A · Substantial damageNTSB MKC72DER46Accident · No injuriesClosedAmer Avco AA1 · Stanton, MNMar 25, 1972 · N5980L · Substantial damageNTSB LAX72FUG57Accident · No injuriesClosedCessna 120 · Elsinore, CAMar 23, 1972 · N1692V · Substantial damageNTSB SEA72DTA14Accident · Minor injuriesClosedPiper PA-25 · Brusett, MTMar 22, 1972 · N7312Z · Substantial damageNTSB MIA72IM095Incident · No injuriesClosedPiper J3-S · St Petersburg, FLMar 22, 1972 · N90411 · Minor damageNTSB NYC72DKM26Accident · Serious injuriesClosedJeanies Teenie · Marion, NCMar 21, 1972 · N2534 · Substantial damageNTSB ANC72FAA25Accident · 1 fatalClosedCessna 180H · Delta Junctn, AKMar 19, 1972 · N2769X · DestroyedNTSB NYC72FID22Accident · No injuriesClosedCessna 150K · Hopewell, VAMar 18, 1972 · N6049G · Substantial damageNTSB MIA72DLD55Accident · Minor injuriesClosedCessna 177 · Lake Wales, FLMar 18, 1972 · N3452F · Substantial damageNTSB MIA72DKQ32Accident · No injuriesClosedCallair A-9 · Avon, MSMar 18, 1972 · N7601V · Substantial damageNTSB CHI72FCA02Accident · Serious injuriesClosedBeech H35 · East St.Louis, ILMar 18, 1972 · N5529D · Substantial damageNTSB SEA72FYC30Accident · Minor injuriesClosedPiper PA-18 · North Plains, ORMar 17, 1972 · N1195A · Substantial damageNTSB SEA72DYG30Accident · No injuriesClosedSnow 600S2R · Sunnyside, WAMar 17, 1972 · N1763S · Substantial damageNTSB NYC72DKM25Accident · No injuriesClosedPiper PA-28 · Brevard, NCMar 16, 1972 · N5516W · Substantial damageNTSB NYC72FNA37Accident · Serious injuriesClosedPiper PA-24 · Marshfield, MAMar 11, 1972 · N6322P · Substantial damageNTSB NYC72FHJ17Accident · No injuriesClosedAeronca 7AC · Amityville, NYMar 11, 1972 · N2122E · Substantial damageNTSB LAX72DUJ86Accident · No injuriesClosedPiper J3C-65 · Scottsdale, AZMar 11, 1972 · N4967V · Substantial damageNTSB SEA72AS037Accident · 1 fatalClosedPiper PA-32 · Tacoma, WAMar 10, 1972 · N5567J · DestroyedNTSB LAX72FUJ81Accident · Serious injuriesClosedAeronca 7AC · Tucson, AZMar 10, 1972 · N3343E · DestroyedNTSB CHI72FEE60Accident · No injuriesClosedCessna 140 · Wheeling, ILMar 10, 1972 · N3552V · Substantial damage