GHCN Country Code Changes by Version – MySQL results

In another thread I’ve been building a MySQL database of information from the various data versions floating around. Here I have my first result.

It is a table of country numbers and names for each of the three versions, sorted by country name. This lets you quickly see the map of what country numbers were before, and are now; and some of what differs between the versions in terms of actual country names.

it is modestly long report, 678 rows, but “enjoy” ;-)

mysql> SELECT version, country, country_name FROM country ORDER BY country_name, version ;
+---------+---------+--------------------------------------------------------+
| version | country | country_name                                           |
+---------+---------+--------------------------------------------------------+
| Gv1     | 514     | ADELIE LAND                                            |
| Gv1     | 206     | AFGHANISTAN                                            |
| Gv2     | 201     | AFGHANISTAN                                            |
| Gv3     | 201     | AFGHANISTAN                                            |
| Gv1     | 401     | ALASKA (U.S.A.)                                        |
| Gv1     | 624     | ALBANIA                                                |
| Gv2     | 601     | ALBANIA                                                |
| Gv3     | 601     | ALBANIA                                                |
| Gv1     | 106     | ALGERIA                                                |
| Gv2     | 101     | ALGERIA                                                |
| Gv3     | 101     | ALGERIA                                                |
| Gv2     | 521     | AMERICAN SAMOA (U.S.A.)                                |
| Gv3     | 521     | AMERICAN SAMOA (U.S.A.)                                |
| Gv2     | 157     | AMSTERDAM ISLAND (FRANCE)                              |
| Gv3     | 157     | AMSTERDAM ISLAND (FRANCE)                              |
| Gv1     | 144     | ANGOLA                                                 |
| Gv2     | 102     | ANGOLA                                                 |
| Gv3     | 102     | ANGOLA                                                 |
| Gv1     | 799     | ANTARCTIC STATIONS (ALL COUNTRIES)                     |
| Gv2     | 700     | ANTARCTICA                                             |
| Gv3     | 700     | ANTARCTICA                                             |
| Gv1     | 418     | ANTIGUA AND BARBUDA                                    |
| Gv2     | 426     | ANTIGUA AND BARBUDA                                    |
| Gv3     | 426     | ANTIGUA AND BARBUDA                                    |
| Gv1     | 313     | ARGENTINA                                              |
| Gv2     | 301     | ARGENTINA                                              |
| Gv3     | 301     | ARGENTINA                                              |
| Gv3     | 701     | ARGENTINE BASE IN ANTARCTICA                           |
| Gv2     | 602     | ARMENIA                                                |
| Gv3     | 602     | ARMENIA                                                |
| Gv2     | 158     | ASCENSION ISLAND (U.K.)                                |
| Gv3     | 158     | ASCENSION ISLAND (U.K.)                                |
| Gv1     | 513     | AUSTRALIA                                              |
| Gv1     | 513     | AUSTRALIA                                              |
| Gv1     | 513     | AUSTRALIA                                              |
| Gv2     | 501     | AUSTRALIA                                              |
| Gv3     | 501     | AUSTRALIA                                              |
| Gv1     | 619     | AUSTRIA                                                |
| Gv2     | 603     | AUSTRIA                                                |
| Gv3     | 603     | AUSTRIA                                                |
| Gv2     | 604     | AZERBAIJAN                                             |
| Gv3     | 604     | AZERBAIJAN                                             |
| Gv1     | 406     | BAHAMAS                                                |
| Gv1     | 406     | BAHAMAS                                                |
| Gv1     | 207     | BAHRAIN                                                |
| Gv2     | 202     | BAHRAIN                                                |
| Gv3     | 202     | BAHRAIN                                                |
| Gv1     | 214     | BANGLADESH                                             |
| Gv2     | 203     | BANGLADESH                                             |
| Gv3     | 203     | BANGLADESH                                             |
| Gv1     | 422     | BARBADOS                                               |
| Gv2     | 401     | BARBADOS                                               |
| Gv3     | 401     | BARBADOS                                               |
| Gv2     | 605     | BELARUS                                                |
| Gv3     | 605     | BELARUS                                                |
| Gv2     | 536     | BELAU                                                  |
| Gv3     | 536     | BELAU                                                  |
| Gv1     | 610     | BELGIUM                                                |
| Gv2     | 606     | BELGIUM                                                |
| Gv3     | 606     | BELGIUM                                                |
| Gv1     | 411     | BELIZE                                                 |
| Gv2     | 402     | BELIZE                                                 |
| Gv3     | 402     | BELIZE                                                 |
| Gv1     | 138     | BENIN                                                  |
| Gv2     | 103     | BENIN                                                  |
| Gv3     | 103     | BENIN                                                  |
| Gv2     | 427     | BERMUDA (U.K.)                                         |
| Gv3     | 427     | BERMUDA (U.K.)                                         |
| Gv1     | 309     | BOLIVIA                                                |
| Gv2     | 302     | BOLIVIA                                                |
| Gv3     | 302     | BOLIVIA                                                |
| Gv2     | 607     | BOSNIA AND HERZEGOVINA                                 |
| Gv3     | 607     | BOSNIA AND HERZEGOVINA                                 |
| Gv1     | 152     | BOTSWANA                                               |
| Gv2     | 104     | BOTSWANA                                               |
| Gv3     | 104     | BOTSWANA                                               |
| Gv1     | 306     | BRAZIL                                                 |
| Gv2     | 303     | BRAZIL                                                 |
| Gv3     | 303     | BRAZIL                                                 |
| Gv3     | 147     | BRITISH OVERSEAS TERRITORIES                           |
| Gv2     | 428     | BRITISH VIRGIN ISLANDS (U.K.)                          |
| Gv3     | 428     | BRITISH VIRGIN ISLANDS (U.K.)                          |
| Gv2     | 522     | BRUNEI                                                 |
| Gv3     | 522     | BRUNEI                                                 |
| Gv1     | 516     | BRUNEI (U.K.)                                          |
| Gv1     | 626     | BULGARIA                                               |
| Gv2     | 608     | BULGARIA                                               |
| Gv3     | 608     | BULGARIA                                               |
| Gv1     | 141     | BURKINA FASO                                           |
| Gv2     | 105     | BURKINA FASO                                           |
| Gv3     | 105     | BURKINA FASO                                           |
| Gv1     | 225     | BURMA                                                  |
| Gv1     | 130     | BURUNDI                                                |
| Gv2     | 106     | BURUNDI                                                |
| Gv3     | 106     | BURUNDI                                                |
| Gv2     | 204     | CAMBODIA                                               |
| Gv3     | 204     | CAMBODIA                                               |
| Gv1     | 136     | CAMEROON                                               |
| Gv2     | 107     | CAMEROON                                               |
| Gv3     | 107     | CAMEROON                                               |
| Gv1     | 402     | CANADA                                                 |
| Gv1     | 402     | CANADA                                                 |
| Gv2     | 403     | CANADA                                                 |
| Gv3     | 403     | CANADA                                                 |
| Gv2     | 159     | CANARY ISLANDS (SPAIN)                                 |
| Gv3     | 159     | CANARY ISLANDS (SPAIN)                                 |
| Gv1     | 101     | CAPE VERDE                                             |
| Gv2     | 108     | CAPE VERDE                                             |
| Gv3     | 108     | CAPE VERDE                                             |
| Gv2     | 429     | CAYMAN ISLANDS (U.K.)                                  |
| Gv3     | 429     | CAYMAN ISLANDS (U.K.)                                  |
| Gv1     | 133     | CENTRAL AFRICAN REPUBLIC                               |
| Gv2     | 109     | CENTRAL AFRICAN REPUBLIC                               |
| Gv3     | 109     | CENTRAL AFRICAN REPUBLIC                               |
| Gv1     | 104     | CEUTA (SPAIN)                                          |
| Gv2     | 160     | CEUTA (SPAIN)                                          |
| Gv3     | 160     | CEUTA (SPAIN)                                          |
| Gv1     | 134     | CHAD                                                   |
| Gv2     | 110     | CHAD                                                   |
| Gv3     | 110     | CHAD                                                   |
| Gv2     | 161     | CHAGOS ARCHIPELAGO (U.K.)                              |
| Gv3     | 161     | CHAGOS ARCHIPELAGO (U.K.)                              |
| Gv1     | 310     | CHILE                                                  |
| Gv2     | 304     | CHILE                                                  |
| Gv3     | 304     | CHILE                                                  |
| Gv1     | 230     | CHINA                                                  |
| Gv2     | 205     | CHINA                                                  |
| Gv3     | 205     | CHINA                                                  |
| Gv2     | 523     | CHRISTMAS ISLAND (AUSTRALIA)                           |
| Gv3     | 523     | CHRISTMAS ISLAND (AUSTRALIA)                           |
| Gv2     | 524     | COCOS ISLANDS (AUSTRALIA)                              |
| Gv3     | 524     | COCOS ISLANDS (AUSTRALIA)                              |
| Gv1     | 301     | COLOMBIA                                               |
| Gv2     | 305     | COLOMBIA                                               |
| Gv3     | 305     | COLOMBIA                                               |
| Gv1     | 145     | COMOROS                                                |
| Gv2     | 111     | COMOROS                                                |
| Gv3     | 111     | COMOROS                                                |
| Gv1     | 131     | CONGO                                                  |
| Gv2     | 112     | CONGO                                                  |
| Gv3     | 112     | CONGO                                                  |
| Gv2     | 525     | COOK ISLANDS (NEW ZEALAND)                             |
| Gv3     | 525     | COOK ISLANDS (NEW ZEALAND)                             |
| Gv2     | 526     | CORAL SEA ISLANDS (AUSTRALIA)                          |
| Gv3     | 526     | CORAL SEA ISLANDS (AUSTRALIA)                          |
| Gv1     | 416     | COSTA RICA                                             |
| Gv2     | 405     | COSTA RICA                                             |
| Gv3     | 405     | COSTA RICA                                             |
| Gv2     | 113     | COTE D'IVOIRE                                          |
| Gv3     | 113     | COTE D'IVOIRE                                          |
| Gv2     | 609     | CROATIA                                                |
| Gv3     | 609     | CROATIA                                                |
| Gv1     | 407     | CUBA                                                   |
| Gv2     | 406     | CUBA                                                   |
| Gv3     | 406     | CUBA                                                   |
| Gv1     | 631     | CYPRUS                                                 |
| Gv2     | 610     | CYPRUS                                                 |
| Gv3     | 610     | CYPRUS                                                 |
| Gv2     | 611     | CZECH REPUBLIC                                         |
| Gv3     | 611     | CZECH REPUBLIC                                         |
| Gv1     | 620     | CZECHOSLOVAKIA                                         |
| Gv2     | 206     | DEMOCRATIC PEOPLE'S REPUBLIC OF KOREA                  |
| Gv3     | 206     | DEMOCRATIC PEOPLE'S REPUBLIC OF KOREA                  |
| Gv2     | 612     | DENMARK                                                |
| Gv3     | 612     | DENMARK                                                |
| Gv1     | 608     | DENMARK (INCLUDING FAEROE ISLANDS)                     |
| Gv1     | 122     | DJIBOUTI                                               |
| Gv2     | 114     | DJIBOUTI                                               |
| Gv3     | 114     | DJIBOUTI                                               |
| Gv1     | 419     | DOMINICA                                               |
| Gv2     | 430     | DOMINICA                                               |
| Gv3     | 430     | DOMINICA                                               |
| Gv1     | 410     | DOMINICAN REPUBLIC                                     |
| Gv2     | 407     | DOMINICAN REPUBLIC                                     |
| Gv3     | 407     | DOMINICAN REPUBLIC                                     |
| Gv1     | 307     | ECUADOR                                                |
| Gv2     | 306     | ECUADOR                                                |
| Gv3     | 306     | ECUADOR                                                |
| Gv1     | 119     | EGYPT                                                  |
| Gv2     | 115     | EGYPT                                                  |
| Gv3     | 115     | EGYPT                                                  |
| Gv1     | 413     | EL SALVADOR                                            |
| Gv2     | 408     | EL SALVADOR                                            |
| Gv3     | 408     | EL SALVADOR                                            |
| Gv1     | 135     | EQUATORIAL GUINEA                                      |
| Gv2     | 116     | ERITREA                                                |
| Gv3     | 116     | ERITREA                                                |
| Gv2     | 613     | ESTONIA                                                |
| Gv3     | 613     | ESTONIA                                                |
| Gv1     | 121     | ETHIOPIA                                               |
| Gv1     | 121     | ETHIOPIA                                               |
| Gv2     | 117     | ETHIOPIA                                               |
| Gv3     | 117     | ETHIOPIA                                               |
| Gv2     | 316     | FALKLAND ISLANDS (U.K.)                                |
| Gv3     | 316     | FALKLAND ISLANDS (U.K.)                                |
| Gv2     | 652     | FAROE ISLANDS (DENMARK)                                |
| Gv3     | 652     | FAROE ISLANDS (DENMARK)                                |
| Gv2     | 527     | FEDERATED STATES OF MICRONESIA                         |
| Gv3     | 527     | FEDERATED STATES OF MICRONESIA                         |
| Gv1     | 508     | FIJI                                                   |
| Gv2     | 502     | FIJI                                                   |
| Gv3     | 502     | FIJI                                                   |
| Gv1     | 603     | FINLAND                                                |
| Gv2     | 614     | FINLAND                                                |
| Gv3     | 614     | FINLAND                                                |
| Gv1     | 614     | FRANCE                                                 |
| Gv2     | 615     | FRANCE                                                 |
| Gv3     | 615     | FRANCE                                                 |
| Gv1     | 305     | FRENCH GUIANA                                          |
| Gv2     | 315     | FRENCH GUIANA (FRANCE)                                 |
| Gv3     | 315     | FRENCH GUIANA (FRANCE)                                 |
| Gv2     | 528     | FRENCH POLYNESIA (FRANCE)                              |
| Gv3     | 528     | FRENCH POLYNESIA (FRANCE)                              |
| Gv1     | 132     | GABON                                                  |
| Gv2     | 118     | GABON                                                  |
| Gv3     | 118     | GABON                                                  |
| Gv1     | 112     | GAMBIA                                                 |
| Gv2     | 616     | GEORGIA                                                |
| Gv3     | 616     | GEORGIA                                                |
| Gv1     | 618     | GERMANY                                                |
| Gv2     | 617     | GERMANY                                                |
| Gv3     | 617     | GERMANY                                                |
| Gv1     | 140     | GHANA                                                  |
| Gv2     | 119     | GHANA                                                  |
| Gv3     | 119     | GHANA                                                  |
| Gv1     | 616     | GIBRALTAR (U.K.)                                       |
| Gv2     | 653     | GIBRALTAR (U.K.)                                       |
| Gv3     | 653     | GIBRALTAR (U.K.)                                       |
| Gv1     | 629     | GREECE                                                 |
| Gv2     | 618     | GREECE                                                 |
| Gv3     | 618     | GREECE                                                 |
| Gv1     | 607     | GREENLAND (DENMARK)                                    |
| Gv2     | 431     | GREENLAND (DENMARK)                                    |
| Gv3     | 431     | GREENLAND (DENMARK)                                    |
| Gv1     | 423     | GRENADA                                                |
| Gv2     | 409     | GRENADA                                                |
| Gv3     | 409     | GRENADA                                                |
| Gv2     | 432     | GUADELOUPE (FRANCE)                                    |
| Gv3     | 432     | GUADELOUPE (FRANCE)                                    |
| Gv2     | 529     | GUAM (U.S.A.)                                          |
| Gv3     | 529     | GUAM (U.S.A.)                                          |
| Gv1     | 412     | GUATEMALA                                              |
| Gv2     | 410     | GUATEMALA                                              |
| Gv3     | 410     | GUATEMALA                                              |
| Gv1     | 114     | GUINEA                                                 |
| Gv2     | 120     | GUINEA                                                 |
| Gv3     | 120     | GUINEA                                                 |
| Gv1     | 113     | GUINEA-BISSAU                                          |
| Gv2     | 121     | GUINEA-BISSAU                                          |
| Gv3     | 121     | GUINEA-BISSAU                                          |
| Gv1     | 303     | GUYANA                                                 |
| Gv2     | 307     | GUYANA                                                 |
| Gv3     | 307     | GUYANA                                                 |
| Gv1     | 409     | HAITI                                                  |
| Gv2     | 411     | HAITI                                                  |
| Gv3     | 411     | HAITI                                                  |
| Gv1     | 414     | HONDURAS                                               |
| Gv2     | 412     | HONDURAS                                               |
| Gv3     | 412     | HONDURAS                                               |
| Gv1     | 220     | HONG KONG (U.K.)                                       |
| Gv1     | 220     | HONG KONG (U.K.)                                       |
| Gv1     | 622     | HUNGARY                                                |
| Gv2     | 619     | HUNGARY                                                |
| Gv3     | 619     | HUNGARY                                                |
| Gv1     | 606     | ICELAND                                                |
| Gv2     | 620     | ICELAND                                                |
| Gv3     | 620     | ICELAND                                                |
| Gv1     | 215     | INDIA                                                  |
| Gv2     | 207     | INDIA                                                  |
| Gv3     | 207     | INDIA                                                  |
| Gv1     | 515     | INDONESIA                                              |
| Gv1     | 515     | INDONESIA                                              |
| Gv1     | 515     | INDONESIA                                              |
| Gv1     | 515     | INDONESIA                                              |
| Gv2     | 503     | INDONESIA                                              |
| Gv3     | 503     | INDONESIA                                              |
| Gv1     | 205     | IRAN                                                   |
| Gv2     | 208     | IRAN                                                   |
| Gv3     | 208     | IRAN                                                   |
| Gv1     | 204     | IRAQ                                                   |
| Gv2     | 209     | IRAQ                                                   |
| Gv3     | 209     | IRAQ                                                   |
| Gv1     | 605     | IRELAND                                                |
| Gv2     | 621     | IRELAND                                                |
| Gv3     | 621     | IRELAND                                                |
| Gv1     | 635     | ISRAEL                                                 |
| Gv2     | 622     | ISRAEL                                                 |
| Gv3     | 622     | ISRAEL                                                 |
| Gv1     | 627     | ITALY                                                  |
| Gv2     | 623     | ITALY                                                  |
| Gv3     | 623     | ITALY                                                  |
| Gv1     | 142     | IVORY COAST                                            |
| Gv1     | 408     | JAMAICA                                                |
| Gv2     | 413     | JAMAICA                                                |
| Gv3     | 413     | JAMAICA                                                |
| Gv1     | 224     | JAPAN                                                  |
| Gv2     | 210     | JAPAN                                                  |
| Gv3     | 210     | JAPAN                                                  |
| Gv2     | 530     | JOHNSTON ATOLL (U.S.A.)                                |
| Gv3     | 530     | JOHNSTON ATOLL (U.S.A.)                                |
| Gv1     | 636     | JORDAN                                                 |
| Gv2     | 624     | JORDAN                                                 |
| Gv3     | 624     | JORDAN                                                 |
| Gv1     | 229     | KAMPUCHEA                                              |
| Gv2     | 625     | KAZAKHSTAN                                             |
| Gv2     | 211     | KAZAKHSTAN                                             |
| Gv3     | 625     | KAZAKHSTAN                                             |
| Gv3     | 211     | KAZAKHSTAN                                             |
| Gv1     | 124     | KENYA                                                  |
| Gv2     | 122     | KENYA                                                  |
| Gv3     | 122     | KENYA                                                  |
| Gv1     | 506     | KIRIBATI                                               |
| Gv2     | 504     | KIRIBATI                                               |
| Gv3     | 504     | KIRIBATI                                               |
| Gv1     | 203     | KUWAIT                                                 |
| Gv2     | 212     | KUWAIT                                                 |
| Gv3     | 212     | KUWAIT                                                 |
| Gv2     | 213     | KYRGYZSTAN                                             |
| Gv3     | 213     | KYRGYZSTAN                                             |
| Gv1     | 228     | LAOS                                                   |
| Gv2     | 214     | LAOS                                                   |
| Gv3     | 214     | LAOS                                                   |
| Gv2     | 626     | LATVIA                                                 |
| Gv3     | 626     | LATVIA                                                 |
| Gv1     | 634     | LEBANON                                                |
| Gv2     | 627     | LEBANON                                                |
| Gv3     | 627     | LEBANON                                                |
| Gv1     | 155     | LESOTHO                                                |
| Gv2     | 162     | LESOTHO                                                |
| Gv3     | 162     | LESOTHO                                                |
| Gv1     | 143     | LIBERIA                                                |
| Gv2     | 123     | LIBERIA                                                |
| Gv3     | 123     | LIBERIA                                                |
| Gv1     | 118     | LIBYA                                                  |
| Gv2     | 124     | LIBYA                                                  |
| Gv3     | 124     | LIBYA                                                  |
| Gv1     | 613     | LIECHTENSTEIN                                          |
| Gv2     | 628     | LITHUANIA                                              |
| Gv3     | 628     | LITHUANIA                                              |
| Gv1     | 611     | LUXEMBOURG                                             |
| Gv2     | 629     | LUXEMBOURG                                             |
| Gv3     | 629     | LUXEMBOURG                                             |
| Gv1     | 221     | MACAU (PORTUGAL)                                       |
| Gv2     | 234     | MACAU (PORTUGAL)                                       |
| Gv3     | 234     | MACAU (PORTUGAL)                                       |
| Gv2     | 648     | MACEDONIA                                              |
| Gv3     | 648     | MACEDONIA                                              |
| Gv1     | 146     | MADAGASCAR                                             |
| Gv2     | 125     | MADAGASCAR                                             |
| Gv3     | 125     | MADAGASCAR                                             |
| Gv2     | 654     | MADEIRA ISLANDS (PORTUGAL)                             |
| Gv3     | 654     | MADEIRA ISLANDS (PORTUGAL)                             |
| Gv1     | 149     | MALAWI                                                 |
| Gv2     | 126     | MALAWI                                                 |
| Gv3     | 126     | MALAWI                                                 |
| Gv1     | 501     | MALAYSIA                                               |
| Gv1     | 501     | MALAYSIA                                               |
| Gv2     | 505     | MALAYSIA                                               |
| Gv3     | 505     | MALAYSIA                                               |
| Gv1     | 217     | MALDIVES                                               |
| Gv2     | 235     | MALDIVES                                               |
| Gv3     | 235     | MALDIVES                                               |
| Gv1     | 109     | MALI                                                   |
| Gv2     | 127     | MALI                                                   |
| Gv3     | 127     | MALI                                                   |
| Gv1     | 628     | MALTA                                                  |
| Gv2     | 630     | MALTA                                                  |
| Gv3     | 630     | MALTA                                                  |
| Gv2     | 531     | MARSHALL ISLANDS                                       |
| Gv3     | 531     | MARSHALL ISLANDS                                       |
| Gv2     | 433     | MARTINIQUE (FRANCE)                                    |
| Gv3     | 433     | MARTINIQUE (FRANCE)                                    |
| Gv1     | 110     | MAURITANIA                                             |
| Gv2     | 128     | MAURITANIA                                             |
| Gv3     | 128     | MAURITANIA                                             |
| Gv1     | 117     | MAURITIUS                                              |
| Gv2     | 129     | MAURITIUS                                              |
| Gv3     | 129     | MAURITIUS                                              |
| Gv2     | 163     | MAYOTTE (FRANCE)                                       |
| Gv3     | 163     | MAYOTTE (FRANCE)                                       |
| Gv1     | 105     | MELILLA (SPAIN)                                        |
| Gv2     | 164     | MELILLA (SPAIN)                                        |
| Gv3     | 164     | MELILLA (SPAIN)                                        |
| Gv1     | 405     | MEXICO                                                 |
| Gv2     | 414     | MEXICO                                                 |
| Gv3     | 414     | MEXICO                                                 |
| Gv2     | 631     | MOLDOVA                                                |
| Gv3     | 631     | MOLDOVA                                                |
| Gv1     | 218     | MONGOLIA                                               |
| Gv2     | 215     | MONGOLIA                                               |
| Gv3     | 215     | MONGOLIA                                               |
| Gv2     | 632     | MONTENEGRO                                             |
| Gv3     | 632     | MONTENEGRO                                             |
| Gv1     | 103     | MOROCCO                                                |
| Gv1     | 103     | MOROCCO                                                |
| Gv1     | 103     | MOROCCO                                                |
| Gv2     | 130     | MOROCCO                                                |
| Gv3     | 130     | MOROCCO                                                |
| Gv1     | 147     | MOZAMBIQUE                                             |
| Gv2     | 131     | MOZAMBIQUE                                             |
| Gv3     | 131     | MOZAMBIQUE                                             |
| Gv2     | 216     | MYANMAR                                                |
| Gv3     | 216     | MYANMAR                                                |
| Gv1     | 151     | NAMIBIA                                                |
| Gv2     | 132     | NAMIBIA                                                |
| Gv3     | 132     | NAMIBIA                                                |
| Gv1     | 504     | NAURU                                                  |
| Gv2     | 506     | NAURU                                                  |
| Gv3     | 506     | NAURU                                                  |
| Gv1     | 219     | NEPAL                                                  |
| Gv2     | 217     | NEPAL                                                  |
| Gv3     | 217     | NEPAL                                                  |
| Gv1     | 609     | NETHERLANDS                                            |
| Gv2     | 633     | NETHERLANDS                                            |
| Gv3     | 633     | NETHERLANDS                                            |
| Gv2     | 434     | NETHERLANDS ANTILLES (NETHERLANDS)                     |
| Gv3     | 434     | NETHERLANDS ANTILLES (NETHERLANDS)                     |
| Gv2     | 532     | NEW CALEDONIA (FRANCE)                                 |
| Gv3     | 532     | NEW CALEDONIA (FRANCE)                                 |
| Gv1     | 511     | NEW ZEALAND                                            |
| Gv2     | 507     | NEW ZEALAND                                            |
| Gv3     | 507     | NEW ZEALAND                                            |
| Gv1     | 415     | NICARAGUA                                              |
| Gv2     | 415     | NICARAGUA                                              |
| Gv3     | 415     | NICARAGUA                                              |
| Gv1     | 108     | NIGER                                                  |
| Gv2     | 133     | NIGER                                                  |
| Gv3     | 133     | NIGER                                                  |
| Gv1     | 137     | NIGERIA                                                |
| Gv2     | 134     | NIGERIA                                                |
| Gv3     | 134     | NIGERIA                                                |
| Gv2     | 533     | NIUE (NEW ZEALAND)                                     |
| Gv3     | 533     | NIUE (NEW ZEALAND)                                     |
| Gv2     | 534     | NORFOLK ISLAND (AUSTRALIA)                             |
| Gv3     | 534     | NORFOLK ISLAND (AUSTRALIA)                             |
| Gv1     | 222     | NORTH KOREA                                            |
| Gv2     | 535     | NORTHERN MARIANA ISLANDS (U.S.A.)                      |
| Gv3     | 535     | NORTHERN MARIANA ISLANDS (U.S.A.)                      |
| Gv2     | 634     | NORWAY                                                 |
| Gv3     | 634     | NORWAY                                                 |
| Gv1     | 601     | NORWAY (INCLUDING JAN MAYEN AND SVALBARD)              |
| Gv1     | 210     | OMAN                                                   |
| Gv2     | 218     | OMAN                                                   |
| Gv3     | 218     | OMAN                                                   |
| Gv1     | 213     | PAKISTAN                                               |
| Gv2     | 219     | PAKISTAN                                               |
| Gv3     | 219     | PAKISTAN                                               |
| Gv1     | 417     | PANAMA                                                 |
| Gv2     | 416     | PANAMA                                                 |
| Gv3     | 416     | PANAMA                                                 |
| Gv1     | 512     | PAPUA NEW GUINEA                                       |
| Gv2     | 508     | PAPUA NEW GUINEA                                       |
| Gv3     | 508     | PAPUA NEW GUINEA                                       |
| Gv1     | 311     | PARAGUAY                                               |
| Gv2     | 308     | PARAGUAY                                               |
| Gv3     | 308     | PARAGUAY                                               |
| Gv1     | 212     | PEOPLE'S DEMOCRATIC REPUBLIC OF YEMEN                  |
| Gv1     | 308     | PERU                                                   |
| Gv2     | 309     | PERU                                                   |
| Gv3     | 309     | PERU                                                   |
| Gv1     | 517     | PHILIPPINES                                            |
| Gv2     | 509     | PHILIPPINES                                            |
| Gv3     | 509     | PHILIPPINES                                            |
| Gv2     | 537     | PITCAIRN ISLAND (U.K.)                                 |
| Gv3     | 537     | PITCAIRN ISLAND (U.K.)                                 |
| Gv1     | 621     | POLAND                                                 |
| Gv2     | 635     | POLAND                                                 |
| Gv3     | 635     | POLAND                                                 |
| Gv1     | 617     | PORTUGAL                                               |
| Gv1     | 617     | PORTUGAL                                               |
| Gv1     | 617     | PORTUGAL                                               |
| Gv2     | 636     | PORTUGAL                                               |
| Gv3     | 636     | PORTUGAL                                               |
| Gv2     | 435     | PUERTO RICO (U.S.A.)                                   |
| Gv3     | 435     | PUERTO RICO (U.S.A.)                                   |
| Gv1     | 208     | QATAR                                                  |
| Gv2     | 220     | QATAR                                                  |
| Gv3     | 220     | QATAR                                                  |
| Gv2     | 221     | REPUBLIC OF KOREA                                      |
| Gv3     | 221     | REPUBLIC OF KOREA                                      |
| Gv2     | 165     | REUNION ISLAND (FRANCE)                                |
| Gv3     | 165     | REUNION ISLAND (FRANCE)                                |
| Gv1     | 625     | ROMANIA                                                |
| Gv2     | 637     | ROMANIA                                                |
| Gv3     | 637     | ROMANIA                                                |
| Gv2     | 222     | RUSSIAN FEDERATION (ASIAN SECTOR)                      |
| Gv3     | 222     | RUSSIAN FEDERATION (ASIAN SECTOR)                      |
| Gv2     | 638     | RUSSIAN FEDERATION (EUROPEAN SECTOR)                   |
| Gv3     | 638     | RUSSIAN FEDERATION (EUROPEAN SECTOR)                   |
| Gv1     | 129     | RWANDA                                                 |
| Gv2     | 166     | RWANDA                                                 |
| Gv3     | 166     | RWANDA                                                 |
| Gv2     | 417     | SAINT KITTS AND NEVIS                                  |
| Gv3     | 417     | SAINT KITTS AND NEVIS                                  |
| Gv1     | 420     | SAINT LUCIA                                            |
| Gv2     | 436     | SAINT LUCIA                                            |
| Gv3     | 436     | SAINT LUCIA                                            |
| Gv2     | 438     | SAINT PIERRE & MIQUELON ISLAND (FRANCE)                |
| Gv3     | 438     | SAINT PIERRE & MIQUELON ISLAND (FRANCE)                |
| Gv1     | 403     | SAINT PIERRE AND MIQUELON (FRANCE)                     |
| Gv1     | 421     | SAINT VINCENT AND THE GRENADINES                       |
| Gv2     | 437     | SAINT VINCENT AND THE GRENADINES                       |
| Gv3     | 437     | SAINT VINCENT AND THE GRENADINES                       |
| Gv2     | 541     | SAMOA                                                  |
| Gv3     | 541     | SAMOA                                                  |
| Gv1     | 116     | SAO TOME AND PRINCIPE                                  |
| Gv2     | 136     | SAO TOME AND PRINCIPE                                  |
| Gv3     | 136     | SAO TOME AND PRINCIPE                                  |
| Gv1     | 202     | SAUDI ARABIA                                           |
| Gv1     | 202     | SAUDI ARABIA                                           |
| Gv2     | 223     | SAUDI ARABIA                                           |
| Gv3     | 223     | SAUDI ARABIA                                           |
| Gv1     | 111     | SENEGAL                                                |
| Gv2     | 137     | SENEGAL                                                |
| Gv3     | 137     | SENEGAL                                                |
| Gv2     | 639     | SERBIA                                                 |
| Gv3     | 639     | SERBIA                                                 |
| Gv1     | 127     | SEYCHELLES                                             |
| Gv2     | 138     | SEYCHELLES                                             |
| Gv3     | 138     | SEYCHELLES                                             |
| Gv2     | 800     | SHIP STATIONS                                          |
| Gv3     | 800     | SHIP STATIONS                                          |
| Gv1     | 115     | SIERRA LEONE                                           |
| Gv2     | 139     | SIERRA LEONE                                           |
| Gv3     | 139     | SIERRA LEONE                                           |
| Gv1     | 502     | SINGAPORE                                              |
| Gv2     | 511     | SINGAPORE                                              |
| Gv3     | 511     | SINGAPORE                                              |
| Gv2     | 641     | SLOVAKIA                                               |
| Gv3     | 641     | SLOVAKIA                                               |
| Gv3     | 642     | SLOVENIA                                               |
| Gv1     | 503     | SOLOMON ISLANDS                                        |
| Gv2     | 512     | SOLOMON ISLANDS                                        |
| Gv3     | 512     | SOLOMON ISLANDS                                        |
| Gv1     | 123     | SOMALIA                                                |
| Gv2     | 140     | SOMALIA                                                |
| Gv3     | 140     | SOMALIA                                                |
| Gv1     | 153     | SOUTH AFRICA                                           |
| Gv2     | 141     | SOUTH AFRICA                                           |
| Gv3     | 141     | SOUTH AFRICA                                           |
| Gv2     | 317     | SOUTH GEORGIA (U.K.)                                   |
| Gv3     | 317     | SOUTH GEORGIA (U.K.)                                   |
| Gv1     | 223     | SOUTH KOREA                                            |
| Gv1     | 615     | SPAIN                                                  |
| Gv2     | 643     | SPAIN                                                  |
| Gv3     | 643     | SPAIN                                                  |
| Gv1     | 216     | SRI LANKA                                              |
| Gv2     | 224     | SRI LANKA                                              |
| Gv3     | 224     | SRI LANKA                                              |
| Gv1     | 120     | SUDAN                                                  |
| Gv2     | 148     | SUDAN                                                  |
| Gv3     | 148     | SUDAN                                                  |
| Gv1     | 304     | SURINAME                                               |
| Gv2     | 312     | SURINAME                                               |
| Gv3     | 312     | SURINAME                                               |
| Gv1     | 154     | SWAZILAND                                              |
| Gv2     | 167     | SWAZILAND                                              |
| Gv3     | 167     | SWAZILAND                                              |
| Gv1     | 602     | SWEDEN                                                 |
| Gv2     | 645     | SWEDEN                                                 |
| Gv3     | 645     | SWEDEN                                                 |
| Gv1     | 612     | SWITZERLAND                                            |
| Gv2     | 646     | SWITZERLAND                                            |
| Gv3     | 646     | SWITZERLAND                                            |
| Gv1     | 633     | SYRIA                                                  |
| Gv2     | 647     | SYRIA                                                  |
| Gv3     | 647     | SYRIA                                                  |
| Gv2     | 236     | TAIWAN                                                 |
| Gv3     | 236     | TAIWAN                                                 |
| Gv2     | 227     | TAJIKISTAN                                             |
| Gv3     | 227     | TAJIKISTAN                                             |
| Gv1     | 125     | TANZANIA                                               |
| Gv2     | 149     | TANZANIA                                               |
| Gv3     | 149     | TANZANIA                                               |
| Gv3     | 143     | TERRITORY OF THE FRENCH SOUTHERN AND ANTARCTIC LANDS   |
| Gv1     | 226     | THAILAND                                               |
| Gv2     | 228     | THAILAND                                               |
| Gv3     | 228     | THAILAND                                               |
| Gv2     | 423     | THE BAHAMAS                                            |
| Gv3     | 423     | THE BAHAMAS                                            |
| Gv2     | 150     | THE GAMBIA                                             |
| Gv3     | 150     | THE GAMBIA                                             |
| Gv1     | 139     | TOGO                                                   |
| Gv2     | 151     | TOGO                                                   |
| Gv3     | 151     | TOGO                                                   |
| Gv2     | 538     | TOKELAU                                                |
| Gv3     | 538     | TOKELAU                                                |
| Gv1     | 510     | TONGA                                                  |
| Gv2     | 517     | TONGA                                                  |
| Gv3     | 517     | TONGA                                                  |
| Gv1     | 424     | TRINIDAD AND TOBAGO                                    |
| Gv2     | 424     | TRINIDAD AND TOBAGO                                    |
| Gv3     | 424     | TRINIDAD AND TOBAGO                                    |
| Gv2     | 168     | TROMELIN ISLAND (FRANCE)                               |
| Gv3     | 168     | TROMELIN ISLAND (FRANCE)                               |
| Gv1     | 107     | TUNISIA                                                |
| Gv2     | 152     | TUNISIA                                                |
| Gv3     | 152     | TUNISIA                                                |
| Gv1     | 630     | TURKEY                                                 |
| Gv2     | 649     | TURKEY                                                 |
| Gv3     | 649     | TURKEY                                                 |
| Gv2     | 229     | TURKMENISTAN                                           |
| Gv3     | 229     | TURKMENISTAN                                           |
| Gv2     | 439     | TURKS AND CAICOS ISLANDS                               |
| Gv3     | 439     | TURKS AND CAICOS ISLANDS                               |
| Gv1     | 507     | TUVALU                                                 |
| Gv2     | 518     | TUVALU                                                 |
| Gv3     | 518     | TUVALU                                                 |
| Gv1     | 126     | UGANDA                                                 |
| Gv2     | 153     | UGANDA                                                 |
| Gv3     | 153     | UGANDA                                                 |
| Gv2     | 650     | UKRAINE                                                |
| Gv3     | 650     | UKRAINE                                                |
| Gv1     | 632     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 632     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 632     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 201     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 201     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 201     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 201     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 201     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 201     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 632     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 632     | UNION OF SOVIET SOCIALIST REPUBLICS                    |
| Gv1     | 209     | UNITED ARAB EMIRATES                                   |
| Gv2     | 230     | UNITED ARAB EMIRATES                                   |
| Gv3     | 230     | UNITED ARAB EMIRATES                                   |
| Gv1     | 604     | UNITED KINGDOM                                         |
| Gv2     | 651     | UNITED KINGDOM                                         |
| Gv3     | 651     | UNITED KINGDOM                                         |
| Gv2     | 425     | UNITED STATES OF AMERICA                               |
| Gv3     | 425     | UNITED STATES OF AMERICA                               |
| Gv1     | 404     | UNITED STATES OF AMERICA (INCLUDING ALASKA)            |
| Gv1     | 404     | UNITED STATES OF AMERICA (INCLUDING ALASKA)            |
| Gv1     | 312     | URUGUAY                                                |
| Gv2     | 313     | URUGUAY                                                |
| Gv3     | 313     | URUGUAY                                                |
| Gv2     | 231     | UZBEKISTAN                                             |
| Gv3     | 231     | UZBEKISTAN                                             |
| Gv1     | 505     | VANUATU                                                |
| Gv2     | 520     | VANUATU                                                |
| Gv3     | 520     | VANUATU                                                |
| Gv1     | 302     | VENEZUELA                                              |
| Gv2     | 314     | VENEZUELA                                              |
| Gv3     | 314     | VENEZUELA                                              |
| Gv1     | 227     | VIET NAM                                               |
| Gv2     | 232     | VIETNAM                                                |
| Gv3     | 232     | VIETNAM                                                |
| Gv2     | 440     | VIRGIN ISLANDS (U.S.A.)                                |
| Gv3     | 440     | VIRGIN ISLANDS (U.S.A.)                                |
| Gv2     | 539     | WAKE ISLAND (U.S.A.)                                   |
| Gv3     | 539     | WAKE ISLAND (U.S.A.)                                   |
| Gv2     | 540     | WALLIS AND FUTUNA (FRANCE)                             |
| Gv3     | 540     | WALLIS AND FUTUNA (FRANCE)                             |
| Gv1     | 102     | WESTERN SAHARA                                         |
| Gv1     | 102     | WESTERN SAHARA                                         |
| Gv2     | 169     | WESTERN SAHARA (MOROCCO)                               |
| Gv3     | 169     | WESTERN SAHARA (MOROCCO)                               |
| Gv1     | 509     | WESTERN SAMOA                                          |
| Gv1     | 199     | WMO REGION 1 DEPENDENT ISLANDS / OCEAN VESSEL STATIONS |
| Gv1     | 299     | WMO REGION 2 DEPENDENT ISLANDS / OCEAN VESSEL STATIONS |
| Gv1     | 399     | WMO REGION 3 DEPENDENT ISLANDS / OCEAN VESSEL STATIONS |
| Gv1     | 499     | WMO REGION 4 DEPENDENT ISLANDS / OCEAN VESSEL STATIONS |
| Gv1     | 599     | WMO REGION 5 DEPENDENT ISLANDS / OCEAN VESSEL STATIONS |
| Gv1     | 699     | WMO REGION 6 DEPENDENT ISLANDS / OCEAN VESSEL STATIONS |
| Gv1     | 211     | YEMEN                                                  |
| Gv2     | 233     | YEMEN                                                  |
| Gv3     | 233     | YEMEN                                                  |
| Gv1     | 623     | YUGOSLAVIA                                             |
| Gv1     | 128     | ZAIRE                                                  |
| Gv2     | 154     | ZAIRE                                                  |
| Gv3     | 154     | ZAIRE                                                  |
| Gv1     | 148     | ZAMBIA                                                 |
| Gv2     | 155     | ZAMBIA                                                 |
| Gv3     | 155     | ZAMBIA                                                 |
| Gv1     | 150     | ZIMBABWE                                               |
| Gv2     | 156     | ZIMBABWE                                               |
| Gv3     | 156     | ZIMBABWE                                               |
+---------+---------+--------------------------------------------------------+
678 rows in set (0.01 sec)

