SQL, Schéma de bases

Je suis en train de me former sur le language SQL et j'ai commencé quelques réflexions concernant le design de la base de  données de Cirrus. Il en ressort un certain nombre de point à approfondir.

Harvesters

Tout d'abord quel schéma de bases de données je souhaite utilise?. Pour le moment mes efforts se sont focalisés sur l'acquisition des données depuis dayer.biz. En est ressortie un "Harvester" qui me permet de récupérer régulièrement les données de ce site. Le programme est opérationnel et permet de downloader les données de bases qui vont populer mon schéma. Cela représente pour le moment environ 40'000 record de relevés. La fréquence est de l'ordre de l'heure concernant ces données. Par la suite j'adapterai le programme Perl pour faire l'acquisition de données provenant de ma station météo. Les aménagement à prévoir sont l'acquisition de données CSV plutôt que des pages WEB. Dans l'absolu, je pense que cela devrait simplifier les choses.

Schéma

Mes premières réflexions sur le schéma de base de données à adopter sont orienté sur l'utilisation de table chaînées reflétant des statistiques à des périodes données. Ainsi je distingue cinq tables de bases me permettant de faire des analyses.

•  weatherData : données brutes. La fréquence est de 60 minutes pour le moment. Pourquoi pas à terme descendre à 15 minutes. La table contient des données de bases comme la pression, température, point de rosé...etc. qui me permettent ensuite une analyse plus fine du temps.
• dailyStats : Regroupe des statistiques à l'échelle de la journée. Un enregistrement de cette table correspond à des calculs pour une journée donnée. La sélection se fait donc sur la date. A ce sujet, la date doit être marqué comme index pour weatherData.
• weeklyStats : Regroupe des statistiques à l'échelle de la semaine. Un enregistrement de cette table correspond à des calculs pour une semaine donnée. La sélection se fait donc sur la date. Il faut en déduire le numéro de semaine. A ce sujet, la date doit être marqué comme index pour dailyStats.
• monthlyStats : Regroupe des statistiques à l'échelle du mois. Un enregistrement de cette table correspond à des calculs pour un mois donnée. La sélection se fait donc sur la date. La date doit être marquée comme index pour weeklyStats.
• yearlyStats : Regroupe des statistiques à l'échelle de l'année. Un enregistrement de cette table correspond à des calculs pour une année donnée. La sélection se fait donc sur la date. La date doit être marquée comme index pour monthlyStats.

Ajout du point de rosé

Suite à quelques essais sur le calcul du point de rosé, j'ai trouvé une méthode d'approche relativement précise pour mes mesures. Je souhaite donc à présent l'intégrer dans mon schéma de table weatherData.

- L'information est en degrés.

- Le dew point peut etre positif ou negatif

- Les valeurs ne dépasse pas 100 en positif ou en négatif.

- Je souhaite donner une précision à deux chiffres après la virgule

L'ajout de cette information nécessite des modifications au niveau du programme de collecte de données météo "getWeekWeatherTable.pl". Les mises à jour correspondent à une modification du format de sortie pour ajouter la donnée dew point soit au niveau des CSV générés, soit au niveau de la table weatherData.

D'autre part, les valeurs dèjà existantes (environs 40'000) devront intégrées cette nouvelles données. Cela nécessite surement l'écriture d'un programme sur mesure pour modifier les champs existant un à un.

Approximation du point de rosée

Un élément que j'ai oublié dans ma table weatherData, le point de rosée. Voila une approximation possible pour le cacul tiré d'un article wikipedia : http://en.wikipedia.org/wiki/Dew_point

Une approximation bien connue utilisée pour calculer le point de rosée Td étant donné l'humidité relative HR et la température T réelle de l'air est:

where

où les températures sont en degrés Celsius et «ln» désigne le logarithme naturel. Les constantes sont les suivantes:

a = 17.271

b = 237.7 °C

Cette expression est basée sur l'approximation août-Roche-Magnus pour la pression de vapeur saturante de l'eau dans l'air en fonction de la température [3]. Il est considéré comme valable pour : 

0 °C < T < 60 °C

