Please read the posts in the Announcements section about the current status of Cumulus development now that I have retired

Please read this post before posting

Latest Cumulus release v1.9.4 (build 1099) - Nov 28 2014
Latest Cumulus MX release - v3.0.0 build 3043 Jan 20 2017. See this post for download

Windrose with nine sectors - advise, help, direction - help

Discussion of Mark Crossley's HTML5/Javascript gauges

Moderator: mcrossley

User avatar
gwheelo
Posts: 453
Joined: Wed Jun 11, 2008 7:36 pm
Weather Station: WMR-88
Operating System: Windows 8.1
Location: L'Estartit, Spain
Contact:

Windrose with nine sectors - advise, help, direction - help

Postby gwheelo » Sun Jul 31, 2016 3:56 am

Looking for some direction, recommendations, and assistance with a "modified Windrose"

Background - My station is in the north east corner of Spain where dominant wind direction has its own unique nomenclature. Instead of the usual 12 sectors (N, NNE, NE...) the locals use nine sectors each with a unique arc and name (Provences - 31°, Gregal - 33°, LLevant - 34°, Xaloc - 46° ...... )

I display both the standard rose - http://www.wheelocknet.net/medes_cam/medes_standard_rose.html and the local 9 sector version - http://www.wheelocknet.net/medes_cam/medes_catalan_rose.html on my site.

Needless to say I shamelessly use Mark's Windrose code almost verbatim for the standard rose and a modified version of Mark's code to drive a D3 chart for the 'Catalan" version.

The data for the nine Catalan sectors is interpreted via a complex of the standard realtime.txt and a couple of php scripts that combime output from an Oregon WM88 and a WM968 which provides a full 360 degree wind direction output.

The WM-968 is dying and I will soon be forced to rely on the WM-88 which provides only the twelve standard compass sectors.

What I hope to do is use the 'avgbearing' data which provides a ten minute wind bearing average which I would like to use for the sector arc data - 'averagebearing' of 1° to 30° = 'Provences', 'averagebearing of 99° - 144° = 'Xaloc' ...

The big problem, for me, is once I fetch a range of data from mysql I need to substitute the nine sector names for the appropriate avgbearing data in each row. How do I do that or does someone have a more creative approach.

I understand that I may be the only Cumulus user with this particular requirement so I understand there will be a huge lack of community interest. But I also know there are many creative members on this forum that enjoy odd, arcane, and challenging problems. Any level of advise, direction, and hints will be most appreciated.

George Wheelock
http://www.wheelocknet.net/medes_cam/medes_home.html
Image

User avatar
mcrossley
Posts: 5192
Joined: Thu Jan 07, 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby mcrossley » Sun Jul 31, 2016 10:30 pm

If you craft a SQL query to return all nine directions (even if some of them are null), then you can just loop through the rows returned and put the data into the appropriate place?

Does this work for you...

Code: Select all

SELECT
    dir, cnt
FROM
(
    SELECT 'Provences' AS dir, 0
    UNION SELECT 'Gregal', 0
    UNION SELECT 'Llevant', 0
    UNION SELECT 'Xaloc', 0
    UNION SELECT 'Garbi', 0
    UNION SELECT 'Llebeig', 0
    UNION SELECT 'Ponent', 0
    UNION SELECT 'Mestral', 0
    UNION SELECT 'Tramuntana', 0
    UNION SELECT 'Calm', 0
) A
LEFT JOIN
(
    SELECT
        CASE
            WHEN avgbearing BETWEEN 1 AND 31 THEN 'Provences'
            WHEN avgbearing BETWEEN 32 AND 64 THEN 'Gregal'
            WHEN avgbearing BETWEEN 65 AND 98 THEN 'Llevant'
            WHEN avgbearing BETWEEN 99 AND 144 THEN 'Xaloc'
            WHEN avgbearing BETWEEN 145 AND 210 THEN 'Garbi'
            WHEN avgbearing BETWEEN 211 AND 249 THEN 'Llebeig'
            WHEN avgbearing BETWEEN 250 AND 294 THEN 'Ponent'
            WHEN avgbearing BETWEEN 295 AND 324 THEN 'Mestral'
            WHEN avgbearing BETWEEN 325 AND 360 THEN 'Tramuntana'
            ELSE 'Calm'
        END AS dir,
        COUNT(*) AS cnt
    FROM realtime
    WHERE LogDateTime >= now() - INTERVAL 24 HOUR
    GROUP BY dir
) B USING (dir)

