UK Cities

[drakodev]

UK Cities are part of England State as per stateId

[dr5hn]

@drakodev can you please elaborate more about this issue ?

[drakodev]

All the cities of United kingdom are under one state i.e England with state_code: "ENG" and id: 2336

For instance Milton Keynes should have been under Buckinghamshire state but it is under England state.

All 2919 cities are under one state England

[dr5hn]

@drakodev Thanks for the brief. Will look into it ASAP.

[dr5hn]

@drakodev Actually there's some issue with Data.

• Buckinghamshire shouldn't be in the States table. As Buckinghamshire comes under State "England".
• So I need to move Buckinghamshire from states to cities table.
• Also need to move out "Milton Keynes" from the data itself.

[mr-vijaychauhan]

I have question related to this question about United Kingdom(UK). Here I facing a problem For Example United Kindom-> England -> London -> City of London or United Kingdom-> London -> City of London which is correct I don't know. but here problem when I selected United Kingdom then in States data showing England and also City of London inside states data. No London Option Here and Showing all cities in States Data. there is some issue but I don't understand what happened here

[nigelhorne]

@drakodev the first is the correct one.

[dr5hn]

@nigelhorne Which one?

[alexandru-bagu]

The current database format cannot accommodate the regions in UK. Should there maybe be another table called regions? Most countries would not have regions but some do, like UK or Italy.
Would it be possible to add another table then for regions which would handle the case we have for UK? Say for UK we would have 4 regions, each region would have its own states and each state would have its own city list.
The link would be a nullable column called region_id in the states table (if the country has no regions then it would be null).

Alternatively we could follow Google Maps' lead and not use regions at all and just map the cities to their own states.
@dr5hn Is the data flawed or is there some manipulation done in the code to map the cities to the region instead of the state?

[SHSP-MichaelCrawford]

I have a similar issue that is brought up in this thread. For the UK, you currently have a mix of Non-Metropolitan Counties, Metropolitan Districts, and the Boroughs of London. Our customers are reaching out because they expect to be able to use Non-Metropolitan Counties, Metropolitan Counties, and Greater London. Would it be possible to add in the options Merseyside, Greater Manshester, South Yorkshire, Tyne and Wear, West Midlands, and West Yorkshire (Metropolitan Counties) and Greater London.

These articles helped my understanding:
https://en.wikipedia.org/wiki/Metropolitan_and_non-metropolitan_counties_of_England
https://en.wikipedia.org/wiki/Regions_of_England

Otherwise, this is a great library and has been super helpful!

[anonbergine]

I was expecting the state_code would be the second part of an ISO 3166-2 code (with country_code being the first part), but as mentioned above all the state_code are set to ENG which isn't valid ISO 3166-2 division code for the GB "country".

EDIT: I should have said "which isn't ideal ISO 3166-2 divisioj code" - namely I want the smallest ("most local") division as that's generally the data I'm looking for. A separate table could be used to create hierarchy of divisions or alternate division names/ids (eg. in Wales there are often two ISO codes for each division - one with English name and one with Welsh name).

A list of GB divisions can be found here: https://en.wikipedia.org/wiki/ISO_3166-2:GB

Note on that wiki page it also lists "parent subdivision" which could give some clues to resolving #290

So maybe for places that have a "parent subdivision" there could be a parent_code field or something like that?

This way if I wanted to find places in England, I'd search:

country_code === "GB" && parent_code === "ENG"

But the key thing, in relation to OP, is that state_code should be the suffix of the ISO 3166-2 code for the place where the town/city is located, as combined with the country_code (GB in this case) it's possible to determine the full ISO 3166-2 code.

[anonbergine]

The data in this gist might be of use: https://gist.github.com/duhaime/1d6d5a8dc77c86128fcc1a05a72726c9

It has a couple of json files which map city to county, and once you have the county it should be possible to work out the ISO 3166-2 division (eg. via data on wikipedia article above).

There are of course some additional data sets which might be of use, namely: https://github.com/kevinroberts/city-timezones - each city in the json includes 2 letter country code, and a province (which could be mapped to an ISO 3166-2 code I think).

I've got some datasets downloaded so will try this evening to see I can generate required data for UK cities.

[anonbergine]

@dr5hn , I was able to add valid codes for some of the GB cities.

Process used:

1. Obtain several .json datasets listing UK towns/cities with lat/long and region/county data.
2. Obtain a .json containing ISO 3166-2:GB codes (division code -> division name)
3. Filter your cities.json to GB cities and store in cities-gb.json.
4. Read and iterate that file, and match to city data from the other sources via city name + lat/long (very high certainty city match)
5. Get the region from the matched cities, and see if that region matches a value in the ISO 3166-2:GB table
6. If so, add region_name and region_code properties to the relevant city in cities-gb.json

An updated city record looks something like this:

  {
    "id": 48219,
    "name": "Alfreton",
    "state_id": 2336,
    "state_code": "ENG",
    "state_name": "England",
    "country_id": 232,
    "country_code": "GB",
    "country_name": "United Kingdom",
    "latitude": "53.09766000",
    "longitude": "-1.38376000",
    "wikiDataId": "Q2462661",
    "region_name": "Derbyshire",
    "region_code": "DBY"
  },

As you can see, the two new fields are appended to the end of the original record. This approach means we can still check by country (eg. ENG) within the GB metaset.

Some stats:

• 3,871 GB cities in your dataset
• 1,748 of those could be matched to cities in other datasets
• 1,703 could then be attributed to a valid ISO 3166-2 division (the region_code)
• 2,168 GB cities are still without the region_code

I could have relaxed some of the matching algos to get more, but didn't want to introduce bad data.

The zip attached below is the cities-gb.json containing the 3,871 GB cities, of which 1,703 have the newly added region_name and region_code fields. Hopefully this will help as a starting point.

cities-gb.zip

EDIT: If it were possible to get the region_code for all GB cities (and any other countries that have a similar situation) then generating valid ISO 3166-2 code for any city in any country would work as follows:

const iso3166_2 = `${city.country_code}-${city.region_code ?? city.state_code}`

[anonbergine]

Just found this which might help fix up more records for UK cities, I will investigate in a few days if time permits: https://simplemaps.com/data/gb-cities

[UNTAMEDHero]

Does anyone have a fix for UK ? as most of them cities are in "England" state instead of them right ones.