Author Topic: State/provice abbreviations for 47 countries display as digits (Read 1231 times)

awjrichards · August 31, 2010, 11:15:01 am

In CiviCRM 3.1.6, the state/province abbreviations for 47 countries are digits rather than actual abbreviations:

mysql> select s.country_id, c.name from civicrm_state_province s,
civicrm_country c where c.id = s.country_id and s.abbreviation rlike '[0-9]'
group by s.country_id;
+------------+--------------------------------------+
| country_id | name                                 |
+------------+--------------------------------------+
|       1003 | Algeria                              | 
|       1014 | Austria                              | 
|       1016 | Bahrain                              | 
|       1017 | Bangladesh                           | 
|       1024 | Bhutan                               | 
|       1033 | Bulgaria                             | 
|       1035 | Myanmar                              | 
|       1037 | Cambodia                             | 
|       1045 | China                                | 
|       1051 | Congo                                | 
|       1054 | C�te d'Ivoire                        | 
|       1055 | Croatia                              | 
|       1056 | Cuba                                 | 
|       1057 | Cyprus                               | 
|       1059 | Denmark                              | 
|       1062 | Dominican Republic                   | 
|       1069 | Estonia                              | 
|       1076 | France                               | 
|       1085 | Greece                               | 
|       1100 | Iceland                              | 
|       1103 | Iran, Islamic Republic of            | 
|       1109 | Japan                                | 
|       1112 | Kenya                                | 
|       1115 | Korea, Republic of                   | 
|       1131 | Malaysia                             | 
|       1132 | Maldives                             | 
|       1133 | Mali                                 | 
|       1137 | Mauritania                           | 
|       1144 | Mongolia                             | 
|       1156 | Niger                                | 
|       1161 | Norway                               | 
|       1166 | Panama                               | 
|       1168 | Paraguay                             | 
|       1173 | Portugal                             | 
|       1187 | Saudi Arabia                         | 
|       1193 | Slovenia                             | 
|       1199 | Sri Lanka                            | 
|       1200 | Sudan                                | 
|       1210 | Tanzania, United Republic of         | 
|       1211 | Thailand                             | 
|       1218 | Tunisia                              | 
|       1219 | Turkey                               | 
|       1223 | Uganda                               | 
|       1224 | Ukraine                              | 
|       1227 | United States Minor Outlying Islands | 
|       1233 | Viet Nam                             | 
|       1239 | Zambia                               | 
+------------+--------------------------------------+
47 rows in set (0.01 sec)

Example with digits for abbreviations (Austria):

Code: [Select]

mysql> select s.name, s.abbreviation from civicrm_state_province s, civicrm_country c where c.id=s.country_id and c.name like 'austria';
+------------------+--------------+
| name             | abbreviation |
+------------------+--------------+
| Burgenland       | 1            | 
| K�rnten          | 2            | 
| Niederosterreich | 3            | 
| Oberosterreich   | 4            | 
| Salzburg         | 5            | 
| Steiermark       | 6            | 
| Tirol            | 7            | 
| Vorarlberg       | 8            | 
| Wien             | 9            | 
+------------------+--------------+
9 rows in set (0.00 sec)

As a temporary fix, I have addresses set to display full province/state names, but there are number of places in Civi that still display the abbreviations (eg contact search results).

I am interested in helping to fix the issue, but am curious as to where the data originally came from and how the SQL to stick it in the Civi database was generated - I'd like to not repeat any work and I really don't want to fix all the messed up entries by hand

kaldari · August 31, 2010, 11:32:03 am

Don't know the answer to any of those questions, but a good source for state abbreviations is statoids.com. They usually list several different sets of abbreviations for the states of each country. I assume the set we want are the postal abbreviations. Here's the data for Austria for example:
http://www.statoids.com/uat.html

They sell spreadsheets of some of their data (http://www.statoids.com/datasale.html), but I'm not sure it includes the postal abbreviations. Perhaps someone could manually compile all the missing data for our 47 countries and then someone else could munge it into an SQL patch. Sounds like a fun project, aye

kaldari · August 31, 2010, 11:36:04 am

I think this is related to ISO 3166-2 (http://en.wikipedia.org/wiki/ISO_3166-2).

kaldari · August 31, 2010, 11:46:00 am

It looks like CiviCRM is using the ISO 3166-2 codes as the state "abbreviations". This is probably not a good idea as these codes are meant to function as globally unique identifiers, not human-readable abbreviations (or postal abbreviations). What should be done is the existing "abbreviation" field should be renamed "iso-code" and a new abbreviation field should be created which contains human-readable abbreviations for use in the CiviCRM interface.

CiviCRM Community Forums (archive)

News:

Author Topic: State/provice abbreviations for 47 countries display as digits (Read 1231 times)

awjrichards

State/provice abbreviations for 47 countries display as digits

kaldari

Re: State/provice abbreviations for 47 countries display as digits

kaldari

Re: State/provice abbreviations for 47 countries display as digits

kaldari

Re: State/provice abbreviations for 47 countries display as digits