Statistics / charts of all Netatmo data - HELP!

[RiseUp]

Push! Any news?

Unfortunately, no update here. I'm still trying to figure this out myself...

[Matze]

Hmmm, ok. Let me know when have somehting.

[mike_du_51]

Hi,

I am currently working on an Excel file (in french language in first time..) to easily recover data from Netatmo station.
This file currently allows to find the maximum and minimum values, but my latest updates offer more information, such as daytime maximum temperatures and minimum, nighttime temperatures, the number of cold and frozen days, the number of days with rain, number of hot day, dew point.. and all this in a table and graph per day and per month. it looks a bit like what i see on your screenshots.
When I will put it online, test it ! Use is simple: it is enough to copy and paste data, and filter the desired data, by day or months. the file is already functional but i must to work to present the data as clearly as possible .

Mike_du_51

[Matze]

Hey,

Fore sure I would like to test it once you have finalized it.

By the way Meteoware is also a good software and provides you with similar data.

However it should be a must for Netatmo to provide all these data and overviews in their own applications.

Matze

Edit: I have just seen that you have the file already available.
I will tr it in the next days.

[frank_s]

Hi there,
hi Matze,
I think, the only flexible solution is to import the netatmo data (CSV) into a database,
mysql for example, and then get the statistic values you need with sql statements.

Last year I imported all netatmo data of the first 4 months into a mysql database with the table name "wetterstation".

Code: Select all