1% < RH < 100%

0 °C < T_d < 50 °C

Les données dont je dispose avec les relevé récupéré de dayer.biz sont :

• La pression
• La température moyenne pour une heure
• L'humidité moyenne pour une heure

Je pense que la température relevé par le thermometre est la temperature seche.L'humidité relative est probablement celle affichée sur ma station, mais il faut que je m'en assure. Pour dayer, je ne sais pas trop comment vérifier. A noter qu'il serait bon de reprendre cette information et de la rajouter à la définition de la table weatherData. L'information du point de rosée permettrait notamment de déduire la haute du plafond nuageux. Cette valeur restera evidement approximative mais permet de donner une idée plus précise des conditions nuageuses.

La méthode d'approximation reste à définir pour un prochain article.

Extract des données Dayer.biz par semaine

Ok, plutot que d'écrire from scratch un programme de génération de données météo, j'ai décidé de me remmettre au travail sur l'ecriture d'extracteurs WEB de données en provenance du site dayer.biz.

Je vois plusieurs avantage à faire cela :

• L'écriture d'un programme de génération de données météo valable est un sujet intéressant mais complexe. L'écriture du programme risque de me prendre plus de temps que je ne souhaite y consacré.
• Je devrais de toute façon récupérer un jour ou l'autre les données existantes de dayer.biz pour populer ma base météo

Cependant il existent des désavantages à faire cela :

• La périodicité des données est de l'ordre de l'heure, donc la fréquence des relevés données sont en dessous de ce que j'avais prévu comme base soit 15 minutes.
• Je garderais 15 minutes comme délai de récolte pour ma station
• Il va falloir prendre en compte le fait que je travail également avec des données sur une heure plutôt que quinze minutes 

Le programme de récolte écrit s'appel "getWeekWeatherTable.pl"

Il est écrit en PERL, lequel language se prête plutot bien à ce genre d'exercice. Le programme est capable de récupérer au minimum 1 semaines de données. La sortie se fait dans un fichier CSV et/ou directement sur la base SQL.

Il est à noter que j'ai légérement modifié la base de données pour intégrer un champs signature.

Ce champs est un calcul de MD5 sur la date/heure et le lieu. Ce système me permet de faire des contrôle de doublons avant insertion afin de ne pas charger plusieurs fois les mêmes données.

De plus le nouveau schéma utilisé implémente des index sur la signature, la date et la localisation pour améliorer les perfs par la suite.

Le schéma de weatherData est à présent celui décrit ci dessous :

CREATE TABLE `cirrus_db`.`weatherData` (

  `idweatherData` int(11) NOT NULL AUTO_INCREMENT,

  `fingerprint` varchar(45) NOT NULL,

  `date` datetime DEFAULT NULL,

  `idLocation` varchar(64) DEFAULT NULL,

  `temperature` decimal(5,2) DEFAULT NULL,

  `pressure` decimal(6,2) DEFAULT NULL,

  `moisture` decimal(5,2) DEFAULT NULL,

  `wind` decimal(5,2) DEFAULT NULL,

  `winddir` varchar(20) DEFAULT NULL,

  `rain` decimal(5,2) DEFAULT NULL,

  `uv` decimal(4,2) DEFAULT NULL,

  PRIMARY KEY (`idweatherData`),

  KEY `byDate` (`date`) USING BTREE,

  KEY `byLocation` (`idLocation`) USING BTREE,

  KEY `byFingerprint` (`fingerprint`) USING BTREE

) ENGINE=MyISAM AUTO_INCREMENT=200322 DEFAULT CHARSET=utf8 COMMENT='Raw weather info / updated hourly';

Note sur l'utilisation des données UVI de dayer.biz

J'ai la chance d'habiter pas très loin d'un amateur de météo qui a mis une station en ligne avec un micro site de météo. Je dois avouer que je lui pique ses données depuis maintenant trois ans. Déjà, j'aimerai lui dire combien je lui suis reconnaissant d'avoir mis l'ensemble de ces informations en ligne. C'est une vraie mine d'or pour moi tant que ma station météo et mon projet d'automatisation des collectes de mesures soient au point. Un GRAND MERCI dayer.biz!

