Improve GB (UK) province list

[rfay]

From user Jan Bates:

I have just signed up for an account and have a comment and a suggestion about the sign-up page.

COMMENT: The State/Province? list that you have for the United Kingdom, is in fact a list of cities and towns. You should instead have a list of counties, e.g. Yorkshire, Warwickshire, Northumberland, etc.

From http://dev.randyfay.com:8000/warmshowers/ticket/63

[rfay]

The description of the user impact in #166 is excellent, and makes the case that we should actually do this.

[rfay]

The wikipedia article about counties of England (not all of the UK) is at http://en.wikipedia.org/wiki/Metropolitan_and_non-metropolitan_counties_of_England. ISO-3166-1, which should list those, is at http://en.wikipedia.org/wiki/Table_of_administrative_divisions_by_country#Administrative_divisions_with_ISO_3166-1

The standard listing second-level administrative divisions is ISO-3166-2, and Wikipedia does have those.

http://en.wikipedia.org/wiki/ISO_3166-2:GB

Sadly, I'm still having trouble sorting out how this all applies to Warmshowers, as the list on that page is essentially the same as what we have currently.

Another member provides this list, http://www.gbet.com/AtoZ_counties/, which does not include county codes (from my own email

A very simple technique would be to just go with the geonames ADM1 listings, which are already right in our database.

Geonames does have quite a list: http://www.geonames.org/GB/administrative-division-united-kingdom.html

