Wenn man im Internet sich nach Durchschnittstemperaturen umschaut, findet man leider nur Daten für Monate. Wir wollten das aber genauer.
Das Wetter ist sowohl für das Mittelalter-Reenactment als auch für LARP außerordentlich wichtig.
Da uns Durchschnittstemperaturen pro Monat nicht reichen und es andere Daten nicht frei verfügbar sind, müßen wir selber dir Durchschnittswerte ermitteln.
Beim deutschen Wetterdienst gibt es einen FTP Zugriff auf die rohen Observationsdaten der verschiedenen Meßstationen. Hier müßen wir uns nochmals bedanken, denn ohne diese Daten bekämen wir nicht die Informationen, die wir möchten.
Der FTP-Server findet sich hier: ftp://ftp-cdc.dwd.de/pub/CDC/
Kiel verfügte über zwei Wetterstationen:
Stations_id | von_datum | bis_datum | Stationshoehe | geoBreite | geoLaenge | Stationsname | Bundesland |
2564 | 19860601 | 20161016 | 27 | 54.3761 | 10.1433 | Kiel-Holtenau | Schleswig-Holstein |
2565 | 19400101 | 19880930 | 17 | 54.3378 | 10.0929 | Kiel-Kronshagen | Schleswig-Holstein |
Entsprechend holen wir uns die historischen Daten für diese Stations-Ids aus folgenden FTP-Verzeichnis:
ftp://ftp-cdc.dwd.de/pub/CDC/observations_germany/climate/daily/kl/historical/
Die beiden Dateien:
tageswerte_02564_19860601_20151231_hist.zip
tageswerte_02565_19400101_19880930_hist.zip
Und auch die aktuelleren „recent“ Daten im passenden FTP-Verzeichnis
ftp://ftp-cdc.dwd.de/pub/CDC/observations_germany/climate/daily/kl/recent/
Bei den „recent“-Daten gibt es die Station 02565 leider nicht:
Mit einen beliebigen Entpacker entpackt man diese Zip-Archive und erhält folgende Dateien:
produkt_klima_Tageswerte_19400101_19880930_02565.txt
produkt_klima_Tageswerte_19860601_20151231_02564.txt
produkt_klima_Tageswerte_20150417_20161017_02564.txt (dieser Dateiname ist tagesaktuell)
Für die Auswetung habe ich eine MySQL-Datenbank genommen. Das mag vielleicht nicht die erste Wahl sein, aber wir haben Zugriff auf eine solche Datenbank und es handelt sich um OpenSource Software.
MySQL verfügt über zwei Möglichkeiten Textdateien in eine Datenbank einzulesen:
- Mittels mysqlimport direkt in der Shell.
- Mit dem Kommando „LOAD DATA LOCAL INFILE“ in einer Client-Session.
Schema für die Wetterdaten:
Der Ordnung halber sollte man diese Daten in ein eigenes Datenbank-Schema stecken. Es ist nicht zwingend nötig, macht es aber übersichtlicher.
CREATE SCHEMA wetter;
In den folgenden Beispiele gehe ich davon aus, das die Tabellen in diesem Schema liegen. Wenn du das anders hälst, mußt du die Beispiele entsprechend anpassen.
Tabelle für den Import
Egal für welches Tool man sich entscheidet, man benötigt auf jeden Fall eine Tabelle, in die man die Daten importiert:
CREATE TABLE `wetterrohdaten` ( `STATIONS_ID` varchar(9) DEFAULT NULL, `MESS_DATUM` varchar(8) DEFAULT NULL, `QUALITAETS_NIVEAU` varchar(4) DEFAULT NULL, `LUFTTEMPERATUR` varchar(7) DEFAULT NULL, `DAMPFDRUCK` varchar(6) DEFAULT NULL, `BEDECKUNGSGRAD` varchar(6) DEFAULT NULL, `LUFTDRUCK_STATIONSHOEHE` varchar(8) DEFAULT NULL, `REL_FEUCHTE` varchar(8) DEFAULT NULL, `WINDGESCHWINDIGKEIT` varchar(6) DEFAULT NULL, `LUFTTEMPERATUR_MAXIMUM` varchar(7) DEFAULT NULL, `LUFTTEMPERATUR_MINIMUM` varchar(7) DEFAULT NULL, `LUFTTEMP_AM_ERDB_MINIMUM` varchar(7) DEFAULT NULL, `WINDSPITZE_MAXIMUM` varchar(6) DEFAULT NULL, `NIEDERSCHLAGSHOEHE` varchar(6) DEFAULT NULL, `NIEDERSCHLAGSHOEHE_IND` varchar(4) DEFAULT NULL, `SONNENSCHEINDAUER` varchar(9) DEFAULT NULL, `SCHNEEHOEHE` varchar(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Wie man sehen kann, wird durchgehend als Datentyp varchar genommen. Diese Tabelle außschließlich für den Import gedacht. Man überführt später die Daten in Tabelle mit sinnvollen Datentypen, damit man damit arbeiten kann.
mysqlimport
Dieses Tool hab ich zuerst probiert. Es funktioniert soweit, allerdings hat es ein Problem:
Sollte der Import nicht sauber laufen, wird zwar angezeigt, das es Warnung gab, aber leider nicht welche. Es gibt auch keine Möglichkeit bei mysqlimport in dieser Version an die Informationen zu kommen. Es hängt damit zusammen, das die Warnings in der Session abrufbar sind. Das Tool myslimport öffnet die Session, führt den Import durch und schließt dann gleich wieder die Session.
Eine weitere Besonderheit ist der Dateiname, der dem Namen der Tabelle ensprechen muß.
Auch wenn die LOAD DATA LOCAL INFILE Methode besser ist, hier der vollständigkeit halber, wie man die Daten aus der Textdatei importiert.
Die Datei muß den Namen der Tabelle bekommen:
cp produkt_klima_Tageswerte_20150417_20161017_02564.txt wetterrohdaten
Das Kommando zum Importieren:
mysqlimport --host=localhost --user=root --password --replace --local --fields-terminated-by=';' \ --ignore-lines=1 --lines-terminated-by='eor\r\n' wetter wetterrohdaten
Als Ausgabe sollte man folgendes bekommen:
wetter.wetterrohdaten: Records: 550 Deleted: 0 Skipped: 0 Warnings: 0
Das Statement um die importierten Daten in eine benutztbare Form umzuformen folgt in den nächsten Abschnitten.
LOAD DATA LOCAL INFILE
Dies ist die Methode der Wahl. Sie wird auch von der Shell ausgeführt, man wirft in den mysql-client mit der Option „-e“ das Kommando, was er ausführen soll, direkt gefolgt vom Kommando „SHOW WARNINGS“ um mögliche Fehlermeldungen zu sehen.
In diesem Beispiel nutzen wir die Datei produkt_klima_Tageswerte_19860601_20151231_02564.txt von der vorher eine Kopie mit dem Namen wetterrohdaten angelegt wird.
cp produkt_klima_Tageswerte_19860601_20151231_02564.txt wetterrohdaten
Dies ist eigentlich unnötig. Man könnte auch direkt den Namen angeben. Außerdem kann man bei dieser Methode die Zieltabelle angeben.
mysql --local-infile=1 --host=localhost --user=root --password wetter \ -e "LOAD DATA LOCAL INFILE 'wetterrohdaten' REPLACE INTO TABLE wetterrohdaten \ FIELDS TERMINATED BY ';' LINES TERMINATED BY 'eor\r\n' IGNORE 1 LINES ; \ SHOW WARNINGS"
Was bei dieser Datei folgende Ausgabe liefert:
+---------+------+------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------+ | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | | Warning | 1261 | Row 10664 doesn't contain data for all columns | +---------+------+------------------------------------------------+
Es handelt sich hier um die letzte Zeile der Tabelle, es ist also kein echtes Problem.
Die Zieltabellen
Es gibt drei Quelldateien, also braucht man drei Zieltabellen:
wetterdaten_02564_history
CREATE TABLE wetterdaten_02564_history ( STATIONS_ID BIGINT , DATUM DATE , JAHR YEAR , MONAT INT , TAG INT , QUALITAETS_NIVEAU INT , LUFTTEMPERATUR DECIMAL(5,2) , DAMPFDRUCK DECIMAL(6,2) , BEDECKUNGSGRAD DECIMAL(5,2) , LUFTDRUCK_STATIONSHOEHE DECIMAL(7,2) , REL_FEUCHTE DECIMAL(5,2) , WINDGESCHWINDIGKEIT DECIMAL(5,2) , LUFTTEMPERATUR_MAXIMUM DECIMAL(4,1) , LUFTTEMPERATUR_MINIMUM DECIMAL(4,1) , LUFTTEMP_AM_ERDB_MINIMUM DECIMAL(4,1) , WINDSPITZE_MAXIMUM DECIMAL(6,2) , NIEDERSCHLAGSHOEHE DECIMAL(4,1) , NIEDERSCHLAGSHOEHE_IND INT , SONNENSCHEINDAUER DECIMAL(6,3) , SCHNEEHOEHE INT ) ENGINE = InnoDB CHARACTER SET=utf8;
wetterdaten_02565_history
CREATE TABLE wetterdaten_02565_history ( STATIONS_ID BIGINT , DATUM DATE , JAHR YEAR , MONAT INT , TAG INT , QUALITAETS_NIVEAU INT , LUFTTEMPERATUR DECIMAL(5,2) , DAMPFDRUCK DECIMAL(6,2) , BEDECKUNGSGRAD DECIMAL(5,2) , LUFTDRUCK_STATIONSHOEHE DECIMAL(7,2) , REL_FEUCHTE DECIMAL(5,2) , WINDGESCHWINDIGKEIT DECIMAL(5,2) , LUFTTEMPERATUR_MAXIMUM DECIMAL(4,1) , LUFTTEMPERATUR_MINIMUM DECIMAL(4,1) , LUFTTEMP_AM_ERDB_MINIMUM DECIMAL(4,1) , WINDSPITZE_MAXIMUM DECIMAL(6,2) , NIEDERSCHLAGSHOEHE DECIMAL(4,1) , NIEDERSCHLAGSHOEHE_IND INT , SONNENSCHEINDAUER DECIMAL(6,3) , SCHNEEHOEHE INT ) ENGINE = InnoDB CHARACTER SET=utf8;
wetterdaten_02564_recent
CREATE TABLE wetterdaten_02564_recent ( STATIONS_ID BIGINT , DATUM DATE , JAHR YEAR , MONAT INT , TAG INT , QUALITAETS_NIVEAU INT , LUFTTEMPERATUR DECIMAL(5,2) , DAMPFDRUCK DECIMAL(6,2) , BEDECKUNGSGRAD DECIMAL(5,2) , LUFTDRUCK_STATIONSHOEHE DECIMAL(7,2) , REL_FEUCHTE DECIMAL(5,2) , WINDGESCHWINDIGKEIT DECIMAL(5,2) , LUFTTEMPERATUR_MAXIMUM DECIMAL(4,1) , LUFTTEMPERATUR_MINIMUM DECIMAL(4,1) , LUFTTEMP_AM_ERDB_MINIMUM DECIMAL(4,1) , WINDSPITZE_MAXIMUM DECIMAL(6,2) , NIEDERSCHLAGSHOEHE DECIMAL(4,1) , NIEDERSCHLAGSHOEHE_IND INT , SONNENSCHEINDAUER DECIMAL(6,3) , SCHNEEHOEHE INT ) ENGINE = InnoDB CHARACTER SET=utf8;
Die Daten von der Rohdaten-Tabelle in die Arbeitstabelle überführen
Arbeitstabelle: wetterrohdaten
Zieltabelle: wetterdaten_02565_history
Per Select werden die Daten formatiert und in die Arbeitstabelle gesteckt.
INSERT INTO wetterdaten_02565_history ( STATIONS_ID , DATUM , JAHR , MONAT , TAG , QUALITAETS_NIVEAU , LUFTTEMPERATUR , DAMPFDRUCK , BEDECKUNGSGRAD , LUFTDRUCK_STATIONSHOEHE , REL_FEUCHTE , WINDGESCHWINDIGKEIT , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , LUFTTEMP_AM_ERDB_MINIMUM , WINDSPITZE_MAXIMUM , NIEDERSCHLAGSHOEHE , NIEDERSCHLAGSHOEHE_IND , SONNENSCHEINDAUER , SCHNEEHOEHE ) SELECT trim(STATIONS_ID) , concat( concat( concat( concat( substring(trim(MESS_DATUM),1,4) , "-" ) , substring(trim(MESS_DATUM),5,2) ) , "-" ) , substring(trim(MESS_DATUM),7,2) ) as DATUM , substring(trim(MESS_DATUM),1,4) as JAHR , substring(trim(MESS_DATUM),5,2) as MONAT , substring(trim(MESS_DATUM),7,2) as TAG , trim(QUALITAETS_NIVEAU) as QUALITAETS_NIVEAU , trim(LUFTTEMPERATUR) as LUFTTEMPERATUR , trim(DAMPFDRUCK) as DAMPFDRUCK , trim(BEDECKUNGSGRAD) as BEDECKUNGSGRAD , trim(LUFTDRUCK_STATIONSHOEHE) as LUFTDRUCK_STATIONSHOEHE , trim(REL_FEUCHTE) as REL_FEUCHTE , trim(WINDGESCHWINDIGKEIT) as WINDGESCHWINDIGKEIT , trim(LUFTTEMPERATUR_MAXIMUM) as LUFTTEMPERATUR_MAXIMUM , trim(LUFTTEMPERATUR_MINIMUM) as LUFTTEMPERATUR_MINIMUM , trim(LUFTTEMP_AM_ERDB_MINIMUM) as LUFTTEMP_AM_ERDB_MINIMUM , trim(WINDSPITZE_MAXIMUM) as WINDSPITZE_MAXIMUM , trim(NIEDERSCHLAGSHOEHE) as NIEDERSCHLAGSHOEHE , trim(NIEDERSCHLAGSHOEHE_IND) as NIEDERSCHLAGSHOEHE_IND , trim(SONNENSCHEINDAUER) as SONNENSCHEINDAUER , trim(SCHNEEHOEHE) as SCHNEEHOEHE from wetterrohdaten;
Die letzte Zeile enthielt nur ein Zeichen daher die Warnings. Praktischerweise bekommt man diese Nullzeile über die STATION_ID die Zahl 0 als Wert hat.
DELETE FROM wetterdaten_02565_history WHERE STATIONS_ID=0;
Rohdaten-Tabelle leeren
Diese Tabelle wurde der Einfachheit mehrfach verwendet und wurde vor der nächsten Benutzung einfach nur geleert.
delete from wetterrohdaten;
Und ein Check obs funktioniert hat:
select * from wetterrohdaten;
Die anderen beiden Textdateien importieren
produkt_klima_Tageswerte_19860601_20151231_02564.txt
Die Rohdatentabelle ist aktuell leer.
Datei: produkt_klima_Tageswerte_19860601_20151231_02564.txt
Arbeitstabelle: wetterrohdaten
Zieltabelle: wetterdaten_02564_history
mysql --local-infile=1 --host=localhost --user=root --password wetter \ -e "LOAD DATA LOCAL INFILE 'produkt_klima_Tageswerte_19860601_20151231_02564.txt' \ REPLACE INTO TABLE wetterrohdaten \ FIELDS TERMINATED BY ';' LINES TERMINATED BY 'eor\r\n' IGNORE 1 LINES ; \ SHOW WARNINGS"
Importieren in die Zieltabelle:
INSERT INTO wetterdaten_02564_history ( STATIONS_ID , DATUM , JAHR , MONAT , TAG , QUALITAETS_NIVEAU , LUFTTEMPERATUR , DAMPFDRUCK , BEDECKUNGSGRAD , LUFTDRUCK_STATIONSHOEHE , REL_FEUCHTE , WINDGESCHWINDIGKEIT , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , LUFTTEMP_AM_ERDB_MINIMUM , WINDSPITZE_MAXIMUM , NIEDERSCHLAGSHOEHE , NIEDERSCHLAGSHOEHE_IND , SONNENSCHEINDAUER , SCHNEEHOEHE ) SELECT trim(STATIONS_ID) , concat( concat( concat( concat( substring(trim(MESS_DATUM),1,4) , "-" ) , substring(trim(MESS_DATUM),5,2) ) , "-" ) , substring(trim(MESS_DATUM),7,2) ) as DATUM , substring(trim(MESS_DATUM),1,4) as JAHR , substring(trim(MESS_DATUM),5,2) as MONAT , substring(trim(MESS_DATUM),7,2) as TAG , trim(QUALITAETS_NIVEAU) as QUALITAETS_NIVEAU , trim(LUFTTEMPERATUR) as LUFTTEMPERATUR , trim(DAMPFDRUCK) as DAMPFDRUCK , trim(BEDECKUNGSGRAD) as BEDECKUNGSGRAD , trim(LUFTDRUCK_STATIONSHOEHE) as LUFTDRUCK_STATIONSHOEHE , trim(REL_FEUCHTE) as REL_FEUCHTE , trim(WINDGESCHWINDIGKEIT) as WINDGESCHWINDIGKEIT , trim(LUFTTEMPERATUR_MAXIMUM) as LUFTTEMPERATUR_MAXIMUM , trim(LUFTTEMPERATUR_MINIMUM) as LUFTTEMPERATUR_MINIMUM , trim(LUFTTEMP_AM_ERDB_MINIMUM) as LUFTTEMP_AM_ERDB_MINIMUM , trim(WINDSPITZE_MAXIMUM) as WINDSPITZE_MAXIMUM , trim(NIEDERSCHLAGSHOEHE) as NIEDERSCHLAGSHOEHE , trim(NIEDERSCHLAGSHOEHE_IND) as NIEDERSCHLAGSHOEHE_IND , trim(SONNENSCHEINDAUER) as SONNENSCHEINDAUER , trim(SCHNEEHOEHE) as SCHNEEHOEHE from wetterrohdaten;
Bereinigen der letzten Zeile:
DELETE FROM wetterdaten_02564_history WHERE STATIONS_ID=0;
produkt_klima_Tageswerte_20150417_20161017_02564.txt (dieser Dateiname ist tagesaktuell)
Die Rohdatentabelle erneut leeren:
delete from wetterrohdaten;
Datei: produkt_klima_Tageswerte_20150417_20161017_02564.txt (dieser Dateiname ist tagesaktuell)
Arbeitstabelle: wetterrohdaten
Zieltabelle: wetterdaten_02564_recent
mysql --local-infile=1 --host=localhost --user=root --password wetter \ -e "LOAD DATA LOCAL INFILE 'produkt_klima_Tageswerte_20150417_20161017_02564.txt' \ REPLACE INTO TABLE wetterrohdaten \ FIELDS TERMINATED BY ';' LINES TERMINATED BY 'eor\r\n' IGNORE 1 LINES ; \ SHOW WARNINGS"
Importieren in die Zieltabelle:
INSERT INTO wetterdaten_02564_recent ( STATIONS_ID , DATUM , JAHR , MONAT , TAG , QUALITAETS_NIVEAU , LUFTTEMPERATUR , DAMPFDRUCK , BEDECKUNGSGRAD , LUFTDRUCK_STATIONSHOEHE , REL_FEUCHTE , WINDGESCHWINDIGKEIT , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , LUFTTEMP_AM_ERDB_MINIMUM , WINDSPITZE_MAXIMUM , NIEDERSCHLAGSHOEHE , NIEDERSCHLAGSHOEHE_IND , SONNENSCHEINDAUER , SCHNEEHOEHE ) SELECT trim(STATIONS_ID) , concat( concat( concat( concat( substring(trim(MESS_DATUM),1,4) , "-" ) , substring(trim(MESS_DATUM),5,2) ) , "-" ) , substring(trim(MESS_DATUM),7,2) ) as DATUM , substring(trim(MESS_DATUM),1,4) as JAHR , substring(trim(MESS_DATUM),5,2) as MONAT , substring(trim(MESS_DATUM),7,2) as TAG , trim(QUALITAETS_NIVEAU) as QUALITAETS_NIVEAU , trim(LUFTTEMPERATUR) as LUFTTEMPERATUR , trim(DAMPFDRUCK) as DAMPFDRUCK , trim(BEDECKUNGSGRAD) as BEDECKUNGSGRAD , trim(LUFTDRUCK_STATIONSHOEHE) as LUFTDRUCK_STATIONSHOEHE , trim(REL_FEUCHTE) as REL_FEUCHTE , trim(WINDGESCHWINDIGKEIT) as WINDGESCHWINDIGKEIT , trim(LUFTTEMPERATUR_MAXIMUM) as LUFTTEMPERATUR_MAXIMUM , trim(LUFTTEMPERATUR_MINIMUM) as LUFTTEMPERATUR_MINIMUM , trim(LUFTTEMP_AM_ERDB_MINIMUM) as LUFTTEMP_AM_ERDB_MINIMUM , trim(WINDSPITZE_MAXIMUM) as WINDSPITZE_MAXIMUM , trim(NIEDERSCHLAGSHOEHE) as NIEDERSCHLAGSHOEHE , trim(NIEDERSCHLAGSHOEHE_IND) as NIEDERSCHLAGSHOEHE_IND , trim(SONNENSCHEINDAUER) as SONNENSCHEINDAUER , trim(SCHNEEHOEHE) as SCHNEEHOEHE from wetterrohdaten;
Tabelle für alle Wetterdaten
Die Daten aus den drei einzelnen Tabellen sollen in eine einzige Tabelle gesteckt werden, damit man bequemer damit arbeiten kann.
Die Tabelle:
CREATE TABLE wetterdaten ( STATIONS_ID BIGINT , DATUM DATE , JAHR YEAR , MONAT INT , TAG INT , QUALITAETS_NIVEAU INT , LUFTTEMPERATUR DECIMAL(5,2) , DAMPFDRUCK DECIMAL(6,2) , BEDECKUNGSGRAD DECIMAL(5,2) , LUFTDRUCK_STATIONSHOEHE DECIMAL(7,2) , REL_FEUCHTE DECIMAL(5,2) , WINDGESCHWINDIGKEIT DECIMAL(5,2) , LUFTTEMPERATUR_MAXIMUM DECIMAL(4,1) , LUFTTEMPERATUR_MINIMUM DECIMAL(4,1) , LUFTTEMP_AM_ERDB_MINIMUM DECIMAL(4,1) , WINDSPITZE_MAXIMUM DECIMAL(6,2) , NIEDERSCHLAGSHOEHE DECIMAL(4,1) , NIEDERSCHLAGSHOEHE_IND INT , SONNENSCHEINDAUER DECIMAL(6,3) , SCHNEEHOEHE INT ) ENGINE = InnoDB CHARACTER SET=utf8;
Die drei Tabellen in die neue Haupttabelle stecken
Das erste Datum von wetterdaten_02564_recent:
select min(DATUM) from wetterdaten_02564_recent; 2015-04-17
Das letzte Datum von wetterdaten_02564_recent:
select max(DATUM) from wetterdaten_02564_recent; 2016-10-17
Das letzte Datum von wetterdaten_02564_history:
select max(DATUM) from wetterdaten_02564_history; 2015-12-31
Test des Selects für das Befüllen:
select * from wetterdaten_02564_recent where DATUM > "2015-12-31" ;
Ok, das schaut gut aus.
Und die Hauptabelle nun befüllen:
INSERT INTO wetterdaten SELECT * FROM wetterdaten_02564_history;
INSERT INTO wetterdaten SELECT * FROM wetterdaten_02565_history;
INSERT INTO wetterdaten SELECT * FROM wetterdaten_02564_recent WHERE DATUM > "2015-12-31";
Probleme mit den Daten:
SELECT MONAT , TAG , LUFTTEMPERATUR , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , BEDECKUNGSGRAD , NIEDERSCHLAGSHOEHE , SONNENSCHEINDAUER FROM wetterdaten WHERE LUFTTEMPERATUR="-999";
Es gibt ein paar merkwürdige Daten, die man aufräumen muß, bevor man einen Durchschnitt ermitteln kann.
Umbenennen und eine neue Tabelle
Wenn man Daten bereinigt, hat sich eine unique id als sehr hilfreich erwiesen. Es gibt mehrere Möglichkeiten sowas zu machen, aber die einfachste ist eine neue Tabelle mit einer automatisch hochzählenden id und ein INSERT von der alten un die neue Tabelle.
ALTER TABLE wetterdaten RENAME wetterdaten_alt;
Und die neue Tabelle mit der unique id erstellen:
CREATE TABLE wetterdaten ( ID INT AUTO_INCREMENT PRIMARY KEY , STATIONS_ID BIGINT , DATUM DATE , JAHR YEAR , MONAT INT , TAG INT , QUALITAETS_NIVEAU INT , LUFTTEMPERATUR DECIMAL(5,2) , DAMPFDRUCK DECIMAL(6,2) , BEDECKUNGSGRAD DECIMAL(5,2) , LUFTDRUCK_STATIONSHOEHE DECIMAL(7,2) , REL_FEUCHTE DECIMAL(5,2) , WINDGESCHWINDIGKEIT DECIMAL(5,2) , LUFTTEMPERATUR_MAXIMUM DECIMAL(4,1) , LUFTTEMPERATUR_MINIMUM DECIMAL(4,1) , LUFTTEMP_AM_ERDB_MINIMUM DECIMAL(4,1) , WINDSPITZE_MAXIMUM DECIMAL(6,2) , NIEDERSCHLAGSHOEHE DECIMAL(4,1) , NIEDERSCHLAGSHOEHE_IND INT , SONNENSCHEINDAUER DECIMAL(6,3) , SCHNEEHOEHE INT ) ENGINE = InnoDB CHARACTER SET=utf8;
Anschließend die Daten von der alten in die neue Tabelle einfügen:
INSERT INTO wetterdaten ( STATIONS_ID , DATUM , JAHR , MONAT , TAG , QUALITAETS_NIVEAU , LUFTTEMPERATUR , DAMPFDRUCK , BEDECKUNGSGRAD , LUFTDRUCK_STATIONSHOEHE , REL_FEUCHTE , WINDGESCHWINDIGKEIT , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , LUFTTEMP_AM_ERDB_MINIMUM , WINDSPITZE_MAXIMUM , NIEDERSCHLAGSHOEHE , NIEDERSCHLAGSHOEHE_IND , SONNENSCHEINDAUER , SCHNEEHOEHE ) SELECT STATIONS_ID , DATUM , JAHR , MONAT , TAG , QUALITAETS_NIVEAU , LUFTTEMPERATUR , DAMPFDRUCK , BEDECKUNGSGRAD , LUFTDRUCK_STATIONSHOEHE , REL_FEUCHTE , WINDGESCHWINDIGKEIT , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , LUFTTEMP_AM_ERDB_MINIMUM , WINDSPITZE_MAXIMUM , NIEDERSCHLAGSHOEHE , NIEDERSCHLAGSHOEHE_IND , SONNENSCHEINDAUER , SCHNEEHOEHE FROM wetterdaten_alt;
Warum der Aufwand mit diesen Werten?
Wenn man das nicht tut, würde der Wert -999 mit in den Durchschnitt eingerechnet werden, was diesen komplett verfälschen würde.
Weiter Aufräumen
Auf der Suche nach weiteren merkwürdigen Werten:
SELECT ID , STATIONS_ID , DATUM , MONAT , TAG , LUFTTEMPERATUR , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , BEDECKUNGSGRAD , NIEDERSCHLAGSHOEHE , SONNENSCHEINDAUER FROM wetterdaten # WHERE LUFTTEMPERATUR="-999" WHERE LUFTTEMPERATUR_MAXIMUM="-999";
Die Abfrage liefert etwa um die 2000 Zeilen.
Es gibt aber insgesamt 28.334 Einträge. Wir brauchen hier keine wissenschaftlich exakte Studie sondern nur einen groeben Überblick. Daher können diese Eintraege gelöscht werden. Es handelt sich lediglich um weniger als 10% der Gesamtdaten.
DELETE FROM wetterdaten WHERE LUFTTEMPERATUR_MAXIMUM="-999";
DELETE FROM wetterdaten WHERE LUFTTEMPERATUR="-999"
Der Bedeckungsgrad?
SELECT ID , STATIONS_ID , DATUM , MONAT , TAG , LUFTTEMPERATUR , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , BEDECKUNGSGRAD , NIEDERSCHLAGSHOEHE , SONNENSCHEINDAUER FROM wetterdaten WHERE BEDECKUNGSGRAD="-999";
Hier werden 78 Zeilen ausgegeben.
Es ist keine kritische Menge, daher werden diese Daten entfernt:
DELETE FROM wetterdaten WHERE BEDECKUNGSGRAD="-999"
Und auch die Niederschlagsmenge hatte Fehlerhafte Daten:
DELETE FROM wetterdaten WHERE NIEDERSCHLAGSHOEHE="-999"
Die Sonnenscheindauer?
SELECT ID , STATIONS_ID , DATUM , MONAT , TAG , LUFTTEMPERATUR , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , BEDECKUNGSGRAD , NIEDERSCHLAGSHOEHE , SONNENSCHEINDAUER FROM wetterdaten WHERE SONNENSCHEINDAUER="-999";
Hier finden sich 440 Zeilen.
Und diese Datensätzen entfernen:
DELETE FROM wetterdaten WHERE SONNENSCHEINDAUER="-999"
Auflistung aller Daten:
SELECT ID , STATIONS_ID , DATUM , MONAT , TAG , LUFTTEMPERATUR , LUFTTEMPERATUR_MAXIMUM , LUFTTEMPERATUR_MINIMUM , BEDECKUNGSGRAD , NIEDERSCHLAGSHOEHE , SONNENSCHEINDAUER FROM wetterdaten
Immer noch 25.772 Messdaten. Damit kann man arbeiten.
WICHTIG: Wir brauchen hier nur einen Überblick. Daher ist für uns ein Schwund von 10% der Meßdaten nicht weiter tragisch. Würde man möglichst genaue Ergebnisse haben wollen, würde man für jedes Feld eine eigene Haupttabelle anlegen um den Verlust möglichst gering zu halten.
Die Durchschnitte bilden
Hier ein Statement für die Durchschnitte über die ganze Tabelle:
SELECT MONAT , TAG , round(AVG(LUFTTEMPERATUR),1) as lufttemperatur , round(AVG(LUFTTEMPERATUR_MAXIMUM),1) as lufttemperatur_max , round(AVG(LUFTTEMPERATUR_MINIMUM),1) as lufttemperatur_min , round(AVG(BEDECKUNGSGRAD),2) as bedeckungsgrad , round(AVG(NIEDERSCHLAGSHOEHE),2) as niederschlagshoehe , round(AVG(SONNENSCHEINDAUER),2) as sonnenscheindauer FROM wetterdaten GROUP BY MONAT, TAG;
Und hier eine Abfrage, die die letzten 5, 10, 25 und 77 Jahre in eigene Spalten gruppiert.
SELECT xxx.MONAT as m , xxx.TAG as t , sum(xxx.5_lufttemperatur) as 5_lufttemperatur , sum(xxx.10_lufttemperatur) as 10_lufttemperatur , sum(xxx.25_lufttemperatur) as 25_lufttemperatur , sum(xxx.77_lufttemperatur) as 77_lufttemperatur , sum(xxx.5_lufttemperatur_max) as 5_lufttemperatur_max , sum(xxx.10_lufttemperatur_max) as 10_lufttemperatur_max , sum(xxx.25_lufttemperatur_max) as 25_lufttemperatur_max , sum(xxx.77_lufttemperatur_max) as 77_lufttemperatur_max , sum(xxx.5_lufttemperatur_min) as 5_lufttemperatur_min , sum(xxx.10_lufttemperatur_min) as 10_lufttemperatur_min , sum(xxx.25_lufttemperatur_min) as 25_lufttemperatur_min , sum(xxx.77_lufttemperatur_min) as 77_lufttemperatur_min , sum(xxx.5_bedeckungsgrad) as 5_bedeckungsgrad , sum(xxx.10_bedeckungsgrad) as 10_bedeckungsgrad , sum(xxx.25_bedeckungsgrad) as 25_bedeckungsgrad , sum(xxx.77_bedeckungsgrad) as 77_bedeckungsgrad , sum(xxx.5_niederschlagshoehe) as 5_niederschlagshoehe , sum(xxx.10_niederschlagshoehe) as 10_niederschlagshoehe , sum(xxx.25_niederschlagshoehe) as 25_niederschlagshoehe , sum(xxx.77_niederschlagshoehe) as 77_niederschlagshoehe , sum(xxx.5_sonnenscheindauer) as 5_sonnenscheindauer , sum(xxx.10_sonnenscheindauer) as 10_sonnenscheindauer , sum(xxx.25_sonnenscheindauer) as 25_sonnenscheindauer , sum(xxx.77_sonnenscheindauer) as 77_sonnenscheindauer FROM ( ( SELECT MONAT , TAG , round(AVG(LUFTTEMPERATUR),1) as 5_lufttemperatur , 0 as 10_lufttemperatur , 0 as 25_lufttemperatur , 0 as 77_lufttemperatur , round(AVG(LUFTTEMPERATUR_MAXIMUM),1) as 5_lufttemperatur_max , 0 as 10_lufttemperatur_max , 0 as 25_lufttemperatur_max , 0 as 77_lufttemperatur_max , round(AVG(LUFTTEMPERATUR_MINIMUM),1) as 5_lufttemperatur_min , 0 as 10_lufttemperatur_min , 0 as 25_lufttemperatur_min , 0 as 77_lufttemperatur_min , round(AVG(BEDECKUNGSGRAD),2) as 5_bedeckungsgrad , 0 as 10_bedeckungsgrad , 0 as 25_bedeckungsgrad , 0 as 77_bedeckungsgrad , round(AVG(NIEDERSCHLAGSHOEHE),2) as 5_niederschlagshoehe , 0 as 10_niederschlagshoehe , 0 as 25_niederschlagshoehe , 0 as 77_niederschlagshoehe , round(AVG(SONNENSCHEINDAUER),2) as 5_sonnenscheindauer , 0 as 10_sonnenscheindauer , 0 as 25_sonnenscheindauer , 0 as 77_sonnenscheindauer FROM wetterdaten WHERE DATUM > "2011-10-16" GROUP BY MONAT, TAG ) UNION ALL ( SELECT MONAT , TAG , 0 as 5_lufttemperatur , round(AVG(LUFTTEMPERATUR),1) as 10_lufttemperatur , 0 as 25_lufttemperatur , 0 as 77_lufttemperatur , 0 as 5_lufttemperatur_max , round(AVG(LUFTTEMPERATUR_MAXIMUM),1) as 10_lufttemperatur_max , 0 as 25_lufttemperatur_max , 0 as 77_lufttemperatur_max , 0 as 5_lufttemperatur_min , round(AVG(LUFTTEMPERATUR_MINIMUM),1) as 10_lufttemperatur_min , 0 as 25_lufttemperatur_min , 0 as 77_lufttemperatur_min , 0 as 5_bedeckungsgrad , round(AVG(BEDECKUNGSGRAD),2) as 10_bedeckungsgrad , 0 as 25_bedeckungsgrad , 0 as 77_bedeckungsgrad , 0 as 5_niederschlagshoehe , round(AVG(NIEDERSCHLAGSHOEHE),2) as 10_niederschlagshoehe , 0 as 25_niederschlagshoehe , 0 as 77_niederschlagshoehe , 0 as 5_sonnenscheindauer , round(AVG(SONNENSCHEINDAUER),2) as 10_sonnenscheindauer , 0 as 25_sonnenscheindauer , 0 as 77_sonnenscheindauer FROM wetterdaten WHERE DATUM > "2005-10-16" GROUP BY MONAT, TAG ) UNION ALL ( SELECT MONAT , TAG , 0 as 5_lufttemperatur , 0 as 10_lufttemperatur , round(AVG(LUFTTEMPERATUR),1) as 25_lufttemperatur , 0 as 77_lufttemperatur , 0 as 5_lufttemperatur_max , 0 as 10_lufttemperatur_max , round(AVG(LUFTTEMPERATUR_MAXIMUM),1) as 25_lufttemperatur_max , 0 as 77_lufttemperatur_max , 0 as 5_lufttemperatur_min , 0 as 10_lufttemperatur_min , round(AVG(LUFTTEMPERATUR_MINIMUM),1) as 25_lufttemperatur_min , 0 as 77_lufttemperatur_min , 0 as 5_bedeckungsgrad , 0 as 10_bedeckungsgrad , round(AVG(BEDECKUNGSGRAD),2) as 25_bedeckungsgrad , 0 as 77_bedeckungsgrad , 0 as 5_niederschlagshoehe , 0 as 10_niederschlagshoehe , round(AVG(NIEDERSCHLAGSHOEHE),2) as 25_niederschlagshoehe , 0 as 77_niederschlagshoehe , 0 as 5_sonnenscheindauer , 0 as 10_sonnenscheindauer , round(AVG(SONNENSCHEINDAUER),2) as 25_sonnenscheindauer , 0 as 77_sonnenscheindauer FROM wetterdaten WHERE DATUM > "1991-10-16" GROUP BY MONAT, TAG ) UNION ALL ( SELECT MONAT , TAG , 0 as 5_lufttemperatur , 0 as 10_lufttemperatur , 0 as 25_lufttemperatur , round(AVG(LUFTTEMPERATUR),1) as 77_lufttemperatur , 0 as 5_lufttemperatur_max , 0 as 10_lufttemperatur_max , 0 as 25_lufttemperatur_max , round(AVG(LUFTTEMPERATUR_MAXIMUM),1) as 77_lufttemperatur_max , 0 as 5_lufttemperatur_min , 0 as 10_lufttemperatur_min , 0 as 25_lufttemperatur_min , round(AVG(LUFTTEMPERATUR_MINIMUM),1) as 77_lufttemperatur_min , 0 as 5_bedeckungsgrad , 0 as 10_bedeckungsgrad , 0 as 25_bedeckungsgrad , round(AVG(BEDECKUNGSGRAD),2) as 77_bedeckungsgrad , 0 as 5_niederschlagshoehe , 0 as 10_niederschlagshoehe , 0 as 25_niederschlagshoehe , round(AVG(NIEDERSCHLAGSHOEHE),2) as 77_niederschlagshoehe , 0 as 5_sonnenscheindauer , 0 as 10_sonnenscheindauer , 0 as 25_sonnenscheindauer , round(AVG(SONNENSCHEINDAUER),2) as 77_sonnenscheindauer FROM wetterdaten GROUP BY MONAT, TAG ) ) as xxx GROUP BY xxx.MONAT, xxx.TAG;
Das Ergebnis in einem OpenOffice-Sheet
Das Ergebnis der vorherigen Abfrage wurde in ein OpenOffice-Sheet gesteckt. Dazu wurde dann auch eine Grafik über die Lufttemperatur eingefügt.
Datenbasis: Deutscher Wetterdienst, Werte bearbeitet
http://www.dwd.de/DE/Home/home_node.html