-- Extract the protein coordinates with the year of the strain as a
-- feature for analysis in ParaView.

EXPORT TO proteins_by_year.csv OF DEL
SELECT C.x,
       C.y,
       0,
       S.strain_year,
       C.gi
FROM influenza_meta M
JOIN strains_parsed S
ON   (S.GBSeq_accession_version = M.gbseq_accession_version)
JOIN coordinates C
ON   (C.gi = 'gi|' || M.gi)
WHERE S.strain_year IS NOT NULL
ORDER BY S.strain_year;

-- Records with invalid years manually removed from the head and tail
-- of the exported file.