Identifying and relating cities from different sources

Question

I have different providers which passes me an excel with different cities, in each city they use some special code for their operations and more data useful to my business.

The problem is that I have a mess with all these cities:

• I have my own cities in my database, around 9000 records.
• Provider A gives me his excel or webservice to get around 6000.
• Provider B gives me another 5000.
• Provider C ... etc

Some of the cities given by my providers are already in my database and I only have to update the required data I need.

Otherwise, I have to insert that new city in my database.

And this, each time a provider gives me an update of these cities.

Well, the main problem is that I call a city differently from them, and they differently from each other... how to know if I already have that city or I have to create a new one since we use different names?

The way I see it, I only can achieve it manually. Comparing their cities with mines.

Of course, it's too much work so I made my own script, and implementing the levehnstein function for the database, I can automatically see the more coincident ones and select them by a click. The script does the rest (updates their special operation code for that city into my corresponding city stored in my database).

Even with it, I still feel like I'm missing something. If there was an unicode for those cities this would be much easier and automatic, but I don't have any code which identifies these cities more than my table identifier. Same for my providers, despite some of the use to provide me the postal code among the cities their provide, but not all.

Is there any better solution than mine for this? Any universal code that you usually use or any other aproatch?

Edit: Well, each city belongs to a country. Of course, I'm considering that.

In my city table I have an Id for each destination, and then a column for the operation code of each provider (I know, this could be better represented with a relationship more), plus country code, zip, url for seo...

Respecting the solution mentioned by MagnusL, creating a Synonyms table, why would I need to store the synonyms? Regarding the script you mentioned with levehnstein and human interaction, that's exactly what I'm currently doing:

With each record provided by a provider and my destinations table. Given a provider city record, I'm showing the more coincident ones from my table.

But before this, I automatically link all those which are coincident in zip code and country.

It's a lot of work for updating my providers special operation code for each city. I am just curious about how people deal with this problem, I'm sure a lot of developers have to face this at some point.

Answer 1

If it is important that the cities are correctly matched, I would guess you must have some manual steps in your process. If you include names of smaller towns you will some day encounter that the same name could actually be two different places in two different countries. (Try Munich on Google Maps and you get one in Germany and one in North Dakota.)

A somewhat complicated, but I guess future proof, workflow is to use id numbers in place of city names in your main data table. Then set up a locations table with those id numbers as primary keys and your preferred name of the city followed by as many meta data columns as required for country code, zip code, WGS84 coordinates, continent name, whatever. Add another table for city name synonyms, with just id numbers and names (without UNIQUE constraint on the id column).

Let your import script try to match the city with help from as many meta data as possible (probably different meta data from different providers), together with the Levehnstein algorithm you mentioned, and let it be clever enough to ask for human interaction in those cases where no one or more than one city are matched. It can of course show you the closest possible guesses, so you can pick the right one and have it stored in the synonym table.

(Yes, it is a lot of coding to get there. If you find it worth it or not depends on how often you do these updates.)

Tip: Wikipedia has articles with different names on cities, i.e. https://en.wikipedia.org/wiki/List_of_names_of_European_cities_in_different_languages