NTSB investigations, IL
333 cases where the phase of flight was Maneuvering.
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 = 450)
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.
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 = 450)
ORDER BY c."eventDate" DESC, c."ntsbNumber" DESCRun it at the DuckDB prompt, or in any tool that reads Parquet over HTTPS. More ways to query the release are on Use the data.
NTSB CHI82DA318Accident · 2 fatalFinal reportMustang II · Plainfield Twp, ILAircraft came down vertically rolling. Just before ground contact or at about 200 feet rolling slowed and aircraft continued descent to terrain contact. No malfunction noted.NTSB CHI82FA312Accident · 2 fatalField investigationFinal reportTaylorcraft BL-65 · Rock Falls, ILAircraft caught fire while performing aerobatics and crashed out of control. Gas tank cap had come off and during inverted flight fuel spilled out and ignited.NTSB CHI82DA270Accident · Serious injuriesFinal reportAustin S.E. 5A · Bolingbrook, ILThe pilots of an Austin s.e. 5A and a folker (both ww I replica aircraft) agreed to make a photo fly-by. The pilot of the Austin took off as number one, but instead of remaining in the traffic pattern, he turned out of the pattern. The folker pilot followed…NTSB CHI82FA262Accident · 2 fatalField investigationFinal reportCessna 150L · Pontiac, ILBoth plts onboard were cfi's. the acft was observed to slow down & enter a stall/spin which continued to impact.NTSB CHI82DA215Accident · Serious injuriesFinal reportBellanca 73CA · Fairfield, ILThe pilot stated that after takeoff he flew 3 or 4 miles, started a right turn and could not recall any further events concerning the flight. Investigation revealed the aircraft crashed on flat terrain. Examination of the flight controls did not reveal any…NTSB CHI81FEV34Accident · Minor injuriesClosedCessna 150M · Wyoming, ILNTSB CHI81FEV27Accident · 1 fatalClosedCessna T188C · Ridgway, ILNTSB CHI81FEV26Accident · No injuriesClosedBrantly B-2B · Chapin, ILNTSB CHI81FEV24Accident · 1 fatalClosedPiper PA-25 · Olney, ILNTSB CHI81FEV23Accident · Serious injuriesClosedCessna A188B · Metropolis, ILNTSB CHI81FEV21Accident · 1 fatalClosedTexas Hel OH-13H · Danville, ILNTSB CHI81FEV14Accident · Serious injuriesClosedBell 47G-2 · Breese, ILNTSB CHI81FA045Accident · 3 fatalClosedCessna 172N · Atlas, ILNTSB CHI81FA033Accident · 1 fatalClosedCessna 172M · Quincy, ILNTSB CHI81FCA02Accident · 2 fatalClosedAlvarez Polli · Grantfork, ILNTSB CHI81DEV01Accident · Minor injuriesClosedGrumman G-164 · Golden, ILNTSB CHI80FEV39Accident · No injuriesClosedCessna 188 · Mill Shoals, ILNTSB CHI80FEV31Accident · No injuriesClosedGrumman 164B · Maroa, ILNTSB CHI80FEV27Accident · No injuriesClosedGrumman G-164B · Atlanta, ILNTSB CHI80FA049Accident · 2 fatalClosedBellanca 8KCAB · Clifton, ILNTSB CHI80FEE30Accident · No injuriesClosedSchweizer 1-35 · Hinckley, ILNTSB CHI80FEV22Accident · Minor injuriesClosedHughes 369HS · Grayville, ILNTSB CHI79FEV35Accident · 1 fatalClosedBellanca 8KCAB · Alma, ILNTSB CHI79DEE75Accident · Minor injuriesClosedBoeing E-75N1 · Erie, ILNTSB CHI79DEV28Accident · No injuriesClosedBrantly B-2B · Flanagan, ILNTSB CHI79FEV22Accident · 1 fatalClosedLuscomb 8A · Garrett, ILNTSB CHI79FEV17Accident · No injuriesClosedGrumman G-164B · Sandoval, ILNTSB CHI79FEY16Accident · No injuriesClosedCessna A188-B · Thompsonville, ILNTSB CHI79DA039Accident · 1 fatalClosedHughes 369D · Pekin, ILNTSB CHI79DEE36Accident · No injuriesClosedCessna 172 · Sublette, ILNTSB CHI79DEE12Accident · No injuriesClosedGrumman AA-5A · Morris, ILNTSB CHI78FEV53Accident · No injuriesClosedCessna 180A · Sparta, ILNTSB CHI78FEV49Accident · No injuriesClosedBell OH-13H · Wellington, ILNTSB CHI78DEV45Accident · No injuriesClosedPiper PA-25 · Laprairie, ILNTSB CHI78FEE61Accident · No injuriesClosedMurphy RV3 · Streator, ILNTSB CHI78FEV40Accident · No injuriesClosedCessna A188B · Ramsey, ILNTSB CHI78DEE33Accident · No injuriesClosedHughes 269C · Mendota, ILNTSB CHI78FA036Accident · 2 fatalClosedBellanca 7KCAB · Rockford, ILNTSB CHI78FEV25Accident · 1 fatalClosedBell 47-G · Clinton, ILNTSB CHI78DEV04Accident · No injuriesClosedPietenpol Camper · Marshall, ILNTSB CHI78FEV01Accident · No injuriesClosedBell 47G4 · Crossville, ILNTSB CHI77FEE45Accident · Serious injuriesClosedCessna A188B · Kankakee, ILNTSB CHI77DEV41Accident · No injuriesClosedContinental 5A · Warrensburg, ILNTSB CHI77DCA05Accident · No injuriesClosedCessna 188C · Valmeyer, ILNTSB CHI77FEE40Accident · No injuriesClosedBrantly B2B · Ringwood, ILNTSB CHI77FEV33Accident · No injuriesClosedHughes 369C · Mackinaw, ILNTSB CHI77DEV31Accident · No injuriesClosedCessna 188B · Duquoin, ILNTSB CHI77FEE31Accident · No injuriesClosedPitts S1 · Lockport, ILNTSB CHI77FEV27Accident · 1 fatalClosedTolman SA-3AM · Salem, ILNTSB CHI77FEV26Accident · Serious injuriesClosedPiper J-3 · Manito, IL