NTSB investigations, WI
424 cases where the phase of flight was Approach.
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\" = 'WI' AND c.\"eventCountry\" = 'USA'
AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 500)
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" = 'WI' AND c."eventCountry" = 'USA'
AND c."mKey" IN (SELECT case_mkey FROM case_phases WHERE main = 500)
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 CHI71AC012Accident · Serious injuriesClosedCessna 402 · Watertown, WINTSB CHI71FDJ05Accident · Minor injuriesClosedPiper PA-28 · Green Bay, WINTSB CHI71DDJ04Accident · No injuriesClosedCessna 150 · Platteville, WINTSB CHI71DDJ02Accident · Minor injuriesClosedCessna 180F · Friendship, WINTSB CHI70DDJ26Accident · No injuriesClosedCessna 180 · Lone Rock, WINTSB CHI70DDJ25Accident · No injuriesClosedCessna 182 · Milwaukee, WINTSB CHI70AC091Accident · No injuriesClosedCessna 150G · Burlington, WINTSB CHI70DDJ22Accident · No injuriesClosedPiper PA-28R · Washington Is, WINTSB CHI70FDJ21Accident · No injuriesClosedPiper PA-22 · Mount Horeb, WINTSB CHI70FDM03Accident · Minor injuriesClosedChampion 7ECA · Webster, WINTSB CHI70AC080Accident · 1 fatalClosedTaylorcraft BC-12D · Mauston, WINTSB CHI70DDJ14Accident · No injuriesClosedLuscombe 8A · Sheboygan Fls, WINTSB CHI70DDJ13Accident · No injuriesClosedLuscombe 8E · Wausau, WINTSB CHI70DDJ05Accident · No injuriesClosedCessna 150 · Waukesha, WINTSB MKC70A0018Accident · No injuriesClosedBeech E18S · Ashland, WINTSB CHI70F0276Accident · No injuriesClosedCessna 150 · River Falls, WINTSB CHI70F0342Accident · Serious injuriesClosedPiper J3C-65 · Niagara, WINTSB CHI70F0274Accident · No injuriesClosedPiper PA-24 · Middleton, WINTSB CHI70D0298Accident · No injuriesClosedCessna 140 · Chilton, WINTSB CHI70F0135Accident · No injuriesClosedPiper PA-28 · Middleton, WINTSB CHI70D0129Accident · Minor injuriesClosedCessna 180 · Balsam Lake, WINTSB CHI70F0040Accident · Minor injuriesClosedJeanies Teenie · Lacrosse, WINTSB CHI69A0095Accident · 1 fatalClosedPiper PA-20 · Milwaukee, WINTSB MKC70D0021Accident · No injuriesClosedCessna 172 · Osceola, WINTSB CHI70D0029Accident · No injuriesClosedBeech 35-C33 · Burlington, WINTSB CHI69F0554Accident · No injuriesClosedCessna 150H · Dodgeville, WINTSB CHI69F0444Accident · No injuriesClosedCessna 150E · Pewaukee, WINTSB CHI69F0409Accident · Minor injuriesClosedPiper PA-28 · Sturtevant, WINTSB CHI69F0317Accident · Minor injuriesClosedCessna 172D · Shawano, WINTSB CHI69D0294Accident · Minor injuriesClosedPiper PA-22 · Platteville, WINTSB CHI69D0167Accident · Minor injuriesClosedCessna 150 · Franklin, WINTSB CHI69D0189Accident · Minor injuriesClosedPiper PA-28 · Wonewoc, WINTSB CHI69D0113Accident · No injuriesClosedLuscombe 8A · Green Bay, WINTSB CHI69D0129Accident · No injuriesClosedPiper PA-22 · Neenah, WINTSB CHI69D0021Accident · Minor injuriesClosedPiper PA-28 · Juneau, WINTSB CHI68D0360Accident · No injuriesClosedPiper PA-22 · Platteville, WINTSB CHI68D0584Accident · Minor injuriesClosedBeech A23 · Milwaukee, WINTSB CHI68A0053Accident · 7 fatalClosedBeech H18 · Madison, WINTSB CHI68F0598Accident · Serious injuriesClosedPiper PA-32 · Wauwatosa, WINTSB CHI68A0012Accident · 2 fatalClosedTaylorcraft BC12-D · Holcombe, WINTSB CHI68D0324Accident · Minor injuriesClosedCurtis Scout · Waukesha, WINTSB CHI68D0375Accident · No injuriesClosedCessna 172 · Ft.Atkinson, WINTSB CHI67A0141Accident · No injuriesClosedBell 47G-2 · Wisc.Dells, WINTSB CHI68F0270Accident · No injuriesClosedCessna 182 · Osceola, WINTSB CHI68D0156Accident · No injuriesClosedCessna 182 · Baileys Hrbr, WINTSB CHI67D0661Accident · No injuriesClosedPiper PA-28 · Pewaukee, WINTSB CHI67F0430Accident · Minor injuriesClosedBeech C35 · Waukesha, WINTSB CHI67D0347Accident · Minor injuriesClosedChampion 7FC · Elcho, WINTSB CHI67A0016Accident · 1 fatalClosedCessna 150 · Racine, WINTSB CHI67D0035Accident · Minor injuriesClosedLuscombe 8A · Montello, WI