NTSB investigations, MI
11 cases where the first occurrence was “Missing aircraft”.
Cases per year
| 1990 | 1 |
|---|---|
| 1992 | 3 |
| 1993 | 2 |
| 1997 | 1 |
| 1998 | 1 |
| 1999 | 1 |
| 2000 | 1 |
| 2002 | 1 |
Where
Open the mapLatest cases
Query with SQL
duckdb -csv -c "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\" = 'MI' AND c.\"eventCountry\" = 'USA'
AND c.\"mKey\" IN (SELECT o.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_occurrences.parquet' o WHERE o.\"occurrenceNumber\" = 1 AND o.\"occurrenceCode\" = 420)
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.
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" = 'MI' AND c."eventCountry" = 'USA' AND c."mKey" IN (SELECT o.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_occurrences.parquet' o WHERE o."occurrenceNumber" = 1 AND o."occurrenceCode" = 420) ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC
Run 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 CHI03FAMS1Accident · 2 fatalMissing aircraftFinal reportCessna 175 · MIUndetermined. Missing aircraft.NTSB CHI00FAMS2Accident · 1 fatalMissing aircraftFinal reportPeters RAF 2000 · Manistique, MIundetermined.NTSB CHI00FAMS1Accident · 1 fatalMissing aircraftFinal reportCessna 175 · MIundetermined.NTSB CHI98FAMS1Accident · 2 fatalMissing aircraftFinal reportAero Vodochody Aero. Works L-39C · Traverse City, MIUndetermined.NTSB CHI97FA308Accident · 2 fatalField investigationFinal reportPiper PA-28-235 · St. Ignaces, MIThe noninstrument-rated pilot's improper decision to depart in instrument meteorological conditions, which resulted in impact with terrain.NTSB CHI94FAMS1Accident · 1 fatalMissing aircraftFinal reportCessna 172I · Kalamazoo, MIUndetermined.NTSB CHI93FAMS2Accident · 1 fatalMissing aircraftFinal reportPiper PA-28R-200 · Grand Haven, MIundetermined.NTSB CHI92FAMS3Accident · 1 fatalMissing aircraftFinal reportCessna 150H · Manistee, MIUndeterminedNTSB CHI92FAMS2Accident · 4 fatalMissing aircraftFinal reportCessna 172N · Lake Michigan, MIUndetermined.NTSB CHI92FAMS1Accident · 2 fatalMissing aircraftFinal reportBeech 95-BE55 · Benton Harbor, MIUndetermined.NTSB CHI90FAMS2Accident · 2 fatalMissing aircraftFinal reportPiper PA-30B · Charlevoix, MIThe probable cause of this accident is undetermined.