However, it remains too much. `

country_code adm1_code name GB 01 Estuaire Province GB 08 Ogooué-Maritime Province GB 11 (UK11) GB 12 (UK12) GB 13 (UK13) GB 17 Greater London GB 19 (UK19) GB 29 (UK29) GB 32 (UK32) GB 36 (UK36) GB 52 (UK52) GB 64 (UK64) GB 80 (UK80) GB 81 (UK81) GB 82 Grampian GB 83 (UK83) GB 84 Lothian GB 85 (UK85) GB 86 (UK86) GB 87 Strathclyde GB 88 Tayside GB 89 (UK89) GB 90 Clwyd GB 91 Dyfed GB 92 Gwent GB 93 (UK93) GB 96 South Glamorgan GB A1 London Borough of Barking and Dagenham GB A2 London Borough of Barnet GB A3 Barnsley GB A4 Bath and North East Somerset GB A5 Bedfordshire GB A6 Bexley GB A7 Birmingham GB A8 Blackburn with Darwen GB A9 Blackpool GB B1 Bolton GB B2 Bournemouth GB B3 Bracknell Forest GB B4 Bradford GB B5 Brent GB B6 Brighton and Hove GB B7 City of Bristol GB B8 Bromley GB B9 Buckinghamshire GB C1 Bury GB C2 Calderdale GB C3 Cambridgeshire GB C4 London Borough of Camden GB C5 Cheshire GB C6 Cornwall GB C7 Coventry GB C8 London Borough of Croydon GB C9 Cumbria GB D1 Darlington GB D2 Derby GB D3 Derbyshire GB D4 Devon GB D5 Doncaster GB D6 Dorset GB D7 Dudley GB D8 Durham GB D9 Ealing GB E1 East Riding of Yorkshire GB E2 East Sussex GB E3 London Borough of Enfield GB E4 Essex GB E5 Gateshead GB E6 Gloucestershire GB E7 London Borough of Greenwich GB E8 London Borough of Hackney GB E9 Halton GB F1 London Borough of Hammersmith and Fulham GB F2 Hampshire GB F3 Haringey GB F4 Harrow GB F5 Hartlepool GB F6 London Borough of Havering GB F7 Herefordshire GB F8 Hertfordshire GB F9 Hillingdon GB G1 Hounslow GB G2 Isle of Wight GB G3 London Borough of Islington GB G4 Kensington and Chelsea GB G5 Kent GB G6 City of Kingston upon Hull GB G7 Kingston upon Thames GB G8 Kirklees GB G9 Knowsley GB H1 London Borough of Lambeth GB H2 Lancashire GB H3 Leeds GB H4 Leicester GB H5 Leicestershire GB H6 London Borough of Lewisham GB H7 Lincolnshire GB H8 Liverpool GB H9 City of London GB I1 Luton GB I2 Manchester GB I3 Medway GB I4 London Borough of Merton GB I5 Middlesbrough GB I6 Milton Keynes GB I7 Newcastle upon Tyne GB I8 Newham GB I9 Norfolk GB IO Isles of Scilly GB J1 Northamptonshire GB J2 North East Lincolnshire GB J3 North Lincolnshire GB J4 North Somerset GB J5 North Tyneside GB J6 Northumberland GB J7 North Yorkshire GB J8 Nottingham GB J9 Nottinghamshire GB K1 Oldham GB K2 Oxfordshire GB K3 Peterborough GB K4 Plymouth GB K5 Poole GB K6 Portsmouth GB K7 Reading GB K8 London Borough of Redbridge GB K9 Redcar and Cleveland GB L1 London Borough of Richmond upon Thames GB L2 Rochdale GB L3 Rotherham GB L4 Rutland GB L5 Salford GB L6 Shropshire GB L7 Sandwell GB L8 Sefton GB L9 Sheffield GB M1 Slough GB M2 Solihull GB M3 Somerset GB M4 Southampton GB M5 Southend-on-Sea GB M6 South Gloucestershire GB M7 South Tyneside GB M8 Southwark GB M9 Staffordshire GB N1 Staffordshire GB N2 Stockport GB N3 Stockton-on-Tees GB N4 Stoke-on-Trent GB N5 Suffolk GB N6 Sunderland GB N7 Surrey GB N8 Sutton GB N9 Swindon GB O1 Tameside GB O2 Telford and Wrekin GB O3 Thurrock GB O4 Torbay GB O5 Tower Hamlets GB O6 Trafford GB O7 Wakefield GB O8 Walsall GB O9 London Borough of Waltham Forest GB P1 Wandsworth GB P2 Warrington GB P3 Warwickshire GB P4 West Berkshire GB P5 Westminster GB P6 West Sussex GB P7 Wigan GB P8 Wiltshire GB P9 Windsor and Maidenhead GB Q1 Wirral GB Q2 Wokingham GB Q3 Wolverhampton GB Q4 Worcestershire GB Q5 York City GB Q6 Antrim GB Q7 Ards GB Q8 Armagh GB Q9 Ballymena GB R1 Ballymoney GB R2 Banbridge GB R3 Belfast GB R4 Carrickfergus GB R5 Castlereagh GB R6 Coleraine GB R7 Cookstown GB R8 Craigavon GB R9 Down GB S1 Dungannon GB S2 Fermanagh GB S3 Larne GB S4 Limavady GB S5 Lisburn GB S6 Londonderry GB S7 Magherafelt GB S8 Moyle GB S9 Newry and Mourne GB T1 Newtownabbey GB T2 North Down GB T3 Omagh GB T4 Strabane GB T5 Aberdeen City GB T6 Aberdeenshire GB T7 Angus GB T8 Argyll and Bute GB T9 The Scottish Borders GB U1 Clackmannanshire GB U2 Dumfries and Galloway GB U3 Dundee City GB U4 East Ayrshire GB U5 East Dunbartonshire GB U6 East Lothian GB U7 East Renfrewshire GB U8 City of Edinburgh GB U9 Falkirk GB V1 Fife GB V2 Glasgow City GB V3 Highland GB V4 Inverclyde GB V5 Midlothian GB V6 Moray GB V7 North Ayrshire GB V8 North Lanarkshire GB V9 Orkney GB W1 Perth and Kinross GB W2 Renfrewshire GB W3 Shetland GB W4 South Ayrshire GB W5 South Lanarkshire GB W6 Stirling GB W7 West Dunbartonshire GB W8 Western Isles GB W9 West Lothian GB X1 Isle of Anglesey GB X2 County Borough of Blaenau Gwent GB X3 County Borough of Bridgend GB X4 Caerphilly GB X5 City and County of Cardiff GB X6 Cardiganshire GB X7 Carmarthenshire GB X8 County Borough of Conwy GB X9 Denbighshire GB Y1 Flintshire GB Y2 Gwynedd GB Y3 Merthyr Tydfil GB Y4 County of Monmouthshire GB Y5 County Borough of Neath Port Talbot GB Y6 County Borough of Newport GB Y7 County of Pembrokeshire GB Y8 Powys GB Y9 County Borough of Rhondda Cynon Taff GB Z1 Swansea GB Z2 Torfaen GB Z3 Vale of Glamorgan GB Z4 Wrexham `

