[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

Status: Closed
Project: CiviCRM
Component/s: Core CiviCRM
Affects Version/s: 3.0.1
Fix Version/s: 3.1

Type: Bug Priority: Minor
Reporter: Tom Kirkpatrick Assignee: Piotr Szotkowski
Resolution: Fixed/Completed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File state.sql     File state.sql    

 Description   
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:
Birmingham
Bristol
Poole

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

 Comments   
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)
and http://en.wikipedia.org/wiki/Subdivisions_of_Scotland#Council_areas

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 =
Bedfordshire
Berkshire
Bristol, City of
Buckinghamshire
Cambridgeshire
Cheshire
Cornwall
Co Durham
Cumbria
Derbyshire
Devon
Dorset
East Riding of Yorkshire
East Sussex
Essex
Gloucestershire
Greater London
Greater Manchester
Hampshire
Herefordshire
Hertfordshire
Isle of Wight
Kent
Lancashire
Leicestershire
Lincolnshire
London, City of
Merseyside
Norfolk
North Yorkshire
Northamptonshire
Northumberland
Nottinghamshire
Oxfordshire
Rutland
Shropshire
Somerset
South Yorkshire
Staffordshire
Suffolk
Surrey
Tyne and Wear
Warwickshire
West Midlands
West Sussex
West Yorkshire
Wiltshire
Worcestershire

= WALES =
Clwyd
Dyfed
Gwent
Gwynedd
Mid Glamorgan
Powys
South Glamorgan
West Glamorgan

= NORTHERN IRELAND =
Co Fermanagh
Co Tyrone
Co Londonderry
Co Antrim
Co Down
Co Armagh

= SCOTLAND =
Aberdeen City
Aberdeenshire
Angus
Argyll and Bute
Clackmannanshire
Dumfries and Galloway
Dundee City
East Ayrshire
East Dunbartonshire
East Lothian
East Renfrewshire
City of Edinburgh
Falkirk
Fife
Glasgow City
Highland
Inverclyde
Midlothian
Moray
North Ayrshire
North Lanarkshire
Perth and Kinross
Renfrewshire
Scottish Borders
South Ayrshire
South Lanarkshire
Stirling
West Dunbartonshire
West Lothian
Na h-Eileanan Siar
Orkney Islands
Shetland Islands
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 ]
Hello,

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

(2712,'Lincolnshire','LIN',1226)
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.
Generated at Thu Apr 17 18:28:47 UTC 2014 using JIRA 6.2#6252-sha1:aa343257d4ce030d9cb8c531be520be9fac1c996.