So looks like Western Sahara got a name change to MOROCCO as an adjective, and Gv1 has the “WMO REGION X” block the others don’t have. Looks also like generally the v2 & v3 country numbers are the same and it is only the v1 that’s the oddball.

I can see using the v2/v3 number as a key index field, and when loading v1 data save the original country code as a data item but map the actual data to the v2/v3 key field. This could take a bit of work, but would make comparison reporting much easier.

Then there’s the issue of the USSR breaking up and we get Russian Federation and some “-ickyStans” added. Sorting that out a bit harder.

There are also some duplicate lines in the report for some countries for v1 data. These are due to the v1 data having a line for each “block” of assigned WMO numbers. Here’s a cut from the input file to show it (yes, I could make this from the MySQL database too, but this was quicker ;-) Scroll the box far to the right to see the WMO numbers.

root@odroidxu4:/SG2/ext/chiefio/SQL/v1# grep AUSTRALIA CCodes.txt 
Gv1  	UNKNOWN   	5	513	AUSTRALIA                                               	94100	94985	
Gv1  	UNKNOWN   	5	513	AUSTRALIA                                               	95000	95499	
Gv1  	UNKNOWN   	5	513	AUSTRALIA                                               	95511	95999	
root@odroidxu4:/SG2/ext/chiefio/SQL/v1#

