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 MKC71DDQ15Accident · No injuriesClosedPiper PA-28 · Winner, SDOct 4, 1970 · N9156J · Substantial damage NTSB MKC71FDQ13Accident · No injuriesClosedPiper PA-18 · Pierre, SDSep 30, 1970 · N9454D · Substantial damage NTSB MKC71FDQ11Accident · Minor injuriesClosedAeronca 7AC · Rapid City, SDSep 19, 1970 · N82576 · Substantial damage NTSB MKC71DDQ12Accident · No injuriesClosedCessna 120 · Harrold, SDSep 19, 1970 · N3141N · Substantial damage NTSB MKC71FDQ10Accident · No injuriesClosedBell 47G-2 · Keystone, SDAug 18, 1970 · N5163B · Substantial damage NTSB MKC71FDQ09Accident · No injuriesClosedAeronca 7GCB · Sturgis, SDAug 17, 1970 · N9939Y · Substantial damage NTSB MKC71DDQ08Accident · No injuriesClosedPiper PA-22 · Chamberlain, SDAug 16, 1970 · N4545Z · Substantial damage NTSB MKC71DDQ07Accident · No injuriesClosedPiper PA-11 · Hayes, SDAug 16, 1970 · N5567H · Substantial damage NTSB MKC71DDQ06Accident · No injuriesClosedCessna 170B · Wilmot, SDAug 15, 1970 · N4383B · Substantial damage NTSB MKC71DDQ05Accident · No injuriesClosedPiper PA-18 · Opal, SDAug 7, 1970 · N7113K · Substantial damage NTSB MKC71DDQ04Accident · No injuriesClosedCessna 150 · Warner, SDAug 5, 1970 · N50376 · Substantial damage NTSB MKC71FDQ03Accident · No injuriesClosedPiper PA-22 · Murdo, SDJul 25, 1970 · N3933P · Substantial damage NTSB MKC71DDQ02Accident · No injuriesClosedPiper PA-18 · Ludlow, SDJul 6, 1970 · N1637A · Substantial damage NTSB MKC71DDQ01Accident · No injuriesClosedPiper PA-18A · White Lake, SDJul 2, 1970 · N7072B · Substantial damage NTSB MKC70DDQ11Accident · No injuriesClosedCessna 182 · Willow Lake, SDJun 19, 1970 · N2426R · Substantial damage NTSB MKC70FDQ10Accident · Serious injuriesClosedAeronca 7CCM · Hayes, SDJun 17, 1970 · N82616 · Substantial damage NTSB MKC70FDQ09Accident · 1 fatalClosedCallair A-9 · Madison, SDJun 11, 1970 · N9653Z · Destroyed NTSB MKC70FDQ08Accident · No injuriesClosedMooney M20C · Miller, SDMay 30, 1970 · N2937L · Substantial damage NTSB MKC70DDQ07Accident · Minor injuriesClosedBeech 35 · Milbank, SDApr 26, 1970 · N2740V · Substantial damage NTSB MKC70DDQ06Accident · No injuriesClosedCessna 180 · Watertown, SDApr 25, 1970 · N9033C · Substantial damage NTSB MKC70AK058Accident · 2 fatalClosedMooney M20E · Winner, SDApr 22, 1970 · N5949Q · Destroyed NTSB MKC70AK056Accident · 2 fatalClosedMooney M20C · Rosholt, SDApr 14, 1970 · N6759U · Destroyed NTSB MKC70DDQ05Accident · No injuriesClosedCessna 172K · Isabel, SDFeb 22, 1970 · N79445 · Substantial damage NTSB MKC70DDQ04Accident · Minor injuriesClosedPiper PA-24 · Onida, SDJan 29, 1970 · N6616P · Substantial damage NTSB MKC70DDQ12Accident · No injuriesClosedPiper PA-18 · Altamont, SDJan 25, 1970 · N1256A · Substantial damage NTSB MKC70DDQ03Accident · No injuriesClosedPiper PA-12 · Kennebec, SDJan 22, 1970 · N2998M · Substantial damage NTSB MKC70DDQ02Accident · No injuriesClosedPiper PA-18 · Mobridge, SDJan 15, 1970 · N8176D · Substantial damage NTSB MKC70DDQ01Accident · No injuriesClosedCessna 150 · Miller, SDJan 13, 1970 · N2870S · Substantial damage NTSB MKC70F0314Accident · Minor injuriesClosedCessna 177 · Newell, SDDec 29, 1969 · N3427T · Substantial damage NTSB MKC70A0037Accident · 2 fatalClosedPiper PA-30 · Vermillion, SDDec 29, 1969 · N7201Y · Destroyed NTSB MKC70F0274Accident · No injuriesClosedPiper PA-22 · Oglala, SDDec 6, 1969 · N8687C · Substantial damage NTSB MKC70F0269Accident · Minor injuriesClosedTaylorcraft BC12-D · Brandon, SDNov 25, 1969 · N43314 · Substantial damage NTSB MKC70F0266Accident · No injuriesClosedPiper J3C-65 · Midland, SDNov 10, 1969 · N87924 · Substantial damage NTSB MKC70F0260Accident · No injuriesClosedPiper PA-18 · Pierre, SDNov 8, 1969 · N3817Z · Substantial damage NTSB MKC70F0248Accident · Minor injuriesClosedLuscombe 8A · Sioux Falls, SDNov 8, 1969 · N9905C · Substantial damage NTSB MKC70F0247Accident · No injuriesClosedPiper PA-24 · Spearfish, SDOct 29, 1969 · N8229P · Destroyed NTSB MKC70F0246Accident · No injuriesClosedBeech A23 · Coleman, SDSep 30, 1969 · N3560R · Substantial damage NTSB MKC70A0009Accident · 3 fatalClosedBell 47G 2 · Keystone, SDSep 4, 1969 · N28208 · Destroyed NTSB MKC70F0225Accident · No injuriesClosedCessna 180 · Rapid City S Dak, SDAug 31, 1969 · N1504C · Substantial damage NTSB MKC70F0182Accident · Minor injuriesClosedPiper PA-28 · Cottonwood, SDAug 31, 1969 · N7875W · Substantial damage NTSB MKC70D0172Accident · No injuriesClosedChampion 7EC · Pierre, SDAug 29, 1969 · N8973R · Substantial damage NTSB MKC70D0154Accident · No injuriesClosedPiper PA-18 · Buffalo Gap, SDAug 23, 1969 · N289T · Substantial damage NTSB MKC70F0103Accident · No injuriesClosedBeech 35 · Sioux Falls, SDAug 5, 1969 · N2795V · Substantial damage NTSB MKC70D0102Accident · No injuriesClosedPiper PA-25 · Doland, SDAug 4, 1969 · N4897Y · Substantial damage NTSB MKC70I0081Incident · No injuriesClosedBell 47G3B1 · Hill City, SDAug 2, 1969 · N7924S · Minor damage NTSB MKC70F0071Accident · Minor injuriesClosedAero Comdr A-9B · Colome, SDJul 22, 1969 · N7687V · Substantial damage NTSB MKC70F0045Accident · No injuriesClosedPiper PA-28 · Rapid City, SDJul 10, 1969 · N8927J · Substantial damage NTSB MKC70F0086Accident · 1 fatalClosedPiper PA-18A · Vermillion, SDJul 4, 1969 · N8153C · Substantial damage NTSB MKC70D0072Accident · No injuriesClosedPiper PA-18 · Camp Crook, SDJul 3, 1969 · N1190A · Substantial damage NTSB MKC70D0047Accident · No injuriesClosedLuscombe 8E · Dempster, SDJun 29, 1969 · N1820K · Substantial damage