NTSB investigations: Piper AEROSTAR 601P

64 cases on aircraft the registry lists as Piper AEROSTAR 601P. Aircraft are matched to the registry by N-number and event date, checked against the reported serial number, make and model, so older matches can be wrong.

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."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('7106012')) 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.\"mKey\" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('7106012'))
  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."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('7106012'))
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 CHI83LA040Accident · Serious injuriesLimited investigationFinal reportPiper 601P · Sheboygan, WIThe plt noticed haze in lights at destination but had excellent ground contact throughout the approach. At threshold he entered dense ground fog & lost all visual contact. The acft crashed during the go-around.Nov 18, 1982 · N6078U · Destroyed NTSB LAX83FA035Accident · No injuriesField investigationFinal reportPiper 601P · Tonopah, NVAs the acft reached 15,500 ft, aprx 30 mi from the dep arpt, the plt & pax noted fumes that irritated the eyes & caused dizziness & breathing difficulties. The plt reversed course, then elected to make an emerg lndg on a dirt road. Drg the lndg roll-out, the…Nov 17, 1982 · N3642A · Substantial damage NTSB LAX82FA275Accident · 2 fatalField investigationFinal reportPiper PA-601P · South Of Wicken, AZAt about 1005 MST the plt received a wx briefing for a flt to Florida with an overnight stay in Texas. Thunderstorm activity was forecast along the proposed route of flt. The briefer advised the plt to get an updated briefing before departure because of the…Jul 24, 1982 · N3642T · Destroyed NTSB DEN81FTE56Accident · Minor injuriesClosedPiper 601P · Crested Butte, COJul 9, 1981 · N36342 · Substantial damage NTSB ATL81FA057Accident · 1 fatalClosedAerostar 601P · Roanoke, VAMar 23, 1981 · N3641U · Destroyed NTSB NYC81FNE03Accident · No injuriesClosedAerostar 601P · Worcester, MANov 18, 1980 · N6075U · Substantial damage NTSB MIA81FA007Accident · 2 fatalClosedPiper 601P · Wilma, FLOct 23, 1980 · N8250J · Destroyed NTSB ATL81FKQ01Accident · Minor injuriesClosedPiper 601P · Greenwood, MSOct 13, 1980 · N3634F · Substantial damage NTSB LAX80FA045Accident · 2 fatalClosedAerostar 601P · San Jose, CAFeb 4, 1980 · N8078J · Destroyed NTSB NYC80FA010Accident · 3 fatalClosedAerostar 601P · Charlstadt, NJNov 16, 1979 · N8099J · Destroyed NTSB LAX79AA075Accident · No injuriesClosedAerostar 601P · Santa Ynez, CAAug 23, 1979 · N6071M · Substantial damage NTSB CHI79FA031Accident · 3 fatalClosedAerostar 601P · Columbus, OHMar 14, 1979 · N8061J · Destroyed NTSB LAX77FA014Accident · 5 fatalClosedAerostar 601P · Costa Mesa, CADec 5, 1976 · N7485S · Destroyed NTSB LAX68F0270Accident · No injuriesClosedAero Comdr 680 · Van Nuys, CADec 15, 1967 · N680S · Substantial damage