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.