Page 1 of 1

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

Posted: Sun 31 Jul 2016 3:56 am
by gwheelo
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

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

Posted: Sun 31 Jul 2016 10:30 pm
by mcrossley
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)

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

Posted: Sun 31 Jul 2016 10:33 pm
by mcrossley
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.

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

Posted: Mon 01 Aug 2016 2:17 am
by gwheelo
argh!
Yes -that is the exact moment I knew it was time to ask the forum!

GW

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

Posted: Mon 01 Aug 2016 2:48 pm
by mcrossley
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.

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

Posted: Mon 01 Aug 2016 3:51 pm
by jdc
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.

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

Posted: Tue 02 Aug 2016 7:40 pm
by gwheelo
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

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

Posted: Tue 02 Aug 2016 8:12 pm
by mcrossley
Obvious 'init! :bash:
Though I would have probably used an insert trigger on the table to populate the custom direction field.

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

Posted: Tue 02 Aug 2016 8:47 pm
by gwheelo
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

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

Posted: Wed 03 Aug 2016 2:00 pm
by mcrossley
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.

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

Posted: Thu 04 Aug 2016 3:17 am
by gwheelo
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