[rfay]

Another version of this conversation is in my email at https://mail.google.com/mail/u/0/#search/counties+uk/120590ae7b978657 - unfortunately putting correct stuff in there would not have solved the problem for her.

[rfay]

This is now mentioned in the Volunteer page, http://www.warmshowers.org/volunteer.

[rfay]

A member is considering volunteering to fix this.

Hi Nick - Thanks for considering working on the Warmshowers UK county problem. Maybe we'll finally get it solved.

It seems like we need to do these things:

1. Get a correct list of counties to use as provinces, with their correct codes. Hopefully from geonames.org. There are resources above; and geonames, and
2. Install that list on Warmshowers
3. Update existing members to the correct county (without changing their map location, hopefully)
4. Ask members to check and update their info.

If we can get a correct mapping of the existing "provinces" to the correct counties, then we can update all members with a query or two.

[nickpaton]

Hi Randy

Touching base with you, and I'll start looking at this over the next few days and keep in touch etc.

Kind regards Nick

[rfay]

Thanks Nick! Finally some progress on this!

[rfay]

BTW, I can get you a sanitized mysql database dump and can explain where this stuff is. Do you have a way to use it?

[nickpaton]

Immediate thoughts are that in UK we don't use Geoname / ISO county identification, and it seems unnecessarily complicated.

Possibly there's been too much looking into this, and all it needs is for a person from the UK to come along and simply list the counties for new members to select from, and to add counties to existing member details. I don't see much more being needed in all honesty.

For example a member is from Carlisle; we need to add "Cumbria" as the county, but not Geonames / ISO "GB C9" which is meaningless to most people and which until now I'd never heard of!

Re an sql database, I don't have anything currently set up, but may be able to on a PC here. Do you want to send it to me and I'll see if I can read / write to it, but if not maybe an Excel/ CSV or similar version?

If it's easier for you to send stuff feel free to use my email nickpaton@fireflyuk.net.

[rfay]

Manually doing the 1800+ members in the UK would be pretty painful. Mysql will run on any kind of machine. If you've ever done sql you won't find it very hard to work with.

[nickpaton]

Hi Randy

I was in a bit of a hurry yesterday, so now got some time.

First no problems with using mysql so feel free to send the database in that format. I use CPanel on my website for my existing databases (via php MYadmin) and it's fine with importing new ones, then carrying out changes / additions etc manipulating and then exporting again, and won't of course appear on my site (www.reflowrepairs.co.uk) but is a good repository for various other work related databases etc. Failing that I'll use something like Navicat Lite etc. Format for sending please, either .sql, or compressed .sql.zip only (CPanel doesn't accept other compression formats by default).

OK onto the interesting stuff! I believe in USA you have a lot of "land" divisions, sub-divisions etc (state, provinces, counties, townships etc). We don't have these, and the format for a typical UK address starting at an individual house on a street and expanding up to the country we live in, would be as follows (" " for clarity):

