Community Support

[This post is marked as unsolved] Import data from Cumulus Dayfile.txt  

  RSS
Oddne Rasmussen
(@oddne)
Eminent Member πŸ‡³πŸ‡΄ Editor

Thanks πŸ˜‰

Next thing I need is the Stats page with all the high/lows. Are you just querying the wp_live_weather_stations_datas_year for that?? I just need to freashen up my PHP-knowledge to get that into a Wordpress page πŸ™‚

ReplyQuote
Posted : 04/03/2018 9:36 am
rkarpa01
(@rkarpa01)
Trusted Member Translator

I am using Wordpress Tables for the data in the stats and rain summary; it's easy to include it but also a bit limited in the output tweaking you can do.

I derive the data for WPTables through 2 views I setup on my wordpress instance.

The rain summary looks like this (code improvements always welcome, my SQL knowledge is not very good):

select 'Total Rain/Year' AS "Total Rain/Year",
substr(wp_live_weather_station_datas_year.timestamp,1,4) AS "Date",
concat(cast(round(sum(wp_live_weather_station_datas_year.measure_value),1) as char charset utf8),' mm') AS "Value"
from wp_live_weather_station_datas_year
where ((wp_live_weather_station_datas_year.device_id = 'yy:00:00:00:00:07')
and (wp_live_weather_station_datas_year.measure_type = 'rain_day_aggregated')
and (wp_live_weather_station_datas_year.measure_set = 'agg')
and (wp_live_weather_station_datas_year.timestamp > '2014-12-31'))
group by 1,2
order by 1,2

For the all time stats it does look probably weird, but that's how I got it to run (as said, SQL isn't my native language):

(select date_format(wp_live_weather_station_datas_year.timestamp,'%Y-%m-%d') AS Date,
'Max Temperature' AS Measure,
concat(cast(round(wp_live_weather_station_datas_year.measure_value,1) as char charset utf8),' Β°C') AS Value
from wp_live_weather_station_datas_year
where ((wp_live_weather_station_datas_year.device_id = 'yy:00:00:00:00:07')
and (wp_live_weather_station_datas_year.measure_type = 'temperature')
and (wp_live_weather_station_datas_year.measure_set = 'max')
and (wp_live_weather_station_datas_year.measure_value =
(select max(wp_live_weather_station_datas_year.measure_value)
from wp_live_weather_station_datas_year
where ((wp_live_weather_station_datas_year.device_id = 'yy:00:00:00:00:07')
and (wp_live_weather_station_datas_year.measure_type = 'temperature')
and (wp_live_weather_station_datas_year.measure_set = 'max')))))
order by 1 desc limit 1)

union

(select date_format(wp_live_weather_station_datas_year.timestamp,'%Y-%m-%d') AS Date,
'Min Temperature' AS Measure,
concat(cast(round(wp_live_weather_station_datas_year.measure_value,1) as char charset utf8),' Β°C') AS Value
from wp_live_weather_station_datas_year
where ((wp_live_weather_station_datas_year.device_id = 'yy:00:00:00:00:07')
and (wp_live_weather_station_datas_year.measure_type = 'temperature')
and (wp_live_weather_station_datas_year.measure_set = 'min')
and (wp_live_weather_station_datas_year.measure_value =
(select min(wp_live_weather_station_datas_year.measure_value)
from wp_live_weather_station_datas_year
where ((wp_live_weather_station_datas_year.device_id = 'yy:00:00:00:00:07')
and (wp_live_weather_station_datas_year.measure_type = 'temperature')
and (wp_live_weather_station_datas_year.measure_set = 'min')))))
order by 1 desc limit 1)

union

...

You need to union further whatever you want to have in the stats.

ReplyQuote
Posted : 04/03/2018 10:03 am
Oddne Rasmussen
(@oddne)
Eminent Member πŸ‡³πŸ‡΄ Editor

Thanks again!!

Strangest thing is that I cannot get WP Tables to accept the queries. Drilled down and found out that it didn't like the column measure_set. Created a view on wp_live_weather_station_datas_year exactly the same as the table except for renaming measure_set til measure_type_value. Then I could query the view. That's something I don't understand why is happening.

Β 

ReplyQuote
Posted : 04/03/2018 7:27 pm
Oddne Rasmussen
(@oddne)
Eminent Member πŸ‡³πŸ‡΄ Editor

Also, any reason why you are converting (CAST) the measure_value to char?Β 