User avatar
mcrossley
Posts: 5192
Joined: Thu Jan 07, 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby mcrossley » Sun Jul 31, 2016 10:33 pm

Ah, but then you don't want just the count of each direction, you want them binned by speed - argh! :bash:

Ignore the post above.

User avatar
gwheelo
Posts: 453
Joined: Wed Jun 11, 2008 7:36 pm
Weather Station: WMR-88
Operating System: Windows 8.1
Location: L'Estartit, Spain
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby gwheelo » Mon Aug 01, 2016 2:17 am

argh!
Yes -that is the exact moment I knew it was time to ask the forum!

GW
Image

User avatar
mcrossley
Posts: 5192
Joined: Thu Jan 07, 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby mcrossley » Mon Aug 01, 2016 2:48 pm

I'd either create a stored procedure to run the 9 queries, then loop through the result sets in PHP. Or just do the query 9 times in PHP - put it in a function with the from/to as parameters.

User avatar
jdc
Posts: 134
Joined: Tue Jun 19, 2012 8:51 pm
Weather Station: Davis VP2 : Instromet
Operating System: Win 10 : Win XP
Location: Berwick-upon-Tweed, England.
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby jdc » Mon Aug 01, 2016 3:51 pm

I'm probably misunderstanding this, but could you not process locally before upload and just store in the database as say, 1 to 9? On extraction, the names could be assigned.

User avatar
gwheelo
Posts: 453
Joined: Wed Jun 11, 2008 7:36 pm
Weather Station: WMR-88
Operating System: Windows 8.1
Location: L'Estartit, Spain
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby gwheelo » Tue Aug 02, 2016 7:40 pm

could you not process locally before upload


Yes John - you found the simpler solution. So I modified my original script which was combining the wind data from the WM-968 with the remaining data from the WM-88 for the realtime.txt input to my mysql table using the avgbearing tag to produce the 1-9 digits which will map to the Catalan wind names on the rose. You can see it in action here: http://www.wheelocknet.net/medes_cam/medes_catalan_rose.html.

Thanks for the help.

GW
Image

User avatar
mcrossley
Posts: 5192
Joined: Thu Jan 07, 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby mcrossley » Tue Aug 02, 2016 8:12 pm

Obvious 'init! :bash:
Though I would have probably used an insert trigger on the table to populate the custom direction field.

User avatar
gwheelo
Posts: 453
Joined: Wed Jun 11, 2008 7:36 pm
Weather Station: WMR-88
Operating System: Windows 8.1
Location: L'Estartit, Spain
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby gwheelo » Tue Aug 02, 2016 8:47 pm

Though I would have probably used an insert trigger on the table to populate the custom direction field.


Hi Mark - If a 'trigger' is a better, more direct method I'm listening. I have no idea what a trigger is!

GW
Image

User avatar
mcrossley
Posts: 5192
Joined: Thu Jan 07, 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby mcrossley » Wed Aug 03, 2016 2:00 pm

You can define triggers on your MySQL table. For instance I have added a 10 minute rolling average UV-I to mine. To do this I added the column UV_AVG to the table, then creating a pre-insert trigger as below...

Code: Select all

BEGIN
   SET NEW.UV_AVG = (
      SELECT ROUND((SUM(t1.UV)+NEW.UV)/(COUNT(t1.UV)+1),1)
      FROM (
         SELECT UV
         FROM realtime
         WHERE LogDateTime >= DATE_SUB(NEW.LogDateTime, INTERVAL 9 MINUTE)
      ) t1
   );
END

This populates the UV_AVG value whenever a new row is added to the table.
You can do maths and case statements rather than selects if you need to.

User avatar
gwheelo
Posts: 453
Joined: Wed Jun 11, 2008 7:36 pm
Weather Station: WMR-88
Operating System: Windows 8.1
Location: L'Estartit, Spain
Contact:

Re: Windrose with nine sectors - advise, help, direction - h

Postby gwheelo » Thu Aug 04, 2016 3:17 am

You can define triggers on your MySQL table.


Thanks Mark!

I will start learning all about 'triggers'. I love it that Cumulus, SS Gauges, and the members of this forum have continually set me on a course of new learning opportunities. .... And provided advise and nudges whenever needed.

Thank you all very much.

GW
Image


Return to “SteelSeries Gauges”

Who is online

Users browsing this forum: No registered users and 3 guests