Skip to content

NTSB investigations, SD

180,611 investigations by the National Transportation Safety Board since 1962: 174,590 accidents, the rest incidents and other occurrences. Each has NTSB’s narrative and, once the investigation is done, its probable cause; since 2008, its findings and sequence of events.

Defining event

Finding

Phase of flight

Aircraft model

Cases, newest first

Query with SQL

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" = 'SD' AND c."eventCountry" = 'USA' ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

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\" = 'SD' AND c.\"eventCountry\" = 'USA'
  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" = 'SD' AND c."eventCountry" = 'USA'
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 MKC72DTK26Accident · No injuriesClosedPiper PA-31 · Brookings, SDMar 18, 1972 · N6620L · Substantial damage NTSB MKC72AK054Accident · 2 fatalClosedCessna 172F · Sioux Falls, SDMar 8, 1972 · N8150U · Destroyed NTSB MKC72DTK25Accident · No injuriesClosedLuscombe 8A · Tea, SDFeb 28, 1972 · N2917K · Substantial damage NTSB MKC72FTK22Accident · Serious injuriesClosedChampion 7FC · Viborg, SDFeb 14, 1972 · N9862B · Substantial damage NTSB MKC72DTK23Accident · No injuriesClosedCessna 310 · Winner, SDFeb 13, 1972 · N3253X · Substantial damage NTSB MKC72FTK21Accident · No injuriesClosedBeech A35 · Hot Springs, SDJan 23, 1972 · N8706A · Substantial damage NTSB MKC72FTK19Accident · Serious injuriesClosedBell 47G-2 · Rapid City, SDJan 16, 1972 · N939B · Destroyed NTSB MKC72DTK20Accident · No injuriesClosedPiper J-3 · Watertown, SDJan 16, 1972 · N88653 · Substantial damage NTSB MKC72DTK18Accident · No injuriesClosedPiper J-3 · Arlington, SDJan 7, 1972 · N35124 · Substantial damage NTSB MKC72DTK17Accident · No injuriesClosedAeronca 7BCM · Huron, SDJan 6, 1972 · N4256E · Substantial damage NTSB MKC72DTK16Accident · No injuriesClosedCessna 182F · Marion, SDJan 3, 1972 · N3523U · Substantial damage NTSB MKC72DTK15Accident · No injuriesClosedCessna 172I · Aberdeen, SDDec 30, 1971 · N8477L · Substantial damage NTSB MKC72DTK14Accident · No injuriesClosedAeronca 7AC · De Smet, SDDec 28, 1971 · N3731E · Substantial damage NTSB MKC72DTK13Accident · No injuriesClosedPiper PA-11 · Miller, SDDec 24, 1971 · N78636 · Substantial damage NTSB MKC72DTK12Accident · No injuriesClosedMooney M20A · Harrington, SDDec 4, 1971 · N8360E · Substantial damage NTSB MKC72FTK11Accident · Serious injuriesClosedBeech S35 · Pierre, SDNov 25, 1971 · N198Q · Substantial damage NTSB MKC72DTK10Accident · No injuriesClosedBeech S35 · Ipswich, SDNov 18, 1971 · N8657Q · Substantial damage NTSB MKC72DTK09Accident · No injuriesClosedPiper PA-24 · Pierre, SDOct 7, 1971 · N7843P · Substantial damage NTSB MKC72AK023Accident · 4 fatalClosedBeech C35 · Hartford, SDOct 1, 1971 · N8953A · Destroyed NTSB MKC72FTK08Accident · No injuriesClosedCessna 150 · Columbia, SDSep 22, 1971 · N5904E · Substantial damage NTSB MKC72FTK07Accident · Serious injuriesClosedPiper PA-18 · Philip, SDSep 6, 1971 · N3927Z · Substantial damage NTSB MKC72FTK06Accident · No injuriesClosedAmer Avco AA1-A · Rapid City, SDAug 25, 1971 · N9341L · Substantial damage NTSB MKC72FTK04Accident · No injuriesClosedCessna 195 · Rapid City, SDAug 19, 1971 · N9820A · Substantial damage NTSB MKC72DTK05Accident · No injuriesClosedCessna 188 · Wagner, SDAug 11, 1971 · N8045V · Substantial damage NTSB MKC72AK012Accident · 1 fatalClosedPiper PA-32 · Andover, SDAug 11, 1971 · N4029R · Destroyed NTSB MKC72FTK02Accident · No injuriesClosedPiper PA-25 · Dixon, SDAug 9, 1971 · N7579Z · Substantial damage NTSB MKC72AK003Accident · 3 fatalClosedCessna 172E · Montrose, SDJul 10, 1971 · N3936S · Destroyed NTSB MKC72DTK01Accident · No injuriesClosedPiper PA-18 · Hayes, SDJul 9, 1971 · N6884B · Substantial damage NTSB MKC72AK004Accident · Serious injuriesClosedGrumman G-159 · Dupree, SDJul 9, 1971 · N944H · Minor damage NTSB MKC71DTK34Accident · No injuriesClosedCessna 170A · Hot Springs, SDJun 26, 1971 · N9150A · Substantial damage NTSB MKC71FTK32Accident · No injuriesClosedGlobe GC-1B · Rapid City, SDJun 21, 1971 · N3366K · Substantial damage NTSB MKC71FTK33Accident · Serious injuriesClosedPiper PA-25 · Onida, SDJun 17, 1971 · N7007Z · Substantial damage NTSB MKC71DTK31Accident · No injuriesClosedPiper PA-28 · Parker, SDMay 21, 1971 · N4588R · Substantial damage NTSB MKC71DTK29Accident · No injuriesClosedAlouette 2 · Lake Norden, SDMay 16, 1971 · N8398 · Substantial damage NTSB MKC71FTK30Accident · Minor injuriesClosedCallair A-9 · Philip, SDMay 15, 1971 · N7288V · Substantial damage NTSB MKC71DTK28Accident · No injuriesClosedCessna 120 · Tea, SDMay 3, 1971 · N1966N · Substantial damage NTSB MKC71DDQ27Accident · Minor injuriesClosedPiper PA-28 · Blunt, SDApr 18, 1971 · N3718T · Substantial damage NTSB MKC71DDQ26Accident · No injuriesClosedCessna 170 · Wsngtn Spgs, SDMar 29, 1971 · N2419D · Substantial damage NTSB MKC71DDQ24Accident · No injuriesClosedPiper PA-22 · Camp Crook, SDJan 29, 1971 · N9169D · Substantial damage NTSB MKC71AK040Accident · 1 fatalClosedMooney M20E · Rapid City, SDJan 18, 1971 · N9273V · Destroyed NTSB MKC71FDQ23Accident · Minor injuriesClosedCessna 172 · Sioux Falls, SDJan 13, 1971 · N4444L · Substantial damage NTSB MKC71FDQ22Accident · Serious injuriesClosedAeronca 7AC · Edgemont, SDDec 25, 1970 · N2968E · Substantial damage NTSB MKC71DDQ21Accident · No injuriesClosedCessna 310D · Mitchell, SDDec 18, 1970 · N6977T · Substantial damage NTSB MKC71DDQ20Accident · No injuriesClosedAeronca 7GC-3 · Tea, SDDec 13, 1970 · N7523E · Substantial damage NTSB MKC71DDQ19Accident · No injuriesClosedPiper PA-18 · Onida, SDDec 12, 1970 · N4220Z · Substantial damage NTSB MKC71AK032Accident · 1 fatalClosedPiper PA-22 · Rapid City, SDDec 9, 1970 · N6920B · Destroyed NTSB MKC71AK031Accident · Minor injuriesClosedPiper PA-28 · Rapid City, SDDec 1, 1970 · N8569W · Substantial damage NTSB MKC71DDQ18Accident · No injuriesClosedCessna 310 · Mission, SDOct 28, 1970 · N5292A · Substantial damage NTSB MKC71DDQ17Accident · Minor injuriesClosedCessna 170 · Hartford, SDOct 10, 1970 · N6848D · Substantial damage NTSB MKC71FDQ16Accident · No injuriesClosedBeech 95-B55 · Sturgis, SDOct 7, 1970 · N8685Q · Substantial damage