CREATE TABLE `Wetterstation` (
  `Timestamp` int(10) NOT NULL,
  `dt_readings` datetime NOT NULL,
  `reading1` decimal(6,2) DEFAULT NULL,
  `reading2` decimal(6,2) DEFAULT NULL,
  `reading3` decimal(6,2) DEFAULT NULL,
  `reading4` decimal(6,2) DEFAULT NULL,
  `reading5` decimal(6,2) DEFAULT NULL,
  `Headings` varchar(255) NOT NULL DEFAULT 'Temperatur;Luftfeuchtigkeit;CO2;Geräsch;Luftdruck',
  `StationName` varchar(255) NOT NULL DEFAULT 'wetterstation',
  `ModulType` varchar(255) NOT NULL DEFAULT '',
  `ModulName` varchar(255) NOT NULL DEFAULT '',
  `Long` decimal(9,6) NOT NULL DEFAULT '8.844903',
  `Lat` decimal(9,6) NOT NULL DEFAULT '49.950591',
  `Timezone` varchar(255) NOT NULL DEFAULT 'Europe/Berlin',
  UNIQUE KEY `Unique_Fields` (`Timestamp`,`StationName`,`ModulName`),
  KEY `Zeitpunkt` (`dt_readings`),
  KEY `ModulName` (`ModulName`)

Code: Select all

select * from Wetterstation where year(dt_readings) =2014 limit 10;
+------------+---------------------+----------+----------+----------+----------+----------+-----------------------------+---------------+-----------+---------------+----------+-----------+---------------+
| Timestamp  | dt_readings         | reading1 | reading2 | reading3 | reading4 | reading5 | Headings                    | StationName   | ModulType | ModulName     | Long     | Lat       | Timezone      |
+------------+---------------------+----------+----------+----------+----------+----------+-----------------------------+---------------+-----------+---------------+----------+-----------+---------------+
| 1388530800 | 2014-01-01 00:00:00 |     1.90 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
| 1388531107 | 2014-01-01 00:05:07 |     1.90 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
| 1388531415 | 2014-01-01 00:10:15 |     1.90 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
| 1388531723 | 2014-01-01 00:15:23 |     2.00 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
| 1388532030 | 2014-01-01 00:20:30 |     1.90 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
| 1388532338 | 2014-01-01 00:25:38 |     2.00 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
| 1388532645 | 2014-01-01 00:30:45 |     2.00 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
| 1388532953 | 2014-01-01 00:35:53 |     2.00 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
| 1388533209 | 2014-01-01 00:40:09 |     2.00 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
| 1388533517 | 2014-01-01 00:45:17 |     1.90 |    86.00 |     NULL |     NULL |     NULL | Temperatur;Luftfeuchtigkeit | wetterstation | outdoor   | Terrasse Nord | 8.844903 | 49.950591 | Europe/Berlin |
+------------+---------------------+----------+----------+----------+----------+----------+-----------------------------+---------------+-----------+---------------+----------+-----------+---------------+

After Importing you can start selecting your data.
For example a simple search of avarage, max and min temperature per month:
select month(dt_readings) Monat, year(dt_readings) Jahr, avg(reading1) durchschnitt, max(reading1) maximum, min(reading1) minimum from Wetterstation where ModulType='outdoor' and year(dt_readings) =2014 group by Monat,Jahr;

Code: Select all

+-------+------+--------------+---------+---------+
| Monat | Jahr | durchschnitt | maximum | minimum |
+-------+------+--------------+---------+---------+
|     1 | 2014 |     5.020297 |   14.60 |   -1.80 |
|     2 | 2014 |     6.415924 |   13.50 |   -0.30 |
|     3 | 2014 |     8.872552 |   21.00 |   -0.70 |
|     4 | 2014 |    14.162683 |   25.60 |    0.90 |
+-------+------+--------------+---------+---------+

Then with some sql-joins you can get the other values you need for your statistic.

If you have no experience with databases and queries then it is a good time to start with it.

Frank

[frank_s]

ok, maybe a good chance to update my database.
After some brainstorming I changed the table structures and created two new tables, one for each device
because it's easier to import the csv data:
outdoor_data for temperature and humidity and
rain_data for the rain amount....

Code: Select all

CREATE TABLE `outdoor_data` (
  `Timestamp` int(10) NOT NULL,
  `dt_readings` datetime NOT NULL,
  `temperature` decimal(6,2) DEFAULT NULL COMMENT 'temp in celsius',
  `humidity` decimal(6,2) DEFAULT NULL COMMENT 'percentage',
   `Stationname` varchar(255) NOT NULL DEFAULT 'Wetterstation',
  `Long` decimal(9,6) NOT NULL DEFAULT '8.844903',
  `Lat` decimal(9,6) NOT NULL DEFAULT '49.950591',
  `Timezone` varchar(255) NOT NULL DEFAULT 'Europe/Berlin',
  UNIQUE KEY `Unique_Fields` (`Timestamp`),
  KEY `Zeitpunkt` (`dt_readings`);

 CREATE TABLE `rain_data` (
  `Timestamp` int(10) NOT NULL,
  `dt_readings` datetime NOT NULL,
  `rain` decimal(6,3) NOT NULL DEFAULT '0.000' COMMENT 'in mm',
  `Stationname` varchar(255) NOT NULL DEFAULT 'Wetterstation',
  `Long` decimal(9,6) NOT NULL DEFAULT '8.844903',
  `Lat` decimal(9,6) NOT NULL DEFAULT '49.950591',
  `Timezone` varchar(255) NOT NULL DEFAULT 'Europe/Berlin',
  UNIQUE KEY `Unique_Fields` (`Timestamp`),
  KEY `Zeitpunkt` (`dt_readings`)

After importing the netatmo csv data with heidisql (heise.de/download/heidisql.html )

heidi_import.png (37.52 KiB) Viewed 461 times

then next step was thinking about the sql script to get the statistic data. This is my (current) final version :
-----------
select
year(DT.dt_readings) Jahr, month(DT.dt_readings) Monat ,round(avg( DT.temperature),2) 'DTemp (C)' , round(DMinTemp,2) 'DMinTemp (C)' , round(DMaxTemp,2) 'DMaxTemp (C)', ifnull(RM.Regenmenge,if(year(dt_readings)=2014 and month(dt_readings) < 5,'-',0)) 'Regenmenge (l)',
ifnull(RT.RegenTage,if(year(dt_readings)=2014 and month(dt_readings) < 5,'-',0)) '# RegenTage', ifnull(HTN.HeiztageNeubau,0) '# HeiztageNeubau (DT<10C)', ifnull(HTA.HeiztageAltbau,0) '# HeiztageAltbau (DT<15C)', ifnull(FT.Frosttage,0) '# FrostTage', ifnull(ET.Eistage,0) '# EisTage',
ifnull(KT.Kalttage,0) '# KaltTage', ifnull(ST.Sommertage,0) '# SommerTage', ifnull(HT.Heissetage,0) '# HeisseTage', ifnull(TN.TropenNaechte,0 ) '# TropenNaechte (MinT > 20C 22-06h)' from outdoor_data DT left join (

select D.Jahr, D.Monat, avg(D.Minimaltemperatur) DMinTemp from ( select year(dt_readings) Jahr, month(dt_readings) Monat, day(dt_readings) Tag, min(temperature) Minimaltemperatur
from outdoor_data where year(dt_readings) > 2013 group by Jahr,Monat,Tag) D group by D.Jahr, D.Monat ) MT
on ( MT.Jahr =year(DT.dt_readings) and MT.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, avg(D.Maximaltemperatur) DMaxTemp from ( select year(dt_readings) Jahr, month(dt_readings) Monat, day(dt_readings) Tag, max(temperature) Maximaltemperatur
from outdoor_data where year(dt_readings) > 2013 group by Jahr,Monat,Tag) D group by D.Jahr, D.Monat ) MMT
on ( MMT.Jahr =year(DT.dt_readings) and MMT.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, sum(D.isRegenTag) RegenTage from ( select distinct year(dt_readings) Jahr, month(dt_readings) Monat, day(dt_readings) Tag, 1 isRegenTag
from rain_data where year(dt_readings) > 2013 group by Jahr,Monat,Tag having sum(rain) > 0 ) D group by D.Jahr, D.Monat ) RT
on ( RT.Jahr =year(DT.dt_readings) and RT.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, D.Regenmenge Regenmenge from ( select year(dt_readings) Jahr, month(dt_readings) Monat, sum(rain) Regenmenge
from rain_data where year(dt_readings) > 2013 group by Jahr,Monat) D ) RM
on ( RM.Jahr =year(DT.dt_readings) and RM.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, sum(D.Heiztag_Neubau) HeiztageNeubau from ( select year(dt_readings) Jahr, month(dt_readings) Monat, day(dt_readings) Tag, 1 Heiztag_Neubau
from outdoor_data where year(dt_readings) > 2013 group by Jahr,Monat,Tag having avg(temperature) <= 10) D group by D.Jahr, D.Monat ) HTN
on ( HTN.Jahr =year(DT.dt_readings) and HTN.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, sum(D.Heiztag_Altbau) HeiztageAltbau from ( select year(dt_readings) Jahr, month(dt_readings) Monat, day(dt_readings) Tag, 1 Heiztag_Altbau
from outdoor_data where year(dt_readings) > 2013 group by Jahr,Monat,Tag having avg(temperature) <= 15) D group by D.Jahr, D.Monat ) HTA
on ( HTA.Jahr =year(DT.dt_readings) and HTA.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, sum(D.isFrosttag) FrostTage from ( select distinct year(dt_readings) Jahr, month(dt_readings) Monat , day(dt_readings) Tag, 1 isFrosttag
from outdoor_data where temperature < 0 and year(dt_readings) > 2013 group by Jahr,Monat,Tag) D group by D.Jahr, D.Monat order by D.Jahr, D.Monat) FT
on ( FT.Jahr =year(DT.dt_readings) and FT.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, sum(D.isEistag) EisTage from (select distinct year(dt_readings) Jahr, month(dt_readings) Monat , day(dt_readings) Tag, 1 isEistag
from outdoor_data where year(dt_readings) > 2013 group by Jahr,Monat,Tag having max(temperature) < 0) D where D.Jahr>2013 group by D.Jahr, D.Monat order by D.Jahr, D.Monat) ET
on ( ET.Jahr =year(DT.dt_readings) and ET.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, sum(D.isKalterTag) KaltTage from (select distinct year(dt_readings) Jahr, month(dt_readings) Monat , day(dt_readings) Tag, 1 isKalterTag
from outdoor_data where year(dt_readings) > 2013 group by Jahr,Monat,Tag having max(temperature) < 10) D where D.Jahr>2013 group by D.Jahr, D.Monat order by D.Jahr, D.Monat) KT
on ( KT.Jahr =year(DT.dt_readings) and KT.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, sum(D.isSommerTag) SommerTage from (select distinct year(dt_readings) Jahr, month(dt_readings) Monat , day(dt_readings) Tag, 1 isSommerTag
from outdoor_data where year(dt_readings) > 2013 group by Jahr,Monat,Tag having max(temperature) >= 25 ) D where D.Jahr>2013 group by D.Jahr, D.Monat order by D.Jahr, D.Monat) ST
on ( ST.Jahr =year(DT.dt_readings) and ST.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, sum(D.isHeisserTag) HeisseTage from (select distinct year(dt_readings) Jahr, month(dt_readings) Monat , day(dt_readings) Tag, 1 isHeisserTag
from outdoor_data where year(dt_readings) > 2013 group by Jahr,Monat,Tag having max(temperature) >= 30 ) D where D.Jahr>2013 group by D.Jahr, D.Monat order by D.Jahr, D.Monat) HT
on ( HT.Jahr =year(DT.dt_readings) and HT.Monat = month(DT.dt_readings) ) left join (

select D.Jahr, D.Monat, sum(D.isTropenNacht) TropenNaechte from ( select distinct year(dt_readings) Jahr, month(dt_readings) Monat, day(dt_readings) Tag, 1 isTropenNacht
from outdoor_data where year(dt_readings) > 2013 and hour(dt_readings) in (22,23,24,0,1,2,3,4,5) group by Jahr,Monat,Tag having min(temperature) >= 20) D group by D.Jahr, D.Monat ) TN
on ( TN.Jahr =year(DT.dt_readings) and TN.Monat = month(DT.dt_readings) )

where year(DT.dt_readings) > 2013 group by year(DT.dt_readings), month(DT.dt_readings) order by year(DT.dt_readings), month(DT.dt_readings);
-----------
ok,code is long but it's working....

And the result:

Wetterdaten.png (24.72 KiB) Viewed 461 times

Next steps: Have to check the amount of rain in 07. and 08.2014....
Frank

[Matze]

Wow, that looks already quite good.

I do not think I can manage this with mysql etc. as I am a complete IT rookie.



Matze

[frank_s]

I am a complete IT rookie

Quatsch! Sorry
We are in the period in which we can technically do many things to achieve our goals.
This is the reason, why I decided to use netatmo devices and
statistical data processing is mostly not IT, it is mostly mathematics, logic and statistics and ... using tools.

Thinking, click...click...click...Thinking !
Who can work with excel also can work with mysql in combination with heidisql.
Most difficult part is the sql-script, and that is done.

First : install mysql on windows, second heidisql (frontend for dummies).
Costs: (only) time, no money.

Then download csvs for outdoor and rain (if you have) .

Then most difficult decision: Name of the Database:
Example: TorgelowerWetter
Go to Abfrage-Tab in Heidisql
copy/paste in Abfrage-Tab between >>>> and <<<< :
>>>>
create database TorgelowerWetter;
use TorgelowerWetter;
CREATE TABLE `outdoor_data` (
`Timestamp` int(10) NOT NULL,
`dt_readings` datetime NOT NULL,
`temperature` decimal(6,2) DEFAULT NULL COMMENT 'temp in celsius',
`humidity` decimal(6,2) DEFAULT NULL COMMENT 'percentage',
`Stationname` varchar(255) NOT NULL DEFAULT 'Wetterstation',
`Long` decimal(9,6) NOT NULL DEFAULT '8.844903',
`Lat` decimal(9,6) NOT NULL DEFAULT '49.950591',
`Timezone` varchar(255) NOT NULL DEFAULT 'Europe/Berlin',
UNIQUE KEY `Unique_Fields` (`Timestamp`),
KEY `Zeitpunkt` (`dt_readings`);

CREATE TABLE `rain_data` (
`Timestamp` int(10) NOT NULL,
`dt_readings` datetime NOT NULL,
`rain` decimal(6,3) NOT NULL DEFAULT '0.000' COMMENT 'in mm',
`Stationname` varchar(255) NOT NULL DEFAULT 'Wetterstation',
`Long` decimal(9,6) NOT NULL DEFAULT '8.844903',
`Lat` decimal(9,6) NOT NULL DEFAULT '49.950591',
`Timezone` varchar(255) NOT NULL DEFAULT 'Europe/Berlin',
UNIQUE KEY `Unique_Fields` (`Timestamp`),
KEY `Zeitpunkt` (`dt_readings`);
<<<<

Maybe change the Long and Lat values (geo coordinates 8.844903 and 49.950591) to yours , then execute

Then import the CSVs in "Heidies" Werkzeug-Tab (for outdoor and rain) .

Last copy/paste SQL-Script (select ....) and execute

Finish!

and ... if we think about
- the next devices like wind/sun and their integration,
- automatically data updates every day,
- presentation on a webpage,
this will be the only way.

If my family and job give me the time ( ), I will do these steps this year.


BTW:

The reasons for the big amount of rain in 07 and 08/2014 : three local thunderstorms with big rain (20-30 l in 1 hour) I checked my data against the data of a weatherstation in next town ( distance 1,5 km) . They had no thunder at that time .

Then I noticed, that my avarage temp was 0.5 degrees to high in the first 9 months.
I remembered, that I 've done some calibration after wetter.com-presentation via wunderweather (At wetter.com I saw, that my temp-values were 0.5 degrees to high).
I performed this corrections with one sql statement for the early months in 2014.

Current differences to the other weatherstations in my neighborhood (not netatmo):
- my temp peaks (min and max) are not so extreme,
- my outdoor humidity values after rain days are to high for days (90-100%).

I think, I have to find a new place for the outdoor device.

Frank

[Matze]

Ok, ok, i will have a try, when i habe enough time.



By the way I have installed my outdoor unit in a davis radiation shield and then i did put in a bush / small tree.
It is really working because I have another outdoor module from another smaller weather station also in this radiation shield. No sun influence nothing.

By the way I am also using a Netatmo indoor module in the outside area. This one is put in a bird house, which is also installed in bush / small tree. Also working..

Matze

[frank_s]

Thanks Matze for this hint,
Now i have my outdoor module without cave in my davis shield 7714 .
After calibration due to the weather stations in my neighborhood my next 'project' is a personal weather-webcam with all (for me) necessary weather data in one "picture" - updated every 20 minutes.

I will open a thread - when I have something to show.

But still waiting for wind

frank