Il s'impose cependant de faire la remarque suivante concernant les représentation graphique et les calculs de moyenne UVI pour la journée. Les données, malheureusement ne tiennent pas compte du cycle nuit/jour et les moyennes sont caculées sur 24h00 !

Une première correction peut consister en rapportant la moyenne au cycle jour/nuit, soit entre l'heure de levé et celle de couché du soleil. Cette première approximation est surement la meilleure. Elle tient compte d'une moyenne générale de la luminosité sur le temps réel d'exposition au soleil d'une part. D'autre part, cette moyenne est atténuée lorsque l'angle du soleil à l'horizon et faible. J'estime entre 0°C et 20°C matin et idem pour le soir ces périodes transitoires d'exposition. Le fait de prendre en considération ces périodes dans le calcul de la moyenne réduit celle-ci du fait de la faible exposition lors des périodes transitoires. Par contre, elle permet de prendre en compte des situations d'exposition partielles optimales en début et fin de journée, et donc de potentiellement élever la moyenne si le reste de la journée est couverte.

Dans cette idée, on peut aussi remarquer la moyenne est probablement plus "déviante" en été que les autres saisons. Ceci est notamment du à l'angle du plan de l’écliptique avec l'horizon. Cet angle est plus faible pendant ces trois saisons.

L'idée est de rapporter la moyenne sur 24 h 00 à une moyenne sur le temps d'exposition. Les calculs sont une simple règle de trois. On fait ici une extrapolation linéaire. Le calcul est évidement empirique. Il permet d'avoir une approximation à la louche mais quand même un peut plus proche de la réalité.

Méthode de calcul proposée

• Calculer la différence de temps dt en soustrayant l'heure de coucher à l'heure de levé du soleil. On obtient de cette façon le temps d'exposition. 
• Diviser la moyenne prise sur dayer.com par 24 
• Multiplier par le temps d'expositon obtenu dans le premier point

Exemple
 temps expo 14.68

 levé 06:15 

 couché 20:56           

Correction relevé UVI Dayer.biz 
UVI avg 2.8
UVI/hour 0.19  (UVI avg/temps expo)
UVI expo 4.58 (UVI/hour * 24)

Générateur de jeux de test

Suite à la description de la structure weatherData, je souhaite à présent écrire un générateur de données météo pour pouvoir populer cette table. L'objectif est de fournir des données à volonté pour pouvoir ensuite faciliter le développement de fonctions de calcul et de prévision sur le serveur SQL.

Le générateur doit respecter un cahier des charges pour pouvoir fournir des informations pertinentes et propice à l'analyse de données.

Je suis donc devant un nouveau chantier pour réaliser un générateur stochastique de données météos. Mais qu'est ce donc ? Un générateur stochastique de données météorologiques produit, pour un endroit donné, des séries chronologiques artificielles de données météorologiques de longueur illimitée, qui reposent sur les caractéristiques statistiques des conditions observées à cet endroit. Ces types de modèles statistiques sont généralement élaborés en deux étapes, la première consistant à modéliser les précipitations quotidiennes, alors que la deuxième se concentre sur les autres variables d’intérêt, telles que les températures minimales et maximales, le rayonnement solaire, l’humidité et la vitesse du vent, qui sont simulées de manière conditionnelle selon l’occurrence de la précipitation. Pour chaque mois, des paramètres différents sont choisis de façon à refléter les variations saisonnières à la fois de la valeur des variables proprement dites et de leurs corrélations croisées (soit dans les rapports entre les variables individuelles au fil du temps).


Donc la c'est simple : soit je me fait mon propre générateur, au risque d'y passer des heures car je souhaite quelque chose de réaliste, soit je vais voir sur le net ce que je trouve...


Aller, un ptit tour sur un moteur de recherche et je trouve le site suivant :
http://www.rothamsted.bbsrc.ac.uk/mas-models/larswg/download.php

C'est un lien vers un générateur gratuit. Par contre cela n'a pas l'air forcement simple à l'utilisation... Alors je vais faire mes petits essais... We keep in touch.