/countries-regions-cities

MySQL ready-to-use tables with countries, regions/states and cities of the world.

MySQL normalized and ready-to-use tables of Countries, Regions (Provinces/States), Cities

Installation

The SQL file includes create statements with indexes, so simply follow the steps below to import the sql file.

Linux/OSX command line:

$ gunzip world.sql.gz
$ mysql -u myusername -p mypassword
mysql> use mydatabase;
mysql> source world.sql;

Windows

  • Install 7zip (so you can extract gzip files)
  • Extract world.sql.gz and then use the same mysql command lines as Linux above on the world.sql file

Data Structure

Countries table

  • Size (uncompressed, in database w/ indexes): 32 KB
  • 230 records
Column Type Description
id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT Primary Key for a Country
name VARCHAR(255) NOT NULL Country name
code VARCHAR(10) NOT NULL Country abbreviation

Regions table (regions/provinces/states)

  • Size (uncompressed, in database w/ indexes): 320 KB
  • 3,888 records
Column Type Description
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT Primary Key for a Region
name VARCHAR(255) NOT NULL Region name
code VARCHAR(10) NOT NULL For USA: State/Territory abbreviation
country_id SMALLINT(5) UNSIGNED NOT NULL Foreign key to Countries table

Cities table

  • Size (uncompressed, in database w/ indexes): 272 MB
  • 2,790,951 records
Column Type Description
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT Primary Key for a City
name VARCHAR(255) NOT NULL City name
region_id INT(11) UNSIGNED NOT NULL Foreign key to Regions table (States for USA)
country_id SMALLINT(5) UNSIGNED NOT NULL Foreign key to Countries table
latitude DECIMAL(10,8) NOT NULL City Latitude
longitude DECIMAL(11,8) NOT NULL City Longitude



License for MaxMind WorldCities Database

OPEN DATA LICENSE for MaxMind WorldCities and Postal Code Databases

Copyright (c) 2008 MaxMind Inc. All Rights Reserved.

All advertising materials and documentation mentioning features or use of this database must display the following acknowledgment: "This product includes data created by MaxMind, available from http://www.maxmind.com/"

Redistribution and use with or without modification, are permitted provided that the following conditions are met:

  1. Redistributions must retain the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
  2. All advertising materials and documentation mentioning features or use of this database must display the following acknowledgement: "This product includes data created by MaxMind, available from http://www.maxmind.com/"
  3. "MaxMind" may not be used to endorse or promote products derived from this database without specific prior written permission.

THIS DATABASE IS PROVIDED BY MAXMIND.COM ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL MAXMIND.COM BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS DATABASE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.