1

For country and state, there are ISO numbers. With City, there is not.

Method 1:

Store in one column:

[Country ISO]-[State ISO]-[City Name]

Method 2:

Store in 3 separate columns.

Also, how to handle city names if there is no unique identifier?

Bear Bile Farming is Torture
  • 4,317
  • 4
  • 27
  • 61
  • 2
    **DO NOT** concatenate together multiple pieces of info - ***not ever***! Use three separate columns - stringing it together to show on screen is easy, if you need to do that! – marc_s Jan 15 '22 at 07:41
  • Only cities in the USA? – LukStorms Jan 15 '22 at 11:39
  • Btw, there exists also [IATA](https://www.iata.org/en/services/codes/) codes, but that's more for the travel industry. – LukStorms Jan 15 '22 at 11:44
  • related old SO post [here](https://stackoverflow.com/questions/7066825/is-there-an-iso-standard-for-city-identification) – LukStorms Jan 15 '22 at 12:24
  • [Here's some good info](https://stackoverflow.com/a/3653574/7644018) on why storing delimited lists in a DB is a _bad_ idea. – Paul T. Jan 15 '22 at 22:25

1 Answers1

1

First and foremost, three separate columns to keep your data. If you want to create a unique identifier, the easiest way would be giving a random 3-10 digit code depending on the size of your data set. However, I would suggest concatenating [country-code]-[state-code]-[code] if you have a small data set and if you want human readability to a certain point. code can be several things. Here are some ideas:

  • of course a random id or even a database row id
  • licence plate number/code if there is for a city
  • phone area code of the city or the code of the center
  • same logic may apply to zip codes
  • combination of latitude and longitude of the city center up to certain degree

Here are also more references that can be used:

  • ISO 3166 is a country codes. In there you can find codes for states or cities depending on the country.
  • As mentioned IATA has both Airport and City codes list but they are hard to obtain.
  • UN Location list is a good mention but it can be difficult to gather the levels of differentiation, like the airport code or city code or a borough code can be on the same list, but eventually the UN/LOCODE must be unique. (Airport codes are used for ICAO, similar to IATA but not the same)
  • there are several data sets out there like OpenTravelData or GeoNames that can be used for start but may require digging and converting. They provide unique codes for locations. And many others can be found.

Bonus: I would suggest checking Schema.org's City Schema and other Place Schemas for a conscious setup.

kingofsevens
  • 515
  • 3
  • 9