NTSB investigations: Continental A&C65 series, IA

61 cases on aircraft whose registry engine model is Continental A&C65 series. 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."eventStateOrRegion" = 'IA' AND c."eventCountry" = 'USA' AND c."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.engine_model_code IN ('17003')) 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\" = 'IA' AND c.\"eventCountry\" = 'USA'
    AND c.\"mKey\" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.engine_model_code IN ('17003'))
  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" = 'IA' AND c."eventCountry" = 'USA'
  AND c."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.engine_model_code IN ('17003'))
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 MKC68A0008Accident · 1 fatalClosedTaylorcraft BC12-D · Le Mars, IAAug 1, 1967 · N43350 · 2 aircraft · Destroyed NTSB MKC68D0002Accident · Minor injuriesClosedLuscombe 8E · Grundy Centr, IAMay 31, 1967 · N28612 · Substantial damage NTSB MKC68D0006Accident · No injuriesClosedPiper J-3 · Denison, IAMay 21, 1967 · N24603 · Substantial damage NTSB MKC67F0199Accident · Minor injuriesClosedAeronca 058B · Essex, IASep 5, 1966 · N48243 · Substantial damage NTSB MKC67D0168Accident · No injuriesClosedAeronca 11AC · Imogene, IAJul 24, 1966 · N3223E · Substantial damage NTSB MKC66D0362Accident · No injuriesClosedTaylorcraft L-2 · Adair, IANov 21, 1965 · N48247 · Substantial damage NTSB MKC65D0127Accident · No injuriesClosedTaylorcraft DCO · Pacific Junct, IAMay 3, 1965 · N57922 · Destroyed NTSB UNK64X2151Accident · Minor injuriesClosedAeronca 7AC · Red Oak, IAJun 6, 1964 · N84230 · Destroyed NTSB UNK64X0781Accident · No injuriesClosedLuscombe 8A · Mapleton, IAMar 1, 1964 · N71760 · Substantial damage NTSB UNK64X0398Accident · No injuriesClosedAeronca 7AC · Washburn, IAJan 29, 1964 · N82003 · Substantial damage NTSB UNK64X0349Accident · No injuriesClosedLuscombe 8A · Des Moines, IAJan 25, 1964 · N45965 · Substantial damage