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 CHI79FEP52Accident · Minor injuriesClosedDavis DA-3 · Burlington, WINTSB CHI79FER03Accident · Minor injuriesClosedCessna 182B · Osceola, WINTSB CHI79FEP47Accident · No injuriesClosedPiper PA-32R · Burlington, WINTSB CHI79FEP44Accident · Serious injuriesClosedCessna T210N · St. Francis, WINTSB CHI79FDP35Accident · No injuriesClosedBeech G18S · Clintonville, WINTSB CHI79DEP28Accident · No injuriesClosedBeech BE-19 · Milwaukee, WINTSB CHI79FA027Accident · 1 fatalClosedBeech 58P · Waukesha, WINTSB CHI79FEP16Accident · No injuriesClosedCessna 172K · Dodgeville, WINTSB CHI79FEP06Accident · Serious injuriesClosedGrumman AA1-A · Milwaukee, WINTSB CHI78DEP62Accident · No injuriesClosedCessna 150 · Milwaukee, WINTSB CHI78DEP60Accident · No injuriesClosedCessna 150K · Kenosha, WINTSB CHI78FEP52Accident · 2 fatalClosedThorp T-18 · Oshkosh, WINTSB CHI78FEP50Accident · No injuriesClosedCessna 172 · Tony, WINTSB CHI78FEP49Accident · Minor injuriesClosedCessna 172E · Pewaukee, WINTSB CHI78DEP48Accident · Minor injuriesClosedThorp T18 · Kenosha, WINTSB CHI78FA049Accident · 1 fatalClosedBellanca 17-30A · Rhinelander, WINTSB CHI78DEP39Accident · No injuriesClosedMustang II · East Troy, WINTSB CHI78FA042Accident · 1 fatalClosedCessna 150M · Waukesha, WINTSB CHI78FEP36Accident · Serious injuriesClosedPiper PA-28 · Portage, WINTSB CHI78DEP16Accident · No injuriesClosedCessna 150 · Kenosha, WINTSB CHI78FEP14Accident · Minor injuriesClosedBell 47-J · Wilmot, WINTSB CHI78DER03Accident · No injuriesClosedCessna 150 · Superior, WINTSB CHI78FEP07Accident · No injuriesClosedCessna 177 · Madison, WINTSB CHI78FA008Accident · 1 fatalClosedCessna 310P · Eagle River, WINTSB CHI78FA007Accident · 1 fatalClosedCessna 310Q · Watertown, WINTSB CHI78FEP09Accident · Serious injuriesClosedPiper PA-31 · Manitowoc, WINTSB CHI78FEP05Accident · No injuriesClosedPiper PA-24 · Green Bay, WINTSB CHI78FEP04Accident · Minor injuriesClosedCessna 172M · Elkhorn, WINTSB CHI77DEP50Accident · No injuriesClosedSchweizer 1-26B · West Bend, WINTSB CHI77FEP47Accident · Serious injuriesClosedCessna 172 · Lake Geneva, WINTSB CHI77DEP52Accident · No injuriesClosedBeech B-19 · Milwaukee, WINTSB CHI77FEP45Accident · No injuriesClosedBellanca 17-30A · Milwaukee, WINTSB CHI77FEP29Accident · Minor injuriesClosedCessna 172 · Janesville, WINTSB CHI77FEP24Accident · Minor injuriesClosedBeech C45H · Milwaukee, WINTSB CHI77DEP20Accident · No injuriesClosedCessna 172 · Delavan, WINTSB CHI77DEP15Accident · No injuriesClosedPiper PA-28 · Pewaukee, WINTSB CHI77FEP14Accident · Minor injuriesClosedPiper PA-23 · Lone Rock, WINTSB CHI77FEP12Accident · Serious injuriesClosedPiper PA-30A · Madison, WINTSB CHI77FA015Accident · 2 fatalClosedAeronca 7AC · Freedom, WINTSB CHI77DEP05Accident · Minor injuriesClosedCessna 140 · Brookfield, WINTSB CHI77DEP03Accident · Minor injuriesClosedCessna 172 · Middleton, WINTSB CHI76DEP51Accident · Minor injuriesClosedCessna 150 · Boscobel, WINTSB CHI76DEP46Accident · Serious injuriesClosedCessna 172 · Dodgeville, WINTSB CHI76FEP42Accident · Minor injuriesClosedMooney M20E · Grafton, WINTSB CHI76FEP39Accident · Minor injuriesClosedPiper PA-28 · Sturtevant, WINTSB CHI76FEP28Accident · No injuriesClosedPiper PA-28R · Pulaski, WINTSB CHI76AC052Accident · 1 fatalClosedBeech E18S · Burlington, WINTSB CHI76DEP23Accident · Minor injuriesClosedTaylorcraft BC12-D · Chambers Isle, WINTSB CHI76DEP21Accident · No injuriesClosedCessna 140 · Kenosha, WINTSB CHI76AC044Accident · 1 fatalClosedPiper PA-28 · Beloit, WI