Unicon 42: Durchschnittstemperaturen pro Tag

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:

tageswerte_KL_02564_akt.zip

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:

  1. Mittels mysqlimport direkt in der Shell.
  2. 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.

Kiel_Wetterdaten_v03.ods

Datenbasis: Deutscher Wetterdienst, Werte bearbeitet
http://www.dwd.de/DE/Home/home_node.html