US City and County List and Database


DOWNLOAD IMMEDIATELY AFTER PURCHASE!
US Cities and Counties List and Database
Single-site license
$39.95


The US Cities and Counties List and Database is a great tool for Webmasters, SEO consultants, Real Estate consultants, and Pay Per Click marketers.

Our US Cities and Counties List and Database contains 198,703 precise data records for US populated places, each with 11 columns of useful data. The list also includes 3,247 US county names. Last updated on August 1, 2011. The data is formatted as a CSV file and can be imported into MySQL, Microsoft SQL Server, Oracle, PostgreSQL, and any other database. This product is a great time saver and you may use it as an information source, a web development resource, for a development project or whatever else comes to your mind. In short our product allows you to create applications which target locations geographically across the country. We understand you are looking for information and you need it now! No need to wait any longer - our cities and counties database becomes available for immediate download upon purchase. The US Cities and Counties List and Database is based on the data from the US Geographic Name Information System (GNIS) and normalized for ease of use with popular Database Management Systems.

Fields in the US Cities and Counties List and Database


Name Type Description
id Number Unique populated place record identifier.
place_name Character Official feature name as defined in INCITS 446-2008, Identifying Attributes for Named Physical and Cultural Geographic Features (Except Roads and Highways) of the United States, Its Territories, Outlying Areas, and Freely Associated Areas, and the Waters of the Same to the Limit of the Twelve-Mile Statutory Zone.
state_abbrev Character The unique two letter alphabetic code and the unique two number code for a US State as specified in INCITS 38:200x, (Formerly FIPS 5-2) Codes for the Identification of the States, the District of Columbia, Puerto Rico, and the Insular Areas of the United States.
state_code Character
county_name Character The name and unique three number code for a county or county equivalent as specified in INCITS 31:200x, (Formerly FIPS 6-4) Codes for the Identification of Counties and Equivalent Entities of the United States, its Possessions, and Insular Areas.
county_code Character
lat_dms Character The official feature location as defined in INCITS 446-2008, Identifying Attributes for Named Physical and Cultural Geographic Features (Except Roads and Highways) of the United States, Its Territories, Outlying Areas, and Freely Associated Areas, and the Waters of the Same to the Limit of the Twelve-Mile Statutory Zone.

NAD 83
DMS-degrees/minutes/seconds
DEC-decimal degrees.

Note: Records showing "Unknown" and zeros for the latitude and longitude DMS and decimal fields, respectively, indicate that the coordinates of the feature are unknown. They are recorded in the database as zeros to satisfy the format requirements of a numerical data type. They are not errors and do not reference the actual geographic coordinates at 0 latitude, 0 longitude.
long_dms Character
lat_dec Number
long_dec Number
elev_in_m  Number Elevation in meters above (-below) sea level of the surface at the primary coordinates from National Elevation Dataset
elev_in_ft  Number Elevation in feet above (-below) sea level of the surface at the primary coordinates from National Elevation Dataset

US Cities and Counties List and Database sample data

You may want to download and evaluate our US Cities and Counties List and Database sample data to make sure you understand what you are going to get before you make the purchase decision. Our sample data includes about 200 records and will allow you to familiarize yourself with our data format, practice the import procedure, run SQL queries etc.

Download US Cities and Counties List and Database sample data


How to import the US Cities and Counties list and database to MySQL

First create a table for the data using something like this:

CREATE TABLE `cities_and_counties` (
  `id` int(6) NOT NULL,
  `place_name` varchar(63) NOT NULL,
  `state_abbrev` varchar(2) NOT NULL,
  `state_code` varchar(2) NOT NULL,
  `county_name` varchar(26) NOT NULL,
  `county_code` varchar(3) NOT NULL,
  `lat_dms` varchar(7) NOT NULL,
  `long_dms` varchar(8) NOT NULL,
  `lat_dec` decimal(9,7) NOT NULL,
  `long_dec` decimal(10,7) NOT NULL,
  `elev_in_m` varchar(5) NOT NULL,
  `elev_in_ft` varchar(5) NOT NULL,
  PRIMARY KEY (`id`)
) CHARSET=utf8;

Of course you may decide to use another table name. The next and final step is to run the 'load data' command to place the data in the table created in the first step.

load data infile 'your_path_here/cities.csv' 
into table cities_and_counties
fields terminated by ',' 
optionally enclosed by '"' 
lines terminated by '\r\n';

What if the target database is on a remote computer? In that case you first need to connect your MySQL Command Line Client to the remote server. Something like this

mysql --host=www.yourdomainname.com -Dyourdatabasename --password=yourpassword --user=yourusername

Then use the 'load data' command but this time with the 'local' optionm

load data local infile 'your_path_here/cities.csv' 
into table cities_and_counties
fields terminated by ',' 
optionally enclosed by '"' 
lines terminated by '\r\n';

How to get just the list of all US counties?

The list of US counties can be created by running a simple SQL query

SELECT DISTINCT state_abbrev, state_code, county_name, county_code 
FROM cities_and_counties
WHERE county_name <> '' 
ORDER BY state_abbrev, county_name
LIMIT 4000

This query return 3247 US county names.

How to get the list of cities in a specific county?

For example you want the list of cities in Collier County, Florida. Use a SQL query like this:

SELECT * from cities_and_counties
WHERE state_abbrev='FL' AND county_name='Collier' 
ORDER BY place_name

What's good about the latitude and longtitude paramaters?

They can be used for example to show location of a city using Google Maps API. You will use 'lat_dec' and 'long_dec' for this purpose. Your code will look something like this:

if (GBrowserIsCompatible()) 
{
	map = new GMap2(document.getElementById("map_canvas"));
	map.setCenter(new GLatLng(latitude, longtitude), 11);
}

Of course the code to display a city using Google Maps is much longer, we just show here how 'lat_dec' and 'long_dec' are relevant.

NOTE: This list can be used in conjunction with US State List for web developers, US Cemetery List and Database for web developers, and US Churches List and Database for web developers.

Share this article!    Bookmark US City and County List and Database at Delicious Digg US City and County List and Database Share US City and County List and Database with your Twitter followers Share US City and County List and Database at StumbleUpon Add US City and County List and Database to your Technorati favorites Share US City and County List and Database on Facebook Share US City and County List and Database on Blinklist Share US City and County List and Database on Diigo Share US City and County List and Database on Reddit Share US City and County List and Database on Linked In Seed the vine with US City and County List and Database Share US City and County List and Database on Mixx Share US City and County List and Database on DZone