Geonames: The only terrible choice we have

Table of Contents

  1. Loading your data
  2. Associating your data
  3. The mysterious admin1Codes.txt

When it comes to getting an accurate, up to date database of cities, towns, and what have you, really the best we can hope for is Geonames. They manage to keep things updated fairly regularly, however there also seems to be a slight, just a slight, massive tidal wave of garbage all mixed in.

As they say on the BBC: I'm on a journey… to figure out how to get the cities, associated provinces, and postal codes from these data dumps, and associate them together. You'd think it'd be easy, you'd think the CSVs have all of the proper association information there, easy to manage, and of course that's what a sane person would think.

Well, I've got a surprise for you.

I'm not going to go super in-depth here all tutorial-style, but rather just outline some things I had to deal with and the solutions I came up with, in hopes they will help you, because they sure as hell were not easy to find.

Loading your data

Loading it is fairly straight forward for a lot of people, but some people it isn't quite clear. A few things need to be considered:

  1. Regular utf8 columns in MySQL will not allow you to import alternative names and some other stuff
  2. In some cases even utf8mb4 columns aren't "good enough" to import some alternative names because the alternative names themselves aren't "good enough" and are malformed Unicode.
  3. Most alternative names are either identical, misspellings, or bizarre stuff like airport codes, misspellings in other languages, what seems to be some sort of sub-typing (like one for Moscow was "wsa     MOW" , what the hell that means, I have no idea), and you'd think the city in the native language/script would come first, but it usually doesn't, if at all. Though I'm sure you'll enjoy all of the Gothic unicode script of names which are absolutely necessary since we live sometime in the Early Middle Ages. 
  4. My suggestion is to just ignore the alternative names completely, but if you insist on importing them, use varbinary or blob.
  5. There are tons and tons of duplicates, with our Moscow example alone there are currently 7 versions of the exact same city, in the same country, and yes the feature type is the same.

The easiest way to do this is create a table in MySQL identical to the schema they lay out for whatever dump you have, but maybe varbinary instead of varchar (as mentioned above). In some cases you may want to add a little on the end too, some columns go over the documented width.

From your MySQL client:

This general idea works with all of the dumps so long as the table is exactly the same.

Whoops, hold on, for some reason there are needless backslashes escaping the tabs from time to time, so you have to go in and replace those otherwise MySQL will freak out. Are these backslashes a part of the names of some cities or places, perhaps a part of a strange alphabet?

LOL, of course they're not, they're just randomly there.

Associating your data

With the locations, you'd think with name like geonameid that would mean other dumps like postal would match up with it in some way, or they'd easily reference administrative divisions by ID… come on, you know that wouldn't do something that logical.

Geonames has the most horrendously, implausibly baffling, most terribly undocumented method of associating data I've seen in years; it's a wonder anyone uses it. It's made worse by the fact that those who have figured it out (which to me seems on par with breaking Egyptian Hieroglyphs) essentially keep it a secret as if posting about it on a forum will cause the Stasi to burst in their door and take their families away.

