Welcome to the new home of the Cumulus Support forum.

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

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

Discussion of Mark Crossley's HTML5/Javascript gauges

Moderator: mcrossley

Post Reply
User avatar
gwheelo
Posts: 453
Joined: Wed 11 Jun 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

Post by gwheelo » Sun 31 Jul 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/me ... _rose.html and the local 9 sector version - http://www.wheelocknet.net/medes_cam/me ... _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: 5210
Joined: Thu 07 Jan 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

Post by mcrossley » Sun 31 Jul 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: 5210
Joined: Thu 07 Jan 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

Post by mcrossley » Sun 31 Jul 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 11 Jun 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

Post by gwheelo » Mon 01 Aug 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: 5210
Joined: Thu 07 Jan 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

Post by mcrossley » Mon 01 Aug 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: 135
Joined: Tue 19 Jun 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

Post by jdc » Mon 01 Aug 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 11 Jun 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

Post by gwheelo » Tue 02 Aug 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/me ... _rose.html.

Thanks for the help.

GW
Image

User avatar
mcrossley
Posts: 5210
Joined: Thu 07 Jan 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

Post by mcrossley » Tue 02 Aug 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 11 Jun 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

Post by gwheelo » Tue 02 Aug 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: 5210
Joined: Thu 07 Jan 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

Post by mcrossley » Wed 03 Aug 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 11 Jun 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

Post by gwheelo » Thu 04 Aug 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

Post Reply