MX SQL query

[sfws]

I have said many times in previous posts, that I use a different schema for my database that makes better use of all the data that Cumulus can provide.
Since I swapped to MX it has continued to run my external PHP script that does a certain amount of validation to ensure I don't have a "garbage in, garbage out" situation and none of my scripts retrieving records from the database have to do any validation of what they read. That script continues to update my daily_summary table and when relevant calls other scripts that update my monthly-summary table (think of that as an expanded thismonth web page).

Anyway, I have also been trying the custom SQL EOD option in MX. As recorded in another topic, initially it was storing rows with the wrong date for primary key, but I solved that by quoting the web tag for yesterday to be the primary key. The following SQL is successfully updating all but one of the columns in my test version of my daily table. The exception is a column that is also in Mark's standard schema, the compass symbol for the strongest wind gust. It does not appear as a web tag, so how do I add a conversion from bearing in degrees to an abbreviated compass direction (e.g. SSW) in my SQL? Alternatively, can it be added as a standard web tag?

INSERT IGNORE INTO `test_daily_summary` (`MaxRainRate`, `TMaxRainRate`, `HighHourRain`, `THighHourRain`, `TotRainFall`, `SnowFalling`, `SnowLying`, `SnowDepth`, `CumChillHours`, `LogDate`, `RollOver`, `MinTemp`, `TMinTemp`, `HeatDegDays`, `AvgTemp`, `MaxTemp`, `TMaxTemp`, `CoolDegDays`, `LowDewPoint`, `TLowDewPoint`, `LowHum`, `TLowHum`, `HighHum`, `THighHum`, `HighDewPoint`, `THighDewPoint`, `GreatWindChill`, `TGreatWindChill`, `LowAppTemp`, `TLowAppTemp`, `HighAppTemp`, `THighAppTemp`, `HighHeatInd`, `THighHeatInd`, `MinPress`, `TMinPress`, `MaxPress`, `TMaxPress`, `HighAvgWSpeed`, `THighAvgWSpeed`, `StrongestWindGust`, `TStrongestWindGust`, `BearStrongestWindGust`, `BearDomWind`, `BearDomWindSym`, `TotWindRun`) VALUES ('<#rrateTM>', '<#TrrateTM>', '<#hourlyrainTH>', '<#ThourlyrainTH>', '<#rfall> ', '<#snowfalling>', '<#snowlying>', '<#snowdepth>', '<#chillhours>', '<#metdateyesterday format=yyyy-MM-dd>', 'SUBSTRING(<#rollovertime>,0,2)', '<#tempYL>', '<#TtempYL> ', '<#heatdegdays> ', '<#avgtemp>', '<#tempTH>', '<#TtempTH> ', '<#cooldegdays> ', '<#dewpointTL>', '<#TdewpointTL>', '<#humTL>', '<#ThumTL>', '<#humTH>', '<#ThumTH>', '<#dewpointTH>', '<#TdewpointTH>', '<#wchillTL>', '<#TwchillTL>', '<#apptempTL>', '<#TapptempTL>', '<#apptempTH>', '<#TapptempTH>', '<#heatindexTH>', '<#TheatindexTH>', '<#pressTL>', '<#TpressTL>', '<#pressTH>', '<#TpressTH>', '<#windTM>', '<#TwindTM>', '<#wgustTM>', '<#TwgustTM>', '<#bearingTM>', '<#domwindbearing>', '<#domwinddir>', '<#windrun>'); UPDATE `test_daily_summary` SET `DailyChillHours`= "(<#chillhours> - (SELECT `CumChillHours` FROM `test_daily_summary` WHERE `LogDate` = '(DATE_SUB(<#metdateyesterday format=yyyy-MM-dd>, INTERVAL 1 DAY)))'";

[mcrossley]

As the high gust compass bearing (and dominant wind direction compass bearing) are not available via web tags (yet), then I would populate them via a pre insert trigger on the table. First I'd define a function to perform the conversion of bearing to symbol, then use that in pre-insert trigger to set the symbol.

Something like (abbreviated and completely untested!)...

Code: Select all

DELIMITER $$

CREATE FUNCTION CompassPoint (
    bearing INT
)
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
    DECLARE symbol VARCHAR(20);
    IF bearing < 45 THEN
        SET symbol = 'N';
    ELSEIF bearing < 135 THEN
        SET symbol = 'E';
    END IF;
    RETURN (symbol);
END$$


CREATE PROCEDURE 'MyTable_before_insert' BEFORE INSERT ON 'Mytable'
FOR EACH ROW BEGIN
	SET NEW.BearDomWindSym = CompassPoint(NEW.BearDomWind);
END$$

DELIMITER ;

[freddie]

If you do take this route then could you please consider the following in your function:

In the meteorological world, the bearing "0" is a reserved number to be used when the wind speed is 0. It is NOT north/N. North in the meteorological world is 360.

Just something to think about for any function involving meteorological wind bearings.