1. "Windy Ridge" (Optional - the name someone's given their house)
2. "1 The High Street" (Required - the number of the house and the street it's in)
3. "Gosforth" (Optional but good to have - the area of the town that person is living in)
4. "Newcastle upon Tyne" (Required - the town they're living in)
5. "NE1 2AB" (Required - is called the "Post Code" and on maps, GPS, Google maps etc etc) will show the immediate area location of the house. I believe it's similar to USA Zip Code).
6. "Tyne and Wear" - (Required - this is what I believe you want me to add to every UK member address. Called the County and identifies which part of United Kingdom the address is in - more below).
7. "England" (Optional - this is the country within United Kingdom the address is in).
8. "United KIngdom" or "UK" for short (Required - which country in the world that address is in.)

What to base the county areas on. I suggest the UK Government Office for National Statistics document http://www.ons.gov.uk/ons/guide-method/geography/beginner-s-guide/administrative/england/counties--non-metropolitan-districts-and-unitary-authorities/map-of-the-uk-counties-and-unitary-administrations.pdf

It's a little complicated to understand as it also includes Council Boundaries and Unitary Authorities (they are political boundaries and not really relevant), but I can sort that out OK, but hopefully you get the idea.

As you can see there are no provinces, geonames etc, and this is the format everyone in UK uses and recognises.

Let me know what you think.

Also pass my wishes and condolences to anyone in Boston you may know who has been affected by the recent bombings.

[rfay]

Thanks for looking at this!

The description of what tables are used for what is in the wiki: https://github.com/rfay/Warmshowers.org/wiki/Adding-a-new-country

We won't be able to change the actual fields used, so we're stuck with "province", which probably means county.

I'm going to share a sanitized database with you via dropbox, but (just to make sure you know) this is for this project only and for you only. It's full of very sensitive user information and must not be placed anywhere on the internet, including your cpanel-hosted server. You can install mysql on any local machine to experiment with it, or you can install something like XAMPP or WAMP or MAMP to get a full web stack.

You may get ambitious and want to set up a local (your local machine) copy of Warmshowers.org, using the instructions at https://github.com/rfay/Warmshowers.org/wiki/Creating-A-Local-Copy-of-Warmshower.org.

[nickpaton]

Thanks for the clarification as to fields and names etc - understood.

Also to state I understand the confidentiality / sensitivity issues with the data; I forgot that of course the CPanel page on my website is saving to my host's servers and that won't happen.

I'll get a local machine sorted out to run mysql or set up a LAMP server or equiv.

Also just downloaded the database from ws-private and it will be kept securely on one of my machines here.

Thanks too for the contact info. Suggest I get things set up and we can then take things from there.

[nickpaton]

Hi Randy and hope you're keeping well.

I've not been in touch with you regarding adding UK counties. I've also misplaced your private email address for which I apologise.

I'm having some issues with setting up SQL here as the various methods I've tried have not succeeded. I'm trying to get use of a newer more powerful one which should work, so to say I've not forgotten about this and am keen to help out.

Also I'm often away cycling which is taking up a lot of time, as I'm building up to a big ride in a couple of month's time.

Hope you can bear with me, and again, apologise for not keeping in touch.

With kind regards Nick

(This message also sent via WS "Fixing UK Counties")

[rfay]

Hi Nick - Thanks for your future work on this. This issue queue is a great way to contact me; I see you did the same thing on the site. That's great too. We'll be happy to help you get a site set up when you get there.

-Randy

[rfay]

Some very interesting resources: Great info on UK provinces and Districts of the United Kingdom (loads of info on GB provinces) and Mapping the terms used for first-order administrative divisions

[rfay]

@nickpaton Did you get any traction on this? If so (or if not :-) this would be a good time to refresh or to brief me on what you learned or resources. I'm thinking of rebuilding the geo databases we use and "fixing" the UK and Denmark provinces while I'm at it...

[rfay]

@nickpaton @all: If nobody objects I'm going to end up using this list for the counties of England: http://prado.ws.geonames.org/children?geonameId=6269131 - That shows very nicely in Chrome, probably in Firefox. Would love to have confirmation that this is reasonable.

[nickpaton]

Hi Randy, and apologies for not getting back to you earlier. I've been diverted by other things over the summer (including cycling!).

The main problem I've had is installing mysql and running the database you sent me, as none of my PC's / laptops here seem powerful enough to run the database for some reason - I've got old equipment! I'm not quite sure what to do about this and am somewhat stuck over this. This was as far as I got way back when I first offered to add the counties and haven't had a chance to try again.

I've still got issues with this geonames list thing. I've looked at the suggested list and not only does it show counties but goes further down into boroughs. Boroughs are areas, mainly around cities and large towns and is far too detailed, and is going to be next to impossible to categorise against existing addresses. Also anyone completing their address details in future are very unlikely to have a clue what borough they're in - I don't and I've lived here for nearly 15 years!

I simply don't understand why you don't use counties which everyone in UK uses and understands. No one in UK has ever heard of geonames and putting down geoname numbers (for instance) after cites or towns etc will be meaningless.

Or am I misunderstanding you? Are you using the geonames lists simply as a source of county names? If so then fine and I apologise for the misunderstanding.

I'll see if I can find a suitable PC to run all this too but not holding out much hope as all mine are ancient!

Kind regards Nick

[rfay]

@nickpaton I think these are more or less the counties: http://prado.ws.geonames.org/children?geonameId=6269131

I'm trying to use a standard list from a reputable source. The best other I can find is the Wikipedia list of counties, which is mostly the same but leaves out a few.

You don't need a mysql install for any of this. Just follow the link and take a look around. And I think this is overall must simpler than I originally envisioned: We'll just change the codes and then send an email to every member in UK asking them to update.

-Randy

[rfay]

@nickpaton following up on a couple things you said: Geonames is only a data source, a worldwide list of placenames and their locations and other information. We use those for most things we do. The geonameid is never exposed to users, and all we're trying to do is to see if there's a reasonable source for counties in the geonames data.

Since the web service (which I linked to) uses the exact same information as the database, there's no need for you to install anything.

[nickpaton]

@rfay First of all apologies re geonames, yes you're right as I misunderstood your intention for using data from them. Also I did realise I didn't have to install anything to simply look at the list!

What does concern me is the "accuracy" of the list as far as this project is concerned.
For instance Geonames doesn't list UK metropolitan counties, eg "Tyne and Wear" where Newcastle upon Tyne (the city I live in) resides, and is a recognisable county name by anyone living UK.
According to Geonames list, Newcastle upon Tyne resides in "City and Borough of Newcastle upon Tyne". By definition Metropolitan Boroughs (to give their full name) are local government districts (see https://en.wikipedia.org/wiki/Metropolitan_borough), and relate to local political / voting boundaries rather than UK geographical areas.

The wikipedia link you gave (http://en.wikipedia.org/wiki/List_of_English_counties) is more accurate (though will need checking to ensure it's up to date etc - after all it is wikipedia!) and I'd suggest using this over geonames list. Also it's a lot easier to read too! Geonames data can be used too to confirm wikipedia information of course but bearing in mind it's not complete WRT this project.

Hope that helps. I just need to solve the basic mysl install issue now!

[nickpaton]

@rfay I've finally found the UK County source information as supplied by the UK Government Office for National Statistics (ONS): http://www.ons.gov.uk/ons/guide-method/geography/beginner-s-guide/administrative/england/counties/index.html . With the source material being government-based, it's going to be the most accurate, current etc. Wikipedia information, for instance, will, or should, be taken from this ONS information.

Ref the Downloads links: "Map of UK Counties and Unitary Administrations" is the one I suggest we should be using. "Map of all UK Authorities" appears similar to the Geoname Data information. Compared to the UK Counties map you can see how it subdivides the counties into smaller areas. As I've said earlier these subdivisions (metropolitan boroughs) tend not to be recognised when defining which counties particular places reside in.

What is a little strange is doesn't appear to be an actual list of counties on the site, but the Wiki link seems to provide this.

I suggest using the Wiki list info, together with the ONS map as an "overlay" for quick county identification for the vast majority of WS host place names.

Apologies for making a bit of a meal of this but there's no point in creating something that won't work in the long term.

[rfay]

So annoying :-) Looked at the official government lists following your links, and got to https://geoportal.statistics.gov.uk/Docs/Names%20and%20Codes/Counties_(Eng)_2012_Names_and_Codes.zip - but the enclosed item includes a list of only 28 counties :-) Doesn't include Tyne and Wear.

I may just have to manually enter the items we're after. I sure wish there was better stuff out there.

[rfay]

Examining the lists from the Wikipedia list (87 counties for England) side by side with the geonames list, IMO there's no reason to go with customizing any lists. I'm going to go ahead with using the geonames list and see if people scream. I really appreciate your help on this @nickpaton and will be interested in seeing what you think when we get it out there. I don't think I can make it any worse, right? Thanks!

[nickpaton]

OK, no problem and let me know if I can be of assistance in the future.

[rfay]

Hopefully fixed in https://github.com/rfay/Warmshowers.org/commit/eb9aa626cc5f1368cbae9c2a55ed2c1ff8f24e9a

[rfay]

Nah, failed I think. At least these major problems at this point:

• A lot of people still have problems with the list contents
• The way I presented it as ENG:name is backwards and not useful. Could just rework those and name,England
• Geocoding is failing for lots of people.

[nickpaton]

Not surprised to be honest Randy. I completed my "county" update and "Tyne and Wear" my actual county wasn't listed and the best I could come up with was something like "Borough of Newcastle upon Tyne" which as I've said before is a local council/voting/policitical boundary.

Bottom line is that you need to use the simplest method possible and I wonder if you're trying to make it more complex than it actually is - after all it's only a list of UK counties! I also appreciate that you want everything to be 110% correct and verifiable, as well as easy to add into your databases, and FWIW, I've uploaded a spreadsheet of what we in the UK recognise as UK counties: https://www.dropbox.com/s/mv7zhife5g8g1rv/list%20UK%20Counties.xls

Information from wikipedia and the ONS map (link in previous message).

Note that there are four UK countries all with their own counties: England ("en" on spreadsheet), Scotland (sco), Wales (wal), Northern Ireland (nir) (three letter labels are made up by myself). I'd suggest keeping them separate so nationals in those countries can easily find their county.

Source References: England: https://en.wikipedia.org/wiki/List_of_English_counties Scotland: https://en.wikipedia.org/wiki/List_of_Scottish_counties_by_highest_point Wales: https://en.wikipedia.org/wiki/List_of_Welsh_principal_areas_by_highest_point Northern Ireland: https://en.wikipedia.org/wiki/Counties_of_Northern_Ireland

Note: Ref ONS map, wikipedia, and Northern Ireland different details. ONS map shows District Council areas, whereas wiki shows county borders that are what you need.

Hope that helps.

I really appreciate the hard work you're putting into this and will do all I can to help.

[rfay]

The list contents are still a problem, and the presentation is a problem. Geocoding is solved and working now.

[nickpaton]

OK, that's great, I'm pleased!

But from the tiny amount I've found out about Geocoding, doesn't it work on the post code / street address only, and doesn't need the "province" information (especially as "province covers a large area). Certainly my exact location is correctly marked on the map by me manually placing it in exactly the right place on the correct side of the street etc. I'm sure the vast majority of UK WS hosts can do the same.

Doesn't this make Geonames provinces Geocoding redundant, and maybe unnecessarily far too complicated? It's "only" a site for touring cyclists to find a bed for the night after all, and part of the comms between hosts and hostees will be address and location details.

Finally, I've still got the same problem in that the "province" list provided doesn't list the county I live in. What am I supposed to do? I've had to leave it "Not Listed".

[rfay]

@nickpaton seems to me like "City and Borough of Newcastle upon Tyne" is what it was asking for. I set it to that. Let me know if it's wrong, but this probably isn't the place for discussion about your account. Thanks, -Randy

[rfay]

Found out where the geonames data comes from, http://geonames.wordpress.com/2007/04/11/ordnance-survey-administrative-divisons/

Since the large set comes from the most authoritative UK agency, I'm confused again.

[rfay]

Member provided link to http://homepage.ntlworld.com/geogdata/pcs/home.htm which has GB address formats, etc.

[rfay]

I've opened a request on the geonames forum to have permission to update the names of the various admin2 subdivisions to remove the "county of", "city of", "borough of". This should be able to happen. Then we just have to bring the info back through.

This doesn't deal with the problem of the list being longer than many think is appropriate.

The next time around, the name of the subdivisions should be changed from ENG:City of Birminghame to "Birmingham, England"

[rfay]

I did many of the admin2 entries on geonames, but a few were locked. Requested permission to work on the others. The forum is http://forum.geonames.org/gforum/posts/list/3099.page

I think it would work to change both the friendly name as above and change the code to the raw admin2 code (assuming that is unique between England/Scotland/Wales/NI). However, doing that requires updating user_location.