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 CHI76FEP16Accident · No injuriesClosedCessna 150H · Lake Geneva, WINTSB CHI76DER04Accident · No injuriesClosedBensen B-8M · New Richmond, WINTSB CHI75FEP65Accident · No injuriesClosedBeech E18S · Burlington, WINTSB CHI75FEP68Accident · Serious injuriesClosedGrum Amer AA1 · Delavan, WINTSB CHI75EEP50Accident · No injuriesClosedErco 415-C · Oconto, WINTSB CHI75FEP31Accident · Serious injuriesClosedCessna 182P · Racine, WINTSB CHI75FEP25Accident · No injuriesClosedStinson V-77 · Belleville, WINTSB CHI75FEP23Accident · Serious injuriesClosedCessna 150E · Milwaukee, WINTSB CHI75FEP22Accident · 1 fatalClosedCessna 182H · West Bend, WINTSB CHI75FEP21Accident · Serious injuriesClosedFairchild M-62A · Oostburg, WINTSB CHI75AC017Accident · 1 fatalClosedAmer Avco AA-1A · New Richmond, WINTSB CHI75FEP04Accident · Minor injuriesClosedPiper PA-18 · Hazelhurst, WINTSB CHI75AC003Accident · No injuriesClosedCessna 310C · Middleton, WINTSB CHI74FEP52Accident · Minor injuriesClosedCessna 210 · Watertown, WINTSB CHI74DER04Accident · Minor injuriesClosedPiper PA-28 · Superior, WINTSB CHI74DEP45Accident · No injuriesClosedAmer Avco AA1-A · Brodhead, WINTSB CHI74FEP41Accident · No injuriesClosedAmer Avco AA-1B · Waunakee, WINTSB CHI74DEP24Accident · No injuriesClosedCessna 150 · Crivitz, WINTSB CHI74DEP23Accident · Minor injuriesClosedAeronca 11AC · Neshkoro, WINTSB CHI74AC043Accident · 4 fatalClosedPiper PA-28 · Delavan, WINTSB CHI74FEP19Accident · No injuriesClosedCessna 175A · Milwaukee, WINTSB CHI74AC033Accident · 1 fatalClosedBeech D35 · Aurora, WINTSB CHI74FEP11Accident · Minor injuriesClosedCessna 170B · Kenosha, WINTSB CHI74FEP10Accident · Serious injuriesClosedCessna 172 · Cadott, WINTSB CHI74FEP04Accident · Serious injuriesClosedStits SA3B · Oshkosh, WINTSB CHI74FEP03Accident · No injuriesClosedBede BD-5J · Oshkosh, WINTSB CHI74DEP02Accident · Minor injuriesClosedCessna 150L · Stoughton, WINTSB CHI73FEP60Accident · Serious injuriesClosedPiper PA-24 · Madison, WINTSB CHI73DEP56Accident · Minor injuriesClosedCessna 177 · Rhinelander, WINTSB CHI73FEP51Accident · No injuriesClosedPiper PA-28 · Sturtevant, WINTSB CHI73FEP33Accident · No injuriesClosedCessna 172C · New Holstein, WINTSB CHI73FEP30Accident · Minor injuriesClosedCessna 120 · Hartford, WINTSB CHI73DER01Accident · No injuriesClosedCessna 150G · New Richmond, WINTSB CHI72FEP51Accident · Minor injuriesClosedPiper J3C-65 · Fond Du Lac, WINTSB CHI72FEP45Accident · 1 fatalClosedBensen B-8 · Wiscnsin Rpds, WINTSB CHI73AC085Accident · 5 fatalClosedBeech 65-A90 · Racine, WINTSB CHI72AC083Accident · 1 fatalClosedShort Bros SC-7-3 · Lacrosse, WINTSB CHI72FEP24Accident · No injuriesClosedMitsubishi MU2B20 · East Troy, WINTSB CHI72FEP23Accident · Minor injuriesClosedPiper PA-32 · Waukesha, WINTSB CHI72DEP19Accident · No injuriesClosedDoers B8M · Tichigan, WINTSB CHI71DEP50Accident · Minor injuriesClosedCessna 337 · Land O Lakes, WINTSB CHI71AC060Accident · 1 fatalClosedCessna 411 · Germantown, WINTSB CHI71DDJ43Accident · Minor injuriesClosedCessna A150K · Appleton, WINTSB CHI71AC053Accident · 1 fatalClosedPiper PA-22 · Dyckesville, WINTSB CHI71AC031Accident · Serious injuriesClosedDehavilland DHC-6 · Lacrosse, WINTSB CHI71FDJ24Accident · Minor injuriesClosedCessna 195 · Franklin, WINTSB CHI71FDJ19Accident · Serious injuriesClosedCessna 150J · Eau Claire, WINTSB CHI71DDJ16Accident · No injuriesClosedCessna 177 · Black Rvr Fls, WINTSB CHI71DDJ14Accident · No injuriesClosedPiper PA-28 · Delavan, WINTSB CHI71FDJ13Accident · No injuriesClosedLuscombe 8E · Cottage Grove, WI