Specifications

Data formats

Format Extension Description
Excel .xls Suitable for use with Microsoft Excel and other spreadsheet applications including Open Office and Libre Office Calc.
Comma-separated values .csv Plain text data file suitable for use with most GIS systems, programming languages and text editors.
SQL .sql ANSI-standard SQL suitable for importing to MySQL, PostgreSQL, SQLite, Microsoft SQL Server, PHPMyAdmin and other SQL databases.

Tried and tested with…

Access MySQL SQL Server PostgreSQL SQLite Access PHPMyAdmin Open Office

Data fields

The data contains 45,605 records covering all incorporated cities, towns, villages, townships and boroughs in the United States.

The fields included in the data are:

Field Example Type Description
id 3119 INTEGER A unique numeric identifier for the city.
name San Clemente TEXT(35) The official name of the city. Please see the naming conventions for more information.
county Orange County TEXT(80) The name of the county or local government area for the city. If the city spans multiple counties, the county names are separated with a forward slash character and listed in order of total area covered (with the largest first).
state_code CA TEXT(2) The 2 character abbreviation for the state that the city falls in. State codes are defined in ISO 3166-2.
state California TEXT(20) The full-length name of the state that the city falls in.
zip_code 92624 / 92672 / 92673 / 92674 TEXT(2701) All ZIP codes in active use within the city limits. If a city spans multiple ZIP codes they are separated with a forward slash character.
type City TEXT(23) Indicates the type of record: Borough, CDP (Census Designated Place), Charter Township, City, City and Borough, Consolidated Government, Corporation, Metro Government, Metropolitan Government, Municipality, Other, Town, Township, Unified Government, Urban County or Village.
latitude 33.42697 NUMERIC(8,5) The WGS84 World Geodetic System latitude coordinate for the centre point of the settlement to five decimal places.
longitude -117.61199 NUMERIC(8,5) The WGS84 World Geodetic System longitude coordinate for the centre point of the settlement to five decimal places.
area_code 949 TEXT(33) The telephone area code for the city. If a city spans multiple area codes, the codes are separated with a forward slash character.
population 65,326 INTEGER The total population living within the city limits, using the latest US census 2017 population estimates. If not available this field is set to 0.
households 24,184 INTEGER The total number of households within the city limits using the latest 5 year estimates from the American Community Survey. If not available this field is set to 0.
median_income 90,071 INTEGER The average (median) household income in US Dollars for the city using the latest 5 year estimates from the American Community Survey. If not available this field is set to 0.
land_area 47,542,284 BIGINT The area of land covered by the city in square meters.
water_area 1,965,336 BIGINT The area of water covered by the city in square meters.
time_zone America/Los Angeles TEXT(30) The IANA time zone for the city.

Naming conventions

All city names in the database use the following naming conventions:

  • Colloquial city names are included in brackets after the official name. For example: El Paso de Robles (Paso Robles).
  • Compound city names are separated by a hyphen with no whitespace. For example: Winston-Salem and McRae-Helena.
  • Consolidated city-counties use the city name only. For example: Nashville (not Nashville-Davidson) and Augusta (not Augusta-Richmond).
  • Saint is abbreviated to St. where applicable For example: St. Marys Point.