Thank you

[sfws]

Freddie, Cumulus uses 360 for N
and 0 for no wind by default.

Mark, dominant wind direction web tag existed even in Cumulus 1.

[freddie]

Freddie, Cumulus uses 360 for N
and 0 for no wind by default

I'm looking at my site now. Wind is calm and it is reporting N at 0 knots. So I would argue that it doesn't.

[mcrossley]

Freddie, Cumulus uses 360 for N
and 0 for no wind by default.

Mark, dominant wind direction web tag existed even in Cumulus 1.

Yeah, I was just using that as an example - I couldn't see a high gust ordinal in your SQL statement, though it may just be my eyes.

[sfws]

I'm looking at my site now. Wind is calm and it is reporting N at 0 knots. So I would argue that it doesn't.

Freddie Please go to
Settings -> Station -> Options -> Use bearing zero when calm
and tick that option.

I have checked and you can see viewtopic.php?f=2&t=291&p=2187 (for version 1.8.3) was when North was moved to 360.
I have researched it and confirmed that it was was default in Cumulus 1 when Steve Loft documented it, but I have also checked MX defaults, it is not default in MX. No idea why it has been done that way, except that most defaults are the zero option, so why should this be different!


I see version 1.8.3 also added support for home automation, that was apparently called xAP in those days. This is before I started using Cumulus.

I couldn't see a high gust ordinal in your SQL statement, though it may just be my eyes.

It is not in my SQL, because I was not able to work out how to give it a value.

Thank you for your function suggestion, I will give that a go one day, but I am very busy both in garden and indoors, almost every day.

I have not been on my PC much today because I wanted to isolate my bedroom socket and found it was not on the circuit it used to be. Consequently, I was turning off circuits in turn in my consumer unit discovering which sockets are on which circuit as i tried to find out how to isolate the bedroom socket. I documented the circuits in the consumer unit on getting this property back in November, but I had an electrician working here before Christmas and I now realise he moved sockets to different circuits to what I documented before. anyway I shut off power to my computer without expecting to, that power cycling totally upset my router and I had trouble getting reauthentication.

[freddie]

I'm looking at my site now. Wind is calm and it is reporting N at 0 knots. So I would argue that it doesn't.

Freddie Please go to
Settings -> Station -> Options -> Use bearing zero when calm
and tick that option.

I have checked and you can see viewtopic.php?f=2&t=291&p=2187 (for version 1.8.3) was when North was moved to 360.
I have researched it and confirmed that it was was default in Cumulus 1 when Steve Loft documented it, but I have also checked MX defaults, it is not default in MX. No idea why it has been done that way, except that most defaults are the zero option, so why should this be different!

I have that option ticked - always have done, always will do. But that isn't the whole story. If it was then you wouldn't see what I saw on my page. Somewhere there is a function that is assigning directions such as "N" to bearings. In that function there should be a special case to assign direction "-" to a bearing of 0. And if there is a function that calculates average bearing to the nearest 10 degrees then that function should round a value of >= 0 and < 5 to 360, not 0. That can't be happening (both cases) otherwise you would not see an average direction of N when your average bearing is 0.

[sfws]

Somewhere there is a function that is assigning directions such as "N" to bearings

Ok, then MX is not replicating Cumulus 1 functionality. You are making me consider moving back to Cumulus 1, it seems MX has its faults with some basic functionality.
This is why we badly need MX to be fully documented, there is obviously a lot more that Steve did not implement properly, and Mark has not spotted.

[freddie]

Ok, then MX is not replicating Cumulus 1 functionality.

The behaviour is the same in Cumulus 1. I know that because I used it for 7 years. At least with MX you know it will be fixed. In fact, I will look at the source code tomorrow and come up with a patch for Mark.

[sfws]

In fact, I will look at the source code tomorrow and come up with a patch for Mark.

Thank you.

The behaviour is the same in Cumulus 1. I know that because I used it for 7 years.

I used it for a whole decade in a town where the anemometer experienced far less windy conditions than my experiences (last year and so far this year) in this county, and I definitely saw "---" reported for compass direction when wind speed was zero, and in those cases the bearing was also reported as zero.

Is the difference between our weather station types a factor in this difference of opinions?

Anyway, please no ongoing arguments, we are friends not foes!

[laulau]

Some options that perhaps may different behavior:

Capture.PNG

[Mapantz]

^ (and '---' for the compass point) ?

I have never seen any dashes. I used to have that option enabled, but I don't anymore.

[mcrossley]

Hmm, I *thought* I had that option enabled, but I don't, however the bearing definitely goes to zero when calm...

Capture.png

Must be because the Davis station uses zero for calm anyway?

[Mapantz]

Hmm, I *thought* I had that option enabled, but I don't, however the bearing definitely goes to zero when calm...

I have just noticed that in the realtime SQL database... It's logging 'N' as it's calm. But, the webtag is returning 'E' and '98°' which is what the console says.