NTSB investigations, Outside the U.S.
183 cases where the defining event was Collision with terr/obj (non-CFIT).
Cases, newest first
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.\"eventCountry\" <> 'USA'
AND c.\"mKey\" IN (SELECT e.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e WHERE e.\"isDefiningEvent\" AND e.\"tier2Number\" = '470')
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."eventCountry" <> 'USA' AND c."mKey" IN (SELECT e.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e WHERE e."isDefiningEvent" AND e."tier2Number" = '470') 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 GAA22WA298Accident · 5 fatalClass 5 · ForeignRobin D140 · Grenoble, FranceNTSB GAA22WA184AccidentClass 5 · ForeignRans S6-ES · Hampshire, United KingdomNTSB GAA22WA185Accident · 1 fatalClass 5 · ForeignSikorsky S61N · Tongyeong-si, KiribatiNTSB GAA22WA171Accident · 4 fatalClass 5 · ForeignPiper PA28 · Sioux Lookout Ontario, CanadaNTSB GAA22WA159Accident · 1 fatalClass 5 · ForeignCessna 172 · Coonagarabran, AustraliaNTSB GAA22WA144Accident · 2 fatalClass 5 · ForeignBell 206 · Adaminaby, AustraliaNTSB GAA22WA201Accident · Serious injuriesClass 5 · ForeignBell 206L-1 · Cooma, AustraliaNTSB GAA22WA113Accident · 1 fatalClass 5 · ForeignCessna U206G · Norseman, AustraliaNTSB GAA22WA112Accident · 14 fatalClass 5 · ForeignCessna 208B · Comoros, ComorosNTSB GAA22WA109Accident · 1 fatalClass 5 · ForeignPiper PA-25-235 · Leongatha, AustraliaNTSB GAA22WA102Accident · 1 fatalClass 5 · ForeignCessna FA150 · Utrera, SpainNTSB GAA22WA103AccidentClass 5 · ForeignGarlick UH1H · Devonport, AustraliaNTSB GAA22WA095Accident · 2 fatalClass 5 · ForeignCessna 172 · Region de la Araucania, ChileNTSB GAA22WA056Accident · 1 fatalClass 5 · ForeignAmateur Built D11 · South Mackay, AustraliaNTSB GAA22WA052Accident · 1 fatalClass 5 · ForeignPiper 25-260 · Vereda Aparco, Ibague, ColombiaNTSB GAA22WA050Accident · 9 fatalClass 5 · ForeignGulfstream GIV · Santo Domingo, Dominican RepublicNTSB GAA22WA040AccidentClass 5 · ForeignAir Tractor AT-400 · Bulyeroi, AustraliaNTSB GAA22WA038Accident · 2 fatalClass 5 · ForeignMurphy Rebel · Wasbush, CanadaNTSB DCA22WA034IncidentClass 5 · ForeignBoeing 737 · Maringá, BrazilNTSB GAA22WA012AccidentClass 5 · ForeignRobinson R44 · Buchen-Sturzenhardt, GermanyNTSB GAA22WA089Accident · 8 fatalClass 4 · ForeignPilatus PC12 · San Donato, ItalyNTSB GAA22WA001Accident · 3 fatalClass 5 · ForeignRobinson R44 · Lytkarino, RussiaNTSB GAA21WA250Accident · 1 fatalClass 5 · ForeignCessna T206 · La Pajarera, MexicoNTSB GAA21WA225Accident · 1 fatalClass 5 · ForeignPiper J3C · Mauricie, CanadaNTSB GAA21WA180Accident · 2 fatalClass 5 · ForeignPiper PA28 · Contrada Coccaro Fasano, ItalyNTSB GAA21WA210AccidentClass 5 · ForeignCessna 172 · Beirut, LebanonNTSB GAA21WA172Accident · 3 fatalClass 5 · ForeignDouglas DC3 · Restrepo, ColombiaNTSB GAA21WA167Accident · Minor injuriesClass 5 · ForeignCessna 152 · Lima, PeruNTSB GAA21WA154Accident · Serious injuriesClass 5 · ForeignPiper PA-32-300 · Moorabbin Aerodrome, AustraliaNTSB GAA21WA145Accident · 1 fatalClass 5 · ForeignRobinson R44 · Eidanger, NorwayNTSB DCA21WA159Accident · No injuriesClass 5 · ForeignBoeing 737 · Derbyshire, United KingdomNTSB GAA21WA141Accident · No injuriesClass 5 · ForeignBell 407 · Siaya County, KenyaNTSB GAA21WA130Accident · 1 fatalClass 5 · ForeignCessna 188A · Inmediaciones Municipio Paz de Ariporo, Casanare, ColombiaNTSB GAA21WA133Accident · 1 fatalClass 5 · ForeignRobinson R22 · Hay South, AustraliaNTSB GAA21WA132AccidentClass 5 · ForeignCessna 210 · Itaituba, BrazilNTSB GAA21WA104Accident · 2 fatalClass 5 · ForeignRobinson R22 · Durban, South AfricaNTSB GAA21WA100Accident · 4 fatalClass 5 · ForeignBell 206 · Rio Bravo, GuatemalaNTSB GAA21WA089Accident · 1 fatalClass 5 · ForeignVans RV7A · Columbia, AustraliaNTSB GAA21WA081Accident · 2 fatalClass 5 · ForeignCessna 172 · Canberra, AustraliaNTSB GAA21WA201AccidentClass 5 · ForeignRobinson R44 · Hertzog Ville, South AfricaNTSB GAA21WA059Accident · No injuriesClass 4 · ForeignBeech 200 · Nairobi, KenyaNTSB GAA21WA038Accident · 1 fatalClass 5 · ForeignRobinson R22 · Bollena, ItalyNTSB GAA21WA048Accident · No injuriesClass 5 · ForeignRobinson R66 · Khvoyninsky District, RussiaNTSB DCA21WA086Accident · No injuriesClass 5 · ForeignBoeing 737 · Vijayawada, IndiaNTSB GAA21WA021AccidentClass 5 · ForeignRobinson R22 · Hughenden, AustraliaNTSB GAA21WA018Accident · 2 fatalClass 4 · ForeignBell 206B · Courchevel, FranceNTSB GAA21WA015Accident · 1 fatalClass 5 · ForeignAir Tractor AT402 · Hoopstad, South AfricaNTSB GAA21WA029Accident · 1 fatalClass 5 · ForeignBell 206 · Wolseley, South AfricaNTSB ANC21WA011Accident · 4 fatalClass 5 · ForeignRobinson R44 · Eaglesham, CanadaNTSB DCA21WA063IncidentClass 5 · ForeignAirbus A319-115 · Bogata, Colombia