[CRM-5224] UK 'State/province' list contains mixture of counties and large towns Created: 13/Oct/09 Updated: 10/Jun/10 Resolved: 10/Jun/10
|Reporter:||Tom Kirkpatrick||Assignee:||Piotr Szotkowski|
|Remaining Estimate:||Not Specified|
|Time Spent:||Not Specified|
|Original Estimate:||Not Specified|
The UK 'State/province' list currently contains mixture of counties and large towns. It should contain the UK counties only. There is a separate, free-text field to capture the town/city (billing_city)
For example, all of the following are Cities and should not be included:
The list of uk counties listed at http://blog.acmultimedia.co.uk/2007/09/list-of-uk-counties-in-sql-csv-format/ seems to be accurate, but also needs to include the 6 Northern Ireland counties as listed at http://en.wikipedia.org/wiki/Counties_of_Northern_Ireland
|Comment by Tom Kirkpatrick [ 13/Oct/09 ]|
|Actually, the list at http://en.wikipedia.org/wiki/Postal_counties_of_the_United_Kingdom is probably the best one to use. The stuff listed under "Postal counties in operation from 1974 to 1996" - These are the ones that people know, recognise and use.|
|Comment by Tom Kirkpatrick [ 25/Oct/09 ]|
The attached SQL script updates the UK state/province list to the list detailed below - this list was discussed in some detail on the UK mailing list. IT does so by
1) NULLing references to counties that are to be removed in the civicrm_address table.
2) Deleting entries from the civicrm_state_province table that should not be there.
3) Altering the names of several counties to be more consistent.
4) Creating 3 additional entries for counties that were missing from the original data.
The resulting state/province list contains 97 counties (the original list had 248, many of which were not really counties in the traditional sense). The list is comprised of the following:
The list at http://en.wikipedia.org/wiki/Ceremonial_counties_of_England#Definition
together with http://en.wikipedia.org/wiki/Ceremonial_counties_of_Wales
http://en.wikipedia.org/wiki/Counties_of_Ireland (the Northern Ireland bit only)
In my opinion, this list is far more recognisable as a list of UK counties, and is far more usable. Hopefully this can be committed into the upcoming 3.1 release.
= ENGLAND =
Bristol, City of
East Riding of Yorkshire
Isle of Wight
London, City of
Tyne and Wear
= WALES =
= NORTHERN IRELAND =
= SCOTLAND =
Argyll and Bute
Dumfries and Galloway
City of Edinburgh
Perth and Kinross
Na h-Eileanan Siar
|Comment by Marcus Liberman [ 26/Oct/09 ]|
We at Alpha International would really welcome this improvement to the list to be implemented in 3.1. Whilst there are still some debates to be had about the list (I had no idea how complicated this could be), we would only make the following suggestion (mentioned in email conversations):
- Remove the CIty of London entry - this should just be covered by Greater London.
Even if this is not entirely correct, it is a massive improvement over the current list. We look forward to seeing it in 3.1 Thanks to Tom for getting it done.
|Comment by Michael McAndrew [ 26/Oct/09 ]|
One comment (though if time permitted I'd make more).
This list definitley looks better but City and London and Greater London shouldn't appear on the same list. City of London is included in Greater London - it's the equilivant of including United States and Texas as choices - or England and Yorkshire. So you should either remove City of London, or include this list for all the subdivisions of London. http://en.wikipedia.org/wiki/Greater_London
I can see the argument both ways and as I don't use this will refrain from stating a preference.
I gave up using this list because of all the controversy. I just use street address, address line 1, 2 and city which gives 4 lines of address that people can self define. I then use postcodes for geo stuff.
|Comment by Tom Kirkpatrick [ 26/Oct/09 ]|
|I know this just got committed, but here is an updated version that also removes City of London, leaving Greater London only. Any chance we can uncommit, and re-commit this version? If not, I'd be happy to write a standalone patch to remove the City of London.|
|Comment by Donald A. Lobo [ 27/Oct/09 ]|
eliminated city of london before the commit :)
also fixed civicrm_state_province.tpl with the new setup. tom, can u please check
|Comment by Tom Kirkpatrick [ 27/Oct/09 ]|
|Yep, looks good. Thanks Donald.|
|Comment by Yashodha Chaku [ 09/Nov/09 ]|
|assigning for 3.1 verification|
|Comment by Sushant Paste [ 18/Nov/09 ]|
verified in r25172.
|Comment by Dave Jenkins [ 04/Jun/10 ]|
|Lincolnshire is missing from civicrm_data*.mysql and civicrm_generated.mysql .|
|Comment by Piotr Szotkowski [ 04/Jun/10 ]|
|Reopening to add Lincolnshire.|
|Comment by Piotr Szotkowski [ 07/Jun/10 ]|
|(Re-)fixed in r28145.|
|Comment by Donald A. Lobo [ 09/Jun/10 ]|
i see lincolnshire already there in a 3.1 db and hence my upgrade to 3.2 fails. here are the values
|Comment by Piotr Szotkowski [ 10/Jun/10 ]|
|Argh, it turns out it *wasn't* there in 3.1 and 3.2, but it *was* there in 3.0 (and earlier releases). I re-fixed that in r28202, bringing it back with the old civicrm_state_province.id (2712) and doing an INSERT IGNORE on upgrades.|