The database, available for purchase from geodatasource.com comes in a tab-delimited format.
The gold edition which we integrated is 400Mb in size and seems pretty complete. The nice thing about it is that it contains the longitude and latitude of the cities which could enable users to search by distance anywhere in the world. Of course, the results will not be that accurate because for small distances all members from the same city as the member searching will be returned but hey, it's a start.
The database does have its errors and it is pretty difficult to work with given the size and the inconsistencies but it is usable anyway. I didn't like the fact that there's no obvious way to tell which of the listed cities are actually states (or counties for non-US countries).
Some of the states are listed as regular cities but some aren't and there's no way to identify that. Or at least none that I found.
Another bad thing is that there are duplicates. Some cities are listed multiple times. After the initial conversion we had 2.9 million cities and after the cleanup 2.4 - 2.5 remained. Still a lot of data.
Anyway, we managed to import it into Etano community builder database in a satisfying form.
BTW: the customer has an existing dating site, running a custom version of Dating Site Builder - the dating application we built and were selling before Etano. He's moving to Etano now and wants this world cities database integrated amongst other things.
So how was Etano behaving with the new database and no other tweaks or tuning? Good I'd say. Selecting a country from the list brought the list of states instantly and selecting a state brought about 1000 cities in about 1 second.
This wasn't good enough for us and given the fact that the cities aren't going anywhere and there's no reason to keep the data in the database we moved everything on disk in the cache folder in an organized directory structure to make it easy and fast to get exactly the needed list of states or cities.
We also changed the default javascript and AJAX code required to retrieve the list and display it in the browser. It's not that nice now but it does its job and it's faster.
The result? ~2000 cities retrieved and displayed in the browser in 1 second. That's double the original speed!
Pretty cool, eh?
So, if you plan to take over the whole world with your dating site or sns, we can help you with this