Oh what the heck, here’s how to do it in MySQL too:

mysql> SELECT start_wmo, end_wmo, country, country_name FROM country WHERE version="Gv1" AND country_name="AUSTRALIA";
+-----------+---------+---------+--------------+
| start_wmo | end_wmo | country | country_name |
+-----------+---------+---------+--------------+
| 94100     | 94985   | 513     | AUSTRALIA    |
| 95000     | 95499   | 513     | AUSTRALIA    |
| 95511     | 95999   | 513     | AUSTRALIA    |
+-----------+---------+---------+--------------+
3 rows in set (0.00 sec)

mysql> 

So that’s a reporting complication. There’s likely some SQL term to make them only one line, but I don’t know it (yet). This does point up that the WMO numbers are not in a simple pattern either. Here we have 3 blocks for Australia in v1. So where are 94986-94999? And the other skipped bits? Who knows… Probably scattered around some islands nearby, I’d guess.

I’ll need to do something similar to this for WMO numbers and see if they are more consistent. It would be nice if the WMO number were constant over the set. Then it could be used as a key field and make country / country_name dependent on it. Somehow I doubt it will be that easy… but “we’ll see”.

FWIW, this is one of the stickier bits in any database design / work. What do you use as a unique key to keep things orderly and easily retrieved / searched? Why the folks collecting this data don’t have ONE consistent key field for things is beyond me… For WMO number is looks like the core 5 digits is fairly constant. They do fiddle around with a few end bits for changes of thermometers or for a thermometer “near” a WMO one. (Yes, if they have data from a thermometer that is NOT a WMO one, they use the WMO number of the nearest one and tack on some bits to say it’s a different one… )

