MaxMind offers a free cities database, the database contains more than 2.7 mil. locations, you can download it at MaxMind’s website.
Once you have downloaded the compressed database, extract the file worldcitiespop.txt and invoke the MySQL client (preferably from the same directory where the worldcitiespop.txt file resides).
Create the world database and the cities table
Here is the structure for the world database (remove the first two lines if you already have a database):
-- Database: `world` CREATE DATABASE `world`; USE `world`; -- Table: `cities` CREATE TABLE IF NOT EXISTS `cities` ( `Country` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `City` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `AccentCity` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Region` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Population` int(10) unsigned NOT NULL, `Latitude` float NOT NULL, `Longitude` float NOT NULL, KEY `AccentCity` (`AccentCity`(5)), KEY `City` (`City`(5)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Import cities from worldcitiespop.txt into database
LOAD DATA LOCAL INFILE _path_to_file_
INTO TABLE `cities`
FIELDS
TERMINATED BY ','
LINES
TERMINATED BY 'n'
IGNORE 1 LINES;
_path_the_file_ can be either an absolute path or relative path (relative to the folder from where you invoked the MySQL client), so in case you followed my instructions and invoked the MySQL client from the directory that contains the file worldcitiespop.txt, _path_to_file_ will be worldcitiespop.txt.
The file worldcitiespop.txt is as big as 130 MB so the process may take very much longer than a few seconds
What’s next? up to you…
Now that the database is installed you can use it for your application, I use it on one of my websites where users can choose one or more cities on their profiles, then they get a daily weather forecast for those cities.
Unfortunately in the cities.txt file from MaxMind there are many empty (00) region codes, making the DB unusable
These are the FIPS 10-4 Subcountry codes, some are hard to find.
thank you nabil
but did you have any link that have
`Latitude` FLOAT NOT NULL,
`Longitude` FLOAT NOT NULL,
for FIPS 10-4 Subcountry codes
regards
DELETE FROM cities WHERE City=” ;
repeat where needed
Most people don’t really need a database of over 2 million cities unless they really plan to do a geographical survey. It is important to stay realistic about the locations that users are going to be looking for.
When I was looking for a decent database of world cities for my web project, I tried using all of the free city databases out there. I even paid for a couple of them.
But none of them satisfied me. They either had too much useless information (like longitudes, administrative subdivisions for tiny countries, or thousands and thousands of entries for small countries) or too little.
This is why I decided to create my own database.
After that was done I decided to share the results of my work with the world.
I offer my database both in Excel and SQL formats. It has over 40,000 of U.S. cities and a total of over 70,000 cities. To me it is the perfect balance between completeness and usefulness.
And you can’t go wrong with a database that only costs $4.99!
If you are interested, visit http://worldcitiesdatabase.info and see if my database is right for you.
Hi,
I tried you script but unfortunately it seems not working. In fact, when I extract the txt from the tar.gz, my txt content seems to be bytecode. So, when I import it, the content of my database is unreadable.
I also sent an email and a posted a question on maxmind forum, but no answer. I know that it looks newbee, but anybody could help me?
Thanks,
Hi,
Your command does not work, the file (worldcitiespop) seems to be bytecode inside. Does the file double compressed or database bytecode? How to import this new version?
Thanks,
Hi, I found the solution : the file is twice zipped (tar.gz), but in the wrong way.
So here is the process : gunzip the tar.gz file. You gonna have a worldcitiespop.txt. Rename this file as a tar.gz. Gunzip (force if it’s required) this file. You gonna obtain a worldcitiespop.tar file. Rename this file as a txt and here is it!
Sorry I didn’t answer earlier I was busy. Please read my post again, I said:
Looking for a 100% database or as close as possible. Has anyone had any issues with this one not being complete?
Such thing does not exist, sorry!