Database Tables

live

The live table is used to store incoming observations. Please note that only a subset of all columns is shown in the table below. The script processing the observations and saving them into this database table automatically creates additional columns if there are data. ... in the table indicate the data columns (e.g,. temperature observations, cloud cover observations, …).

The live table is a rolling database containing the latest observations for all incoming stations. The script CleanUp.py cleans the database from time to time moving the observations for some specific stations into the archive database table and deletes all others.

[Autogenerated table scheme of table “live] Rolling database for (raw) incoming observations.”
Field Type Null Key Default Extra
statnr int(11) NO MUL None  
datum int(8) NO MUL None  
datumsec int(11) NO MUL None  
stdmin smallint(4) NO   None  
msgtyp enum(‘na’,’bufr’,’synop’) YES   na  
stint enum(‘na’,’essential’,’additional’) YES   na  
utime timestamp NO MUL CURRENT_TIMESTAMP  
ucount tinyint(3) unsigned YES   0  
… … … … … …
  • Non-unique key named bufr_statnr on (statnr)
  • Non-unique key named bufr_datumsec on (datumsec)
  • Non-unique key named bufr_datum on (datum)
  • Non-unique key named bufr_einspiel on (utime)
  • Unique-key named bufr_statnr_datumsec_msgtyp on (statnr, datumsec, msgtyp)

archive

The archive table has the same structure as the live database table and contains long-term archive data for a set of specified stations. We keep the data for the tournament stations and drop all others as we don’t want to keep a copy of all observations (would be a huge database and an unnecessary and unused copy of everything).

[Autogenerated table scheme of table “archive] Archive table, contains long-term observations (copy of the live table) for specified stations.”
Field Type Null Key Default Extra
statnr int(11) NO MUL None  
datum int(8) NO MUL None  
datumsec int(11) NO MUL None  
stdmin smallint(4) NO   None  
msgtyp enum(‘na’,’bufr’,’synop’) YES   na  
stint enum(‘na’,’essential’,’additional’) YES   na  
utime timestamp NO MUL CURRENT_TIMESTAMP  
ucount tinyint(3) unsigned YES   0  
… … … … … …
  • Non-unique key named bufr_statnr on (statnr)
  • Non-unique key named bufr_datumsec on (datumsec)
  • Non-unique key named bufr_datum on (datum)
  • Non-unique key named bufr_einspiel on (utime)
  • Unique-key named bufr_statnr_datumsec_msgtyp on (statnr, datumsec, msgtyp)

stations

Station information as read from the BUFR files.

[Autogenerated table scheme of table “stations] Station meta information as received from the BUFR messages. Rows will be updated, no historical information kept (if a station e.g., would be moved or renamed).”
Field Type Null Key Default Extra
statnr smallint(11) unsigned NO MUL None  
nr tinyint(3) unsigned NO   None  
name varchar(150) YES   None  
lon decimal(10,4) NO   None  
lat decimal(10,4) NO   None  
hoehe smallint(6) NO   None  
hbaro smallint(6) YES   -999  
changed timestamp NO   CURRENT_TIMESTAMP  
  • Non-unique key named stations_statnr on (statnr)

bufrdesc

BUFR description as read from the BUFR files.

[Autogenerated table scheme of table “bufrdesc] Stores bufr data description handlers. Contain variable description and original BUFRID.”
Field Type Null Key Default Extra
bufrid smallint(3) unsigned NO   None  
param varchar(35) NO PRI None  
desc varchar(150) YES   None  
unit varchar(35) YES   None  
period mediumint(8) unsigned YES   0  
offset float YES   0  
factor float YES   1  
changed timestamp NO   CURRENT_TIMESTAMP  
  • Unique-key named bufrdesc_param on (param)