NTSB investigations, IL

71 cases where the phase of flight was Standing.

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\" = 'IL' AND c.\"eventCountry\" = 'USA'
    AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 150)
  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 CHI75FEE69Accident · No injuriesClosedPiper PA-28 · Belvidere, ILJun 21, 1975 · N6398R · 2 aircraft · Minor damageNTSB CHI75AC005Accident · Serious injuriesClosedBoeing 727 · Chicago, ILJul 10, 1974 · N6818 · No damageNTSB CHI74FEE83Accident · No injuriesClosedCessna 172N · Rock Falls, ILJun 6, 1974 · N19975 · DestroyedNTSB CHI74FEE23Accident · No injuriesClosedCessna 310J · West Chicago, ILSep 22, 1973 · N3187L · Substantial damageNTSB CHI72FEE58Accident · No injuriesClosedCessna 150 · Northbrook, ILFeb 26, 1972 · N22265 · 2 aircraft · Substantial damageNTSB MKC72DCA21Accident · No injuriesClosedCessna 172 · Cahokia, ILJan 24, 1972 · N7710U · Substantial damageNTSB CHI72IC055Incident · 1 fatalClosedLockheed Ga 382 · Chicago, ILDec 26, 1971 · N9258R · No damageNTSB CHI72AC034Accident · No injuriesClosedDouglas DC-9 · Chicago, ILOct 9, 1971 · N3308L · Minor damageNTSB CHI71FEV41Accident · No injuriesClosedPiper PA-18 · Sheldon, ILMay 16, 1971 · N223T · Minor damageNTSB CHI71AC070Accident · Serious injuriesClosedBoeing 727 · Chicago, ILApr 1, 1971 · N7892 · No damageNTSB CHI71AC059Accident · 1 fatalClosedCessna 150 · Frankfort, ILFeb 25, 1971 · N5758G · No damageNTSB CHI70AC059Accident · Serious injuriesClosedBoeing 720 · Chicago, ILMar 2, 1970 · N7210U · No damageNTSB CHI69A0036Accident · Serious injuriesClosedBoeing 727 · Springfield, ILSep 23, 1968 · N1981 · No damageNTSB CHI68A0109Accident · 1 fatalClosedBeech 65-90 · Chicago, ILJun 24, 1968 · N895K · Minor damageNTSB CHI68D0057Accident · No injuriesClosedAero Comdr 560-E · Wheeling, ILMay 31, 1967 · N701W · No damageNTSB CHI67D0312Accident · Serious injuriesClosedCessna 172 · Frankfort, ILSep 10, 1966 · N7279A · No damageNTSB CHI67A0007Accident · No injuriesClosedDouglas DC-3 · Peoria, ILJul 17, 1966 · N52935 · Substantial damageNTSB CHI66F0712Accident · No injuriesClosedPiper PA-23 · Quincy, ILApr 28, 1966 · N503GD · Substantial damageNTSB CHI66A0074Accident · Serious injuriesClosedDouglas DC-9 · Chicago, ILMar 4, 1966 · N3307L · No damageNTSB CHI66F0327Accident · No injuriesClosedNavion G · Peoria, ILSep 23, 1965 · N2424T · Substantial damageNTSB CHI66I0012Incident · No injuriesClosedBoeing 720B · Chicago, ILJul 31, 1965 · N723US · Minor damage