gistfile1.sql

DROP TEMPORARY TABLE IF EXISTS siteswith30days;
CREATE TEMPORARY TABLE siteswith30days SELECT r2.siteid, COUNT(*) as totalrows FROM rawdata AS r2 GROUP BY siteid HAVING totalrows > 6*24*30;
SELECT
	r.*, 
	(r.winddirection % 1024) * (360/1024) AS winddirectiondegrees,
	0.8669 * (r.windpulsecount/600) + 0.32 AS windspeed,
	s.postcode,
	s.anemometerheight
FROM 
	rawdata AS r 
	INNER JOIN sites AS s USING (siteid)
	INNER JOIN siteswith30days USING (siteid)
WHERE 
	s.country="GB" AND 
	s.anemometerheight = 12
ORDER BY s.siteid, r.sampledate
INTO OUTFILE "/tmp/suzie-data.csv";