I have a table that stores wind speed and wind direction over time.
The table stores a new row every 10 minutes exactly.
The wind direction are always fixed in 16 different values from 0 to 360.
(0, 22.5, 45, 67.5, ... 337.5)
date | avg_speed | wind_dir
2011-01-01 00:00:00 | 6.24 | 45
2011-01-01 00:10:00 | 5.00 | 22.5
2011-01-01 00:20:00 | 3.38 | 22.5
2011-01-01 00:30:00 | 4.16 | 45
I need to generate a table to create a windrose chart showing the number of registers of wind for each direction grouped in wind ranges:
Hours of wind per direction in "ranges" of:
0-2m/s => In SQL : avg_speed >= 0 and avg_speed < 3
2-4m/s => In SQL : avg_speed >= 3 and avg_speed < 5
4-6m/s => ....
To explain it better, here's a "sample" of the resulting chart (this doesn't have the wind ranges I need as you can see).
I've tried to do it using php and sql, with a couple "for" loops that executes a SQL for each wind direction and for each wind range. That makes the process extremely long and overloads the sql server as the table can be very big.
This is (in simplified code) what I do:
for wind_step = 0 to 360 step 22.5 do
foreach ranges as range
SELECT wind_dir, COUNT(wind_dir) AS regs
WHERE wind_dir = wind_step
AND range (for example avg_speed >= 0 and avg_speed <= 2)
GROUP BY wind_dir
data[wind_dir][range_index] = table with result of select
And I'm sure there is a way to do it with just SQL, but my SQL knowledge doesn't reach so far
Any help will be greatly appreciated, thanks