U.S. Cities Database

The SQL file has 29.880 registered cities. All cities are located in the United States.

The SQL file contains two tables:

• US_STATES: ID, STATE_CODE and STATE_NAME.

• US_CITIES: ID, ID_STATE, CITY, COUNTY, LATITUDE and LONGITUDE.

The US_CITIES table has all (or almost all) cities from the United States.

Compatibility

The us_cities.sql file is compatible with MySQL. See sqlite for the sqlite3 compatible version.

Samples

US_STATES

US_CITIES

Export Data

You can use the following SQL command to export the data to a CSV file.

SELECT
	US_CITIES.ID, US_STATES.STATE_CODE, US_STATES.STATE_NAME, US_CITIES.CITY, US_CITIES.COUNTY, US_CITIES.LATITUDE, US_CITIES.LONGITUDE
FROM
	US_STATES
INNER JOIN
	US_CITIES
ON
	US_STATES.ID = US_CITIES.ID_STATE
ORDER BY
	US_CITIES.ID ASC
INTO OUTFILE
	'C:/US_Cities.csv'
FIELDS TERMINATED BY
	';'
ENCLOSED BY
	''
LINES TERMINATED BY
	'\n';

For convenience, the generated CSV file is here.

License

This project was created under the MIT license.

If you find some inconsistent data, such as a duplicated city, please open an issue explaining what is happening or directly fix the problem and send a Pull Request.

References

This SQL dump was created based on the SQL dump that can be found at the following link: http://www.farinspace.com/us-cities-and-state-sql-dump/

Some changes that have been made:

• The zip column was deleted.
• Some duplicated cities were deleted.
• All data were reorganized (rows and columns).
• It was included the Puerto Rico state name that was missing in the original SQL.
• It was included the US_STATES table.