#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Location
    Zaragoza (Spain)
    Posts
    8
    Rep Power
    0

    Problems getting data for a windrose chart with speed ranges


    Hello!

    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)

    Code:
    Table: wind_data
    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 => ....
    6-10m/s ...
    10-12m/s ...
    12-99m/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).
    http://jpgraph.net/images/gallery/wind1.png


    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:

    Code:
    for wind_step = 0 to 360 step 22.5 do
        foreach ranges as range
    		SELECT wind_dir, COUNT(wind_dir) AS regs
    		FROM wind_data
    		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
    	end
    end
    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by asejua
    0-2m/s => In SQL : avg_speed >= 0 and avg_speed <= 2
    3-4m/s => In SQL : avg_speed >= 3 and avg_speed <= 4
    you do realize that these ranges as you have given them will skip over values like 2.5, right?

    what you want is an inclusive lower bound and an exclusive upper bound

    0-2m/s => In SQL : avg_speed >= 0 and avg_speed < 3
    3-4m/s => In SQL : avg_speed >= 3 and avg_speed < 5

    what you want to do next is store these ranges in a small table, with three columns:

    - lower bound (will be inclusive)
    - upper bound (will be exclusive)
    - descr for this range

    then it's a simple matter of a join, which i can help you with once you've created the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Location
    Zaragoza (Spain)
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    you do realize that these ranges as you have given them will skip over values like 2.5, right?
    My fault, you're right, wrote the post too quick the checks are as you say in my current code.

    First of all, thanks for replying ^^

    Okay, I've created a table with:

    Code:
    Table : wind_ranges
    lbound | hbound | desc
    0      | 3      | "0 to 2 m/s"
    3      | 5      | "3 to 4 m/s"
    ...
    Still a bit lost with the final sql query... what'd be the next step?

    Regards.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    SELECT d.wind_dir
         , r.desc
         , COUNT(*) AS regs
      FROM wind_data AS d
    INNER
      JOIN wind_ranges AS r
        ON r.lbound <= d.avg_speed
       AND             d.avg_speed < r.hbound 
    GROUP
        BY d.wind_dir
         , r.desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Location
    Zaragoza (Spain)
    Posts
    8
    Rep Power
    0
    Wow, simple and effective

    You've saved my neck

    Thank you so much!
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0

    Smile


    Originally Posted by r937
    Code:
    SELECT d.wind_dir
         , r.desc
         , COUNT(*) AS regs
      FROM wind_data AS d
    INNER
      JOIN wind_ranges AS r
        ON r.lbound <= d.avg_speed
       AND             d.avg_speed < r.hbound 
    GROUP
        BY d.wind_dir
         , r.desc
    Nifty code!

    I know it has been 2 years since this solution was given but could you please help me modify it to get percentages instead of a count of registers?

    Thanks in advance!
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    Code:
    SELECT d.wind_dir
         , r.desc
         , COUNT(*) AS regs
      FROM wind_data AS d
    INNER
      JOIN wind_ranges AS r
        ON r.lbound <= d.avg_speed
       AND             d.avg_speed < r.hbound 
    GROUP
        BY d.wind_dir
         , r.desc
    Based on your code, I tried to implement something. Just modified the third line
    Code:
    SELECT d.wind_dir
         , r.desc
         , COUNT(*) / (SELECT COUNT(*) FROM wind_data) * 100 AS percent
      FROM wind_data AS d
    INNER
      JOIN wind_ranges AS r
        ON r.lbound <= d.avg_speed
       AND             d.avg_speed < r.hbound 
    GROUP
        BY d.wind_dir
         , r.desc
    Is this still correct and efficient?

    Thanks!
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Axcoder
    Is this still correct and efficient?
    looks okay to me... what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0
    It showed some data; looks accurate to me. Thanks a bunch! That initial code of yours is amazing and so useful!

IMN logo majestic logo threadwatch logo seochat tools logo