How the data are associated is pretty damn goofy and poorly thought out:

  • Countries are referenced by their ISO-3166-1 code, at least this is somewhat consistent, in fact GB is used rather than UK, so don't confuse with the almost identical country top-level domains. SX and XK are also in use for the new nations of South Sudan and Kosovo, since as of writing this, they do not have codes yet.
  • Cities and states/provinces: You'd think they'd follow the logical geonameid integer logic, but at this point I guess I don't need to say it's far more retarded than that. Cities are associated with provinces by the country and admin1 columns.  So what are these admin1 values? In some cases like US states they make sense, they're the ISO standard abbreviations, in some cases they're area codes, and in some cases they're various other things. (See bottom of this post about where to get the admin1 values from).
  • Postal codes are matched primarily by country and admin1 , admin2 (when they're properly filled, sometimes not), and the "place_name," however if you're expecting place_name to match the city in spelling in the other dump (such as O'Brien in the locations dump to be also O'Brien in the postal codes dump), you are sorely mistaken, yet again. Instead spelling changes are haphazard, so it can be "O Brien" or "OBrien" but never the expected "O'Brien," I mean jeeze, it's almost like they're running a psychological experiment on us.
  • As for other stuff, I don't have any information, my only concern was getting associated cities, provinces/states, countries, and postal codes, but it doesn't take a massive leap of faith to guess they're all fucked up too.
The mysterious admin1Codes.txt

So you want to associate your cities with the appropriate province/state? "Tough shit, asshole" say the administrators of Geonames, they make it basically impossible.

How you say?

So where do they keep the states/provinces? No where, they deleted them. Apparently they felt it was "confusing for a lot of users" because of various issues, so instead of fixing those, they simply remove admin1Codes.txt from their web site, but continue to reference it in the official readme.txt for how to import the data.

Brilliant! That's exactly how not to confuse people, reference a non-existent file which contains important information on which cities belong to which provinces/states in countries, just delete it and replace it with nothing, because having anything at all would be confusing.

I don't recommend using the one linked by "marc," it's missing a lot and it's badly encoded.

I went ahead and recreated a new admin1Codes.txt, I based it on combining several versions of admin1Codes.txt I found, being certain to actually use the same encoding type through the whole file and making sure all of the cities listed have a code.

admin1Codes.txt with names in "plain-text" Unicode
admin1Codes.txt with names in hex (for easy transport, but they're still Unicode bytes)

If you import this, be certain to use utf8mb4_unicode_ci columns, and I explain at great length in my post: Better Unicode support for MySQL (including emoji).

Disclaimer: I can't vouch for the accuracy of the names or whether or not obsolete ones still exist, but hell, it's better than providing you with nothing.

In case you are interested in contributing to this list, an example of the missing codes can be found here: missing.txt, if you manage to match them up, let me know. A few I started to manually do, but since I spent so much time on this already, I stopped. I'm also considering starting an API service like Geonames that uses proper association. I realise the job of getting accurate data isn't easy, but if they've got people manually entering things to properly set it (and they do) then why in the hell don't they maintain a logical, static association?

5 thoughts on “Geonames: The only terrible choice we have”

  1. Hi Tony, I'm building a web application built on cities data. So far Geonames is the best I've found but completely agree on all the problems you've mentioned. Additionally the cities themselves are quite a mess. For example, I'm looking at data around my home city of New York. We have New York City but each of the borroughs are separately list as well – you would think one or the other. Then I did a check on my small hometown of Kensington, MD. There is a North Kensington, MD, South Kensington, MD, and Kensington, MD in the database. I've never heard of splitting the city into three like this.

    Are you aware of any database, even paid, that gives us a 'real' list of cities? As an any city you would actually write on an envelope and mail… Thanks, KH

    1. Unfortunately no, even paid ones I've dealt with are often just as bad, and typically more incomplete. There just needs to be a new project to fix this, I suspect. Something with proper association too would be nice, the methods they use to associate cities to provinces and countries, etc actually just makes things worse, and often they leave things out (as noted in my post). It's a real dog turd, but what else can we do for now?

  2. What a paradox that you insult to amazing people at Geonames, when they provide that amazing data for free, and you just provide your useless opinion in your blog…

    1. That's because I am a hypocrite and a jerk, but you're wrong, I didn't just provide my useless opinion. I also provided a text file needed to map their data which, at least at that time, had been missing on their site for months, thereby making their data types hard to map. I put in a hell of a lot of work assembling it from multiple places and verifying the data with little to go on I had. They purposefully withheld useful information, that's just being a cocksucker trying to fuck with people, no more, no less. Instead I was being a pussy.

      Hmm… maybe I should rephrase that.

      Anyway, I mentioned this in this post, maybe you didn't read that far or maybe you can't read that well. So not just a useless opinion, but I did their job for them, so up yours turd face!

      1. I agree sometimes you should always provide your criticism, if we make a mistake for ignorance then oh well. eventually, we will be provided with an answer. Thank you?

Leave a Reply