CiviCRM
  1. CiviCRM
  2. CRM-5224

UK 'State/province' list contains mixture of counties and large towns

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed/Completed
    • Affects Version/s: 3.0.1
    • Fix Version/s: 3.1
    • Component/s: Core CiviCRM
    • Labels:
      None

      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
      1. state.sql
        4 kB
        Tom Kirkpatrick
      2. state.sql
        4 kB
        Tom Kirkpatrick

        Activity

        Hide
        Tom Kirkpatrick added a comment -
        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.
        Show
        Tom Kirkpatrick added a comment - 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.
        Hide
        Tom Kirkpatrick added a comment -
        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
        Show
        Tom Kirkpatrick added a comment - 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
        Hide
        Marcus Liberman added a comment -
        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.
        Show
        Marcus Liberman added a comment - 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.
        Hide
        Michael McAndrew added a comment -
        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.
        Show
        Michael McAndrew added a comment - 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.
        Hide
        Tom Kirkpatrick added a comment -
        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.
        Show
        Tom Kirkpatrick added a comment - 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.
        Hide
        Donald A. Lobo added a comment -

        eliminated city of london before the commit :)

        also fixed civicrm_state_province.tpl with the new setup. tom, can u please check
        Show
        Donald A. Lobo added a comment - eliminated city of london before the commit :) also fixed civicrm_state_province.tpl with the new setup. tom, can u please check
        Hide
        Tom Kirkpatrick added a comment -
        Yep, looks good. Thanks Donald.
        Show
        Tom Kirkpatrick added a comment - Yep, looks good. Thanks Donald.
        Hide
        Yashodha Chaku added a comment -
        assigning for 3.1 verification
        Show
        Yashodha Chaku added a comment - assigning for 3.1 verification
        Hide
        Sushant Paste added a comment -
        verified in r25172.
        Show
        Sushant Paste added a comment - verified in r25172.
        Hide
        Dave Jenkins added a comment -
        Lincolnshire is missing from civicrm_data*.mysql and civicrm_generated.mysql .
        Show
        Dave Jenkins added a comment - Lincolnshire is missing from civicrm_data*.mysql and civicrm_generated.mysql .
        Hide
        Piotr Szotkowski added a comment -
        Reopening to add Lincolnshire.
        Show
        Piotr Szotkowski added a comment - Reopening to add Lincolnshire.
        Hide
        Piotr Szotkowski added a comment -
        (Re-)fixed in r28145.
        Show
        Piotr Szotkowski added a comment - (Re-)fixed in r28145.
        Hide
        Donald A. Lobo added a comment -

        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)
        Show
        Donald A. Lobo added a comment - 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)
        Hide
        Piotr Szotkowski added a comment -
        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.
        Show
        Piotr Szotkowski added a comment - 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.

          People

          • Assignee:
            Piotr Szotkowski
            Reporter:
            Tom Kirkpatrick
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development