Community Support

Import data from Cu...
 
Notifications
Clear all

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

25 Posts
4 Users
1 Likes
169.4 K Views
(@oddne)
Posts: 11
Member
Topic starter
 

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 🙂

 
Posted : 04/03/2018 9:36 am
(@rkarpa01)
Posts: 58
Member
 

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.

 
Posted : 04/03/2018 10:03 am
(@oddne)
Posts: 11
Member
Topic starter
 

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.

 

 
Posted : 04/03/2018 7:27 pm
(@oddne)
Posts: 11
Member
Topic starter
 

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

 
Posted : 04/03/2018 7:29 pm
(@rkarpa01)
Posts: 58
Member
 

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".

 
Posted : 04/03/2018 7:35 pm
(@oddne)
Posts: 11
Member
Topic starter
 

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 😉

 
Posted : 04/03/2018 7:37 pm
(@rkarpa01)
Posts: 58
Member
 

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.

 
Posted : 04/03/2018 7:41 pm
(@gwctas)
Posts: 20
Eminent 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

 
Posted : 05/11/2019 4:59 am
(@oddne)
Posts: 11
Member
Topic starter
 

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 4 years ago by Oddne Rasmussen
 
Posted : 05/11/2019 9:30 am
(@gwctas)
Posts: 20
Eminent 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

 
Posted : 05/11/2019 9:46 am
Page 2 / 2