Translate the German weather phenomenons

[fortunewalla]

Translate the German weather phenomenons.

`weather` enum('Nebel-Schneefall','Schneefall','Regen','Regen-Schneefall','Nebel-Regen','Nebel-Regen-Gewitter','Gewitter','Nebel','Regen-Gewitter') DEFAULT NULL,

The table structure with the German translation.

mysql> describe weatherdata;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field         | Type                                                                                                                                      | Null | Key | Default | Extra |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
| log_date      | date                                                                                                                                      | NO   | PRI | NULL    |       |
| time          | time                                                                                                                                      | NO   | PRI | NULL    |       |
| station       | int                                                                                                                                       | NO   | PRI | NULL    |       |
| temp          | decimal(3,1)                                                                                                                              | NO   |     | NULL    |       |
| humidity      | decimal(4,1)                                                                                                                              | NO   |     | NULL    |       |
| airpressure   | decimal(10,2)                                                                                                                             | NO   |     | NULL    |       |
| wind          | decimal(5,2)                                                                                                                              | NO   |     | NULL    |       |
| weather       | enum('Nebel-Schneefall','Schneefall','Regen','Regen-Schneefall','Nebel-Regen','Nebel-Regen-Gewitter','Gewitter','Nebel','Regen-Gewitter') | YES  |     | NULL    |       |
| winddirection | smallint                                                                                                                                  | NO   |     | NULL    |       |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
9 rows in set (0.05 sec)

[fortunewalla]

Translate the weather phenomenons

In order to replace the German weather phenomenon names with the English translation, we need to expand the the ENUM column weather to include the new English names as well. Then we can UPDATE the names and finally we can remove the German names from the ENUM list.

1) Expand the ENUM column to include new English names also.

ALTER TABLE
    weatherdata
    MODIFY COLUMN
        weather ENUM (
        'Nebel-Schneefall', 'Schneefall', 'Regen', 'Regen-Schneefall', 'Nebel-Regen', 'Nebel-Regen-Gewitter', 'Gewitter', 'Nebel', 'Regen-Gewitter',
        'fog-snowfall',
        'snowfall',
        'rain',
        'rain-snowfall',
        'fog-rain',
        'fog-rain-thunderstorm',
        'thunderstorm',
        'fog',
        'rain-thunderstorm'
        );

2) Then update the weatherdata data column with English Names.

UPDATE weatherdata
SET weather='snowfall'
WHERE weather = 'Schneefall';
UPDATE weatherdata
SET weather='rain-snowfall'
WHERE weather = 'Regen-Schneefall';
UPDATE weatherdata
SET weather='rain'
WHERE weather = 'Regen';
UPDATE weatherdata
SET weather='fog'
WHERE weather = 'Nebel';
UPDATE weatherdata
SET weather='rain-thunderstorm'
WHERE weather = 'Regen-Gewitter';
UPDATE weatherdata
SET weather='fog-rain'
WHERE weather = 'Nebel-Regen';
UPDATE weatherdata
SET weather='thunderstorm'
WHERE weather = 'Gewitter';
UPDATE weatherdata
SET weather='fog-snowfall'
WHERE weather = 'Nebel-Schneefall';
UPDATE weatherdata
SET weather='fog-rain-thunderstorm'
WHERE weather = 'Nebel-Regen-Gewitter';

3) Finally update the table weatherdata ENUM to keep only the English names.

ALTER TABLE
    weatherdata
    MODIFY COLUMN
        weather ENUM (
        'fog-snowfall',
        'snowfall',
        'rain',
        'rain-snowfall',
        'fog-rain',
        'fog-rain-thunderstorm',
        'thunderstorm',
        'fog',
        'rain-thunderstorm'
        );

[fortunewalla]

This seems done...

mysql> describe weatherdata;                                                                                                                       
+---------------+----------------------------------------------------------------------------------------------------------------------------------
--+------+-----+---------+-------+                                                                                                                 
| Field         | Type                                                                                                                             
  | Null | Key | Default | Extra |                                                                                                                 
+---------------+----------------------------------------------------------------------------------------------------------------------------------
--+------+-----+---------+-------+                                                                                                                 
| log_date      | date                                                                                                                             
  | NO   | PRI | NULL    |       |                                                                                                                 
| time          | time                                                                                                                             
  | NO   | PRI | NULL    |       |                                                                                                                 
| station       | int                                                                                                                              
  | NO   | PRI | NULL    |       |                                                                                                                 
| temp          | decimal(3,1)                                                                                                                     
  | NO   |     | NULL    |       |                                                                                                                 
| humidity      | decimal(4,1)                                                                                                                     
  | NO   |     | NULL    |       |                                                                                                                 
| airpressure   | decimal(10,2)                                                                                                                    
  | NO   |     | NULL    |       |                                                                                                                 
| wind          | decimal(5,2)                                                                                                                     
  | NO   |     | NULL    |       |                                                                                                                 
| weather       | enum('fog-snowfall','snowfall','rain','rain-snowfall','fog-rain','fog-rain-thunderstorm','thunderstorm','fog','rain-thunderstorm'
) | YES  |     | NULL    |       |                                                                                                                 
| winddirection | smallint                                                                                                                         
  | NO   |     | NULL    |       |                                                                                                                 
+---------------+----------------------------------------------------------------------------------------------------------------------------------
--+------+-----+---------+-------+                                                                                                                 
9 rows in set (0.34 sec)                                                                                                                           

mysql> select distinct weather from weatherdata;                                                                                                   
+-----------------------+                                                                                                                          
| weather               |                                                                                                                          
+-----------------------+                                                                                                                          
| snowfall              |                                                                                                                          
| NULL                  |                                                                                                                          
| rain-snowfall         |                                                                                                                          
| rain                  |                                                                                                                          
| fog                   |                                                                                                                          
| rain-thunderstorm     |                                                                                                                          
| fog-rain              |                                                                                                                          
| thunderstorm          |                                                                                                                          
| fog-snowfall          |                                                                                                                          
| fog-rain-thunderstorm |                                                                                                                          
+-----------------------+                                                                                                                          
10 rows in set (8.07 sec)                                                                                                                          

mysql>

updated field values and their counts.

mysql> select weather, count(weather) from weatherdata group by weather;
+-----------------------+----------------+                              
| weather               | count(weather) |                              
+-----------------------+----------------+                              
| snowfall              |         464056 |                              
| NULL                  |              0 |                              
| rain-snowfall         |         416776 |                              
| rain                  |        1611684 |                              
| fog                   |         141436 |                              
| rain-thunderstorm     |         367284 |                              
| fog-rain              |         129820 |                              
| thunderstorm          |           1508 |                              
| fog-snowfall          |          21580 |                              
| fog-rain-thunderstorm |          18688 |                              
+-----------------------+----------------+                              
10 rows in set (10.30 sec)