ReplyQuote
Posted : 04/03/2018 7:29 pm
rkarpa01
(@rkarpa01)
Trusted Member Translator

I cast to char because I add the measure unit to the result (like Β°C).

I don't think WPTables can be used with too complex queries (never tried). Therefore I tested my queries in phpadmin and after got them to work I built two views for them, one for rain and another one for the stats. That allowed me to have nearly everything predefined in the queries and use WPTables with "select * from view".

ReplyQuote
Posted : 04/03/2018 7:35 pm
Oddne Rasmussen
(@oddne)
Eminent Member πŸ‡³πŸ‡΄ Editor

aah, as long as you build views you need to convert, very understandable πŸ˜‰

And also a good hint on making wp Tables more useful, with simple queries on views instead of "complex" once agains the tables πŸ˜‰

ReplyQuote
Posted : 04/03/2018 7:37 pm
rkarpa01
(@rkarpa01)
Trusted Member Translator

I wanted to avoid having a fourth column with the measure unit as it looked awful in the results, therefore I added the units directly to the value and for this converted to char to use concat.

Views allow me to run them even outside of WPTables and whenever I change the plugin or something, I can simply continue using them.

ReplyQuote
Posted : 04/03/2018 7:41 pm
GWCTas
(@gwctas)
New Member

Hi @oddne,

Like you, I have many years of data from Cumulus. Can you tell me what you did to convert the historical data for use in Weather Station?

Kind regards,

George

ReplyQuote
Posted : 05/11/2019 4:59 am
Oddne Rasmussen
(@oddne)
Eminent Member πŸ‡³πŸ‡΄ Editor

Hi George ,

Long post, sorry!

This worked for me, hope it can work for you πŸ˜‰

1.
I created a temp table for loading all the Cumulus.txt data:

CREATE TABLE temp_dayfile_import (
`recorded_date` DATETIME,
`gust_max` NUMERIC(3, 1),
`gust_max_angle` INT,
`gust_max_time` VARCHAR(5) CHARACTER SET utf8,
`temp_min` NUMERIC(3, 1),
`temp_min_time` VARCHAR(5) CHARACTER SET utf8,
`temp_max` NUMERIC(3, 1),
`temp_max_time` VARCHAR(5) CHARACTER SET utf8,
`pressure_min` NUMERIC(5, 1),
`pressure_min_time` VARCHAR(5) CHARACTER SET utf8,
`pressure_max` NUMERIC(5, 1),
`pressure_maks_time` VARCHAR(5) CHARACTER SET utf8,
`rainfall_max` NUMERIC(4, 1),
`rainfall_max_time` VARCHAR(5) CHARACTER SET utf8,
`rainfall_total` NUMERIC(4, 1),
`temp_avg` NUMERIC(3, 1),
`wind_total_km` NUMERIC(4, 1),
`wind_max` NUMERIC(3, 1),
`wind_max_time` VARCHAR(5) CHARACTER SET utf8,
`humidity_min` INT,
`humidity_min_time` VARCHAR(5) CHARACTER SET utf8,
`humidity_max` INT,
`humidity_max_time` VARCHAR(5) CHARACTER SET utf8,
`headindex_max` NUMERIC(3, 1),
`heatindex_max_time` VARCHAR(5) CHARACTER SET utf8,
`app_temp_max` NUMERIC(3, 1),
`app_temp_max_time` VARCHAR(5) CHARACTER SET utf8,
`app_temp_min` NUMERIC(3, 1),
`app_temp_min_time` VARCHAR(5) CHARACTER SET utf8,
`rain_hour_max` NUMERIC(4, 1),
`rain_hour_max_time` VARCHAR(5) CHARACTER SET utf8,
`windchill_min` NUMERIC(3, 1),
`windchill_min_time` VARCHAR(5) CHARACTER SET utf8,
`dewpoint_max` NUMERIC(3, 1),
`dwpoint_max_time` VARCHAR(5) CHARACTER SET utf8,
`dewpoint_min` NUMERIC(3, 1),
`dewpoint_min_time` VARCHAR(5) CHARACTER SET utf8,
`windangle_dominant` INT,
`heating_deg_days` NUMERIC(3, 1)
);

2.
Dump the data from Cumulus.txt into this table (example):

INSERT INTO temp_dayfile_import VALUES ('2012-01-10 00:00:00',7.8,135,'19:18',-2.8,'18:10',4.2,'17:44',1011.2,'22:28',1013.5,'17:45',0,'00:00',0,-1.8,35,4.4,'22:53',38,'17:44',92,'23:50',4.2,'17:44',-0.9,'17:44',-8.4,'18:14',0,'00:00',-7.7,'18:14',-2.2,'23:50',-9.8,'17:45',134,5.3);
INSERT INTO temp_dayfile_import VALUES ('2012-01-11 00:00:00',4.4,135,'13:34',-3.7,'21:16',-0.5,'03:25',1003.4,'23:58',1011.6,'00:45',7.2,'18:08',0.6,-1.7,36.5,2.7,'00:23',88,'23:41',95,'01:42',-0.5,'03:25',-2.7,'03:25',-7,'22:11',0.6,'18:06',-5.5,'22:11',-1.3,'03:25',-5.2,'23:59',118,20.3);
INSERT INTO temp_dayfile_import VALUES ('2012-01-12 00:00:00',4.1,90,'20:05',-7.5,'14:55',-3.4,'00:20',999.1,'07:20',1008.2,'23:59',0,'00:00',0,-5.2,58.9,2.4,'19:43',72,'08:13',90,'20:50',-3.4,'00:20',-6.1,'00:46',-11.6,'14:55',0,'00:00',-10.1,'19:43',-5.2,'00:00',-10.3,'14:55',312,23.5);

Use your tool of choice for loading the data πŸ˜‰

3.
Remember to make a copy of wp_live_weather_station_datas_year before messing with the data:

create table wp_live_weather_station_datas_year_20191105_bck as select * from wp_live_weather_station_datas_year;

4.
Then I loaded the cumulus.txt data into wp_live_weather_station_datas_year table.

Replace with you Device ID and also Module ID depending on module.
Run this to find your IDs:

SELECT distinct device_id, module_id, module_type FROM wp_live_weather_station_datas_year;

Then start loading data:

insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule1>','NAModule1', 'temperature', 'max', temp_max from temp_dayfile_import;
insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule1>','NAModule1', 'temperature', 'min', temp_min from temp_dayfile_import;
insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule1>','NAModule1', 'temperature', 'avg', temp_avg from temp_dayfile_import;

insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule1>','NAModule1', 'humidity', 'max', humidity_max from temp_dayfile_import;
insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule1>','NAModule1', 'humidity', 'min', humidity_min from temp_dayfile_import;

insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAComputed>','NAComputed', 'dew_point', 'min', dewpoint_min from temp_dayfile_import;
insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAComputed>','NAComputed', 'dew_point', 'max', dewpoint_max from temp_dayfile_import;

insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule2>','NAModule2', 'gustangle', 'max', gust_max_angle from temp_dayfile_import;
insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule2>','NAModule2', 'guststrength', 'max', gust_max from temp_dayfile_import;
insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule2>','NAModule2', 'windstrength', 'max', wind_max from temp_dayfile_import;
insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule2>','NAModule2', 'windangle', 'max', windangle_dominant from temp_dayfile_import;

insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAMain>','NAMain', 'pressure', 'max', pressure_max from temp_dayfile_import;
insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAMain>','NAMain', 'pressure', 'min', pressure_min from temp_dayfile_import;

insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID_NAModule3>','NAModule3', 'rain', 'max', rainfall_max from temp_dayfile_import;
YOUR_MODULE_ID_NAModule3
insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>','<YOUR_MODULE_ID>','NAModule3', 'rain_day_aggregated', 'agg', rainfall_total from temp_dayfile_import;

insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>', '<YOUR_MODULE_ID_NAComputed>','NAComputed', 'wind_chill', 'min', windchill_min from temp_dayfile_import;

insert into wp_live_weather_station_datas_year select recorded_date, '<YOUR_DEV_ID>', '<YOUR_MODULE_ID_NAComputed>','NAComputed', 'heat_index', 'max', headindex_max from temp_dayfile_import;
This post was modified 2 weeks ago by Oddne Rasmussen
ReplyQuote
Posted : 05/11/2019 9:30 am
GWCTas
(@gwctas)
New Member

Hey Oddne,

Greetings from Tasmania, Australia. Thank you for your reply. It looks too much for me but my son works in the USA and is the senior developer for Influx Data https://www.influxdata.com I hope he can help me.

Thank you again.

Kind regards,

George

ReplyQuote
Posted : 05/11/2019 9:46 am

  
Working

To participate, please connect first!