So there’s your table of countries. Hope you like it ;-)

Subscribe to feed

Advertisements

About E.M.Smith

A technical managerial sort interested in things from Stonehenge to computer science. My present "hot buttons' are the mythology of Climate Change and ancient metrology; but things change...
This entry was posted in NCDC - GHCN Issues and tagged , , . Bookmark the permalink.

16 Responses to GHCN Country Code Changes by Version – MySQL results

  1. John F. Hultquist says:

    Why the folks collecting this data don’t have ONE consistent key field for things is beyond me…

    I think I hear someone (way back) saying … “It seemed like a good idea at a time.”

    It is not good for one’s blood pressure to fool with large data sets.

    We worked with some of the 1960 US Census data. The computer code was slow so the boss hired a IBM 360/65 native coder. The problem was that the budget (via a grant) had no money for such a person. The project would not have been done without his abilities I knew FORTRAN and very tiny bits of several other languages, and besides, I was being payed to do something else.

    I wonder how fast our home computers are now compared to the ones we started with:
    IBM 1620, 1410, and 360 series.
    Today I have an Intel i7-3770 CPU @ 3.4 Ghz

  2. E.M.Smith says:

    @John F. H.:

    I’ll look up a benchmark after church….

    IIRC the old IBM boxes were about 10 MIPS with a couple of 10 MB /second disk channels. It was the disk bandwith plus sequential data access that gave them apparent speed.

    For now, my stardard comparison is that my RaspberryPi Model 3 has more of everything than my 1983 or so Cray X-MP/ 48. It was 4 CPUs plus vector units. The Pi is 4 cores plus GPUs. It was 400 MFOPS, the Pi is 1.2 GHz and does more FLOPS… especially with NEON builds of the kernel. The Cray had 64 MegaBytes of memory… though it used ir very efficiently… the Pi has 1 GIGaByte… We had to pay extra for fast network on the Cray… and default was, IIRC, 100 Mbit. It had 30 GB of disk where my Pi has a 32 GB uSD card at similar transfer rates (though the Cray could do much faster random writes due to the SD read a large block, change a bit, rewrite it). I added 2 TBytes of nearline tape robot with 10 MB tranfer rate for $1/2 Million. Just added 8 TBytes of real disk to my Pi at 10 MB tranfer rate, or can put it on my USB 3.0 SBCs at 100 MB rate… cost about $125.

    Cray: $40, 000, 000.00
    R.Pi: $40 (including PSU and added bits…)

    Cray took a 750 kVA power feed. Pi takes a couple of Watts….

  3. E.M.Smith says:

    THE basic failing in the data structure of the climate data is the “point in time” view. Everthing changes over time, but they set countries as a constant (at a point in time) and do worse with instrument data.

    The airport used for The Berlin Airlift was not an airport prior to 1900, yet got an “airstation” flag set as though it had always been. Now it is a shopping mall, so I expect v4 will simply forget it ever was an airport…

    While it would take more space, IMHO, the right keys are likely YEAR and WMO# (where WMO is fully qualified with the added digits). Then things like changes of country, latitude, longitude, type, airstation, and the actual temp data naturally track reality.

    Don’t know if the real historical information still can be found, but that’s how I’m thinking it ought to be…

    That would change my Ascension field a bit, but not much… It would end up something of a sparse file as not all things change in all years. Might complicate some reporting… Hmmmm….

  4. H.R. says:

    You can see how a few country codes were recycled from V1 to V2 and V3. For example, Afghanistan is cc201 for Gv2 & Gv3, but further down the list, Union of Soviet Socialist Republics is cc201 for Gv1.

    Seems a little odd to me, but what do I know? I would have thought that 001 through 999 would handle any country breakups and formations, but I guess there were more than I thought.

  5. Gary says:

    Why the folks collecting this data don’t have ONE consistent key field for things is beyond me…

    No doubt the whole operation has been managed by multiple persons who don’t have the same understanding as their predecessors. Also likely is that there wasn’t a standards manual created at the beginning, or it was written but later lost/ignored. Most researchers are not compulsive about these kind of details. Then natural evolution of hardware, software, and archives take their toll. It surprising the anomalies aren’t more numerous.

  6. jim2 says:

    I do database design from time to time and use Oracle, SQL Server, and DB2.

    I piss off my fellow developers from time to time because I normalize data. They want the data to look like a spreadsheet. It’s OK if they are pissed off because they won’t have to clean up the data when they screw it up, I will.

    Anyway, in this case the thing to do is to have a versions table and a countries table.

    Versions Table
    Version ID Primary Key(int), Version (string), Version Description.

    Countries table

    Country ID Primary Key (int), Version ID (PK from Versions table), Country ID (Int from data), Country Name (from data)

    Now you can have a third table with Country ID (PK from Country table), Version ID (PK from Versions table), Start date, end date, or whatever data pertains to that country and version. It might have to be normalized further depending on what’s there.

    This way, using your own Primary Keys, it doesn’t matter how they hacked up the data they presented to you. You have to use SQL with joins to combine tables for queries, but that’s how it done.

  7. jim2 says:

    For reporting/processing, you would have to “flatten” the data out again using joins in SQL, but you can select only the data you want for the flattened table. Also, it probably will be easier to add another version, especially if it’s different from the others in some ways.

  8. H.R. says:

    @Gary (7:14 pm)

    I think you answered my “whassup wit dat?” question about the recycling of country codes. With ‘no controlling legal authority,’ each someone did what seemed to be the right thing to them at the time.

  9. E.M.Smith says:

    It looks to me like they just listed all the countries in a continent and sequentially numbered them, renumbering for v2, no thought to consistency.

    @Jim2: I was a DBA for years. Yes normalized is in general The Way. Though I tend to not go all the way if a partial makes reporting easier…

  10. jim2 says:

    Yep, normalization is usually used where inserts, updates, and deletes are happening. In the case of the temp data, I was thinking it might help tame the data anomalies.

  11. A C Osborn says:

    Jim, I raised this issue in EM’s first SQL post, as I only built databases in BASIC, Dataease & MS Access it came naturally to break down the data as far as possible.
    I did an NCC Systems Analyst course back in 1983, but really couldn’t be bothered with all the Flow Charts in real life.

  12. andysaurus says:

    I was “only” a business analyst/project manager, but I always knew how valuable a thoughtful and skilled database design was. I started with COSASYL (IDMS-X) where everything had to be considered before loading. Was harder work but extremely efficient when it came to coding if done well.
    Moving to relational databases seemed to remove the need for the same planning, but in fact if one understands third normal form from a theoretical POV, one can make sound decisions on DB design, as Jim2 suggests. In the long run you can package up any complex joins in views (or whatever they are called in the database you are using) and the clean data becomes just as easy to access as a spreadsheet. Or spit it out into a warehouse for reporting. Depends how often you want to do it.
    I think E.M is going to bypass the initial load into a normalised database and go straight to the warehouse. Fair enough if you are going to have few updates. (IMHO).

  13. E.M.Smith says:

    Andysaurus has kind of pegged me on this one…

    The Relational Calculus is based on the notion of “Best on Average for Unknown Needs”. IFF you know exactly what you need, you can design better more efficient layouts in advance. So “normalize” into minimal data stored tables for general purpose future uses, yet a flat file with dramatic data redundancy can be most efficient for a particular known report as you only pass the file once, sequentially (so trading disk bytes for processor cycles and ordered data for disk head seeks).

    In between those two you can tune to your hearts content. Adding a bit of redundancy for convenience later in known needs (like my putting in a region or continent flag that is redundant with the first CHAR of country code – so later selections “by continent” don’t need to fiddle that char out of the field) or grouping data you know could be normalized out in with other data that will frequently be used together anyway. Essentially building a permanent JOIN into your table. I did this in some reports I made a few years ago where I joined (not the DB kind…) the Station Data with the Temperature Data in a giant flat file. Then I could just pass that file once for all sorts of analysis for things like “by airstation flag” et. al.

    The general notion of the Relational Calculus is that, in a typical company running for years, you will not know the nature of future needs, so don’t design your data storage in such a way as to favor one use (today’s) over others (the future). Then use flexible DB tools to glue the views together as you need them. Prior art was to make custom flat files as the particular application demanded and deal with the future later. Both can be “reasonable” depending on your actual needs.

    As pointed out, THE big bugaboo is attempting to do random updates with lots of redundant data. Suddenly your “update this one field” becomes “find all the buggers in 10 copies of flat files with various formats and update them all… don’t miss any!”. Since I’m only loading historical and now static data, that does not apply to what I’m doing, really.

    The other bit is that I’m looking to, essentially, merge the v1, v2, v3, and v4 data into one structure for “compare and contrast”. As I’m not familiar with the changes in v4, there’s a big unknown hanging out there. Thus my willingness to “try a few things I might throw away”. So I’m tossing some “stuff” at the wall partly to measure speed and size issues. So far it is showing itself to be “so small it doesn’t really matter”. Moore’s Law has given enough speed for nearly no $$ that basic select / report steps just take a few seconds even without index fields. Really a bit of a startling surprise compared to some decades back. In essence: Moore’s Law making the choice of trading disk space vs CPU cycles irrelevant.

    FWIW, my DBA attitudes about data normalization and data redundancy got munged a bit doing SuperComputer stuff where, by definition, the problem in question can use all available cycles, and then some; so you apply any and all tricks to make it GoFast. Even to the point of planning your disk head seeks. (Cray disks had 4 sets of heads for any platter to that rotational latency was cut to 1/4 …) So now I’m a bit more “eclectic” in how I view data layout / storage design. There is no absolute “best”, only what works well for this question… but that’s harder when you don’t know for sure what the question might be ;-)

    So, much of what I’m doing Right Now is just finding out “How hard is it to change structure and reload the data?” and “What is the size of the processing for sample questions?” and “What fields are likely to be best for index / key fields?”. Then there’s that whole “Just learn MySQL syntax and data storage concepts.” thing. My favorite and most used (professionally) databases had a basically hierarchical structure with the relational bits as a “glue on” sort of. So I still tend to think in terms of an efficient hierarchical storage with keys at the top and data mass at the bottom; joins of those as ways to deal with ad hoc needs. I know that’s not the case with MySQL, but I’ve not yet got their use of key / index fully soaked into my brain, so I’m playing with it a bit to get that experience base built. ;-)

    What I’ve learned so far is that it’s really really easy to have a data layout (“schema”) file you just suck in, and to have a sequential file of data to load and just suck it in. Means I can fiddle the design of the storage “on the fly” as I like it with a time cost of about 4 minutes / iteration. Essentially nothing. That then means it would be “reasonable” to have custom schema made for particular needs. (Don’t think I’ll do that, as it looks like it will be just as fast to just use joins & such with whatever tables you have…). In short: It doesn’t seem to matter what I do as the XU4 is way more than fast enough and the data are small enough that even a complete reload is fast.

    Now I’m still about an order of magnitude away from final size, so “My mileage will vary” ;-) I’ve loaded one instantiation of the data (v3, one ascension, unadjusted, TAVG). So multiply by 3 (for TAVG, TMAX, TMIN) and x 2 for QA applied and x 4 for version (v1, v2, v3, v4) and by ?? for adding USHCN & Hadley data… Then maybe a couple of more orders of magnitude should I decide to add Daily data… That’s about 24 x to 200 x as a guess. “Someday” I’ll need to think about efficiency…

    But for now I can play with different data structures without worry and discover what works best for the things I want to do. Be that normalized or “tuned for purpose”. While I learn the capabilities of MySQL and its limitations. (More than once I’ve thought “I’ll just do FOO to make that happen” and discovered a particular product didn’t DO “FOO” and I was in a sudden “Go Fish” situation… so it is worth it to learn the limits of a product with some play time…)

    Hopefully that explains some of my willingness to “wander in the dark”… though some of that is just for “show and tell”. To let the non-programmers see what it really is like. Way too many folks just see the final perfectly working thing and think we got it that way in one go. We must be geniuses. The reality is lots of bouncing off walls and tripping over your own shoelaces, so letting that “Bumble Bits” be seen is also a goal… Showing that “anybody can do this”… and it is neither magical nor Godlike. Strip away the funny words and the confounding jargon and just show stuffing bits in a bag-o-bits and sucking them back out again, by name…

  14. andysaurus says:

    Thanks E.M for elucidating your rationale.
    You and I are of a generation before SQL. When I was a PM, I found it hard to understand how university graduates in computer science could not know it. All computing deals with data in one form or another and I would have thought it a basic essential, particularly for business computing.

    Thinking about it some more, I guess there are some forms of computing, such as games development where data storage and retrieval are not key elements.

  15. Larry Ledwick says:

    I think another issue with that, is that DBA’s are very protective of their specialty and most computer programmers, walk into an environment where “those wizards in the DBA team do that” and they work with data which is already setup a particular way and just leave the why and how to the DBA team and focus on the computing problem and just fetch data when they need it from existing files.

  16. jb says:

    SELECT DISTINCT start_wmo, end_wmo, country, country_name FROM country WHERE version=”Gv1″ AND country_name=”AUSTRALIA”;

Anything to say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.