timtrice/datasets

Add SQL View `ace` to HURDAT

Closed this issue · 1 comments

Add code to HURDAT/sql/HURDAT.sql output:

CREATE VIEW `ace` AS 
SELECT `t1`.`storm_key` AS `storm_key`,
  (SUM(`t1`.`wind_squared`) / 10000) AS `ace` 
FROM (
  SELECT `HURDAT`.`HURDAT`.`storm_key` AS `storm_key`,
    POW(`HURDAT`.`HURDAT`.`wind`,2) AS `wind_squared` 
  FROM `HURDAT`.`HURDAT` 
  WHERE ((`HURDAT`.`HURDAT`.`wind` > 35) 
  AND (hour(`HURDAT`.`HURDAT`.`date_time`) in (0,6,12,18)) 
  AND (`HURDAT`.`HURDAT`.`status` in ('TS','HU')))) `t1` 
GROUP BY `t1`.`storm_key`

Have removed SQL statements.