USA and Canada Cities Database with Latitude and Longitude

This is a complete USA and Canada Cities Database with Latitude and Longitude containing 147,000+ cities and places of US and Canada. The size of the package is 4.8 megabyte zip file having 15 megabytes of SQL dumps when unzipped. The package contains CSV text file and readily executable SQL dump files to create and populate the US and Canada city database. Any other data format is available on request. The data is ready to be published on your content web site or application.

The file "world-city-database-regions.sql" should be run first in order to create the tables and all entries but the cities. Subsequently run "us-canada-cities.sql" script to create the entries in CITIES table. The first script will create 4 tables namely CONTINENTS, COUNTRIES, REGIONS and CITIES. If you want you can just create CITIES table (as you are interested in only US and CANADA cities you may not need all the country and region names) and delete all other table definitions but remember that CITIES table use country codes and region codes that are defined in COUNTRIES and REGIONS table. Also, if you keep these extra table definitions in your database you will be able to extend your database in future with records of other countries and cities available from this site. Definitions of all the tables are described below:

Fields in the Table CITIES:

1. CityID: City identification number
2. ISO2: Two character ISO country code
3. RC: Two character region code
4. City: Name of the city
5. AccentCity: Accented name of the city
6. Latitude: Latitude of the city
7. Longitude: Longitude of the city

Fields in the Table CONTINENTS:

There is a single field named [Continent] in this table. Actually the table contains name of the continents as well some mapping reference zones. The full list of the continents or zones is:

1. Africa
2. Antarctic Region
3. Arctic Region
4. Asia
5. Central America and the Caribbean
6. Commonwealth of Independent States
7. Europe
8. Middle East
9. North America
10. Oceania
11. South America
12. South East Asia
13. World Others

Fields in the Table COUNTRIES:

1. ISO2: Two character ISO country code
2. FIPS104: Two character FIPS country code
3. ISO3: Three character ISO country code
4. ISON: Numeric ISO country code
5. TLD: Top level Internet domain name for the country
6. Country: Name of the country
7. Continent: Continent or reference zone
8. Capital: Capital of the country
9. NationalitySingular: Nationality (singular form)
10. NationalityPlural: Nationality (plural form)
11. Currency: Currency of the country
12. CurrencyCode: Three character currency code
13. Population: Population of the country

Fields in the Table REGIONS:

1. ISO2: Two character ISO country code
2. RC: Two character region code
3. Region: Name of the region of the country

The corresponding SQL command, for example, in the dump file to create CITIES table is:

CREATE TABLE `Cities` (
`CityID` INTEGER NOT NULL, 
`ISO2` VARCHAR(2), 
`RC` VARCHAR(2), 
`City` VARCHAR(100), 
`AccentCity` VARCHAR(100), 
`Latitude` DOUBLE NULL, 
`Longitude` DOUBLE NULL, 
PRIMARY KEY (`CityID`)
) ENGINE=innodb DEFAULT CHARSET=utf8;

Table: Cities

	CityID	ISO2	RC	City	AccentCity	Latitude	Longitude
2639795	US	PR	columbia	Columbia	18.0063889	-65.9069444
2639796	US	PR	combate	Combate	18.4316667	-66.4313889
2639797	US	PR	comerio	Comerio	18.22	-66.2263889
2639798	US	PR	cometa	Cometa	18.1447222	-65.8291667
2639799	US	PR	comunas	Comunas	18.0891667	-65.8441667
2639800	US	PR	concordia	Concordia	17.9816667	-66.0372222
2639801	US	PR	condado	Condado	18.4563889	-66.0711111