Solved

Database of State, County, City, Zip

Posted on 2008-10-27
8
1,655 Views
Last Modified: 2012-05-05
I need a database that contains State, County, City, Zip.
I will import this data into my web application.
Preferred free, will pay if applicable.
Also, if there is a flash map of some sort that would be helpful.
0
Comment
Question by:pmsguy
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22816398
Hi there;

I don't know if you are searching for this...a mysql dump as attached...

Best regards...
CREATE TABLE address (
contact_id smallint(6) DEFAULT '0' NOT NULL,
StreetAddress char(50),
City char(20),
State char(20),
Zip char(10),
Country char(20),
PRIMARY KEY (contact_id)
);
INSERT INTO address VALUES (1,'300 Yamila Ave.','Los Angeles','CA','300012','USA');
INSERT INTO address VALUES (2,'4000 Nikki St.','Boca Raton','FL','500034','USA');
INSERT INTO address VALUES (3,'404 Tia Blvd.','New York','NY','10011','USA');
CREATE TABLE company_details (
contact_id smallint(6) DEFAULT '0' NOT NULL,
CompanyName char(25),
Designation char(20),
PRIMARY KEY (contact_id)
);
INSERT INTO company_details VALUES (1,'Xerox','New Business Manager');
INSERT INTO company_details VALUES (2,'Cabletron','Customer Support Eng');
INSERT INTO company_details VALUES (3,'Apple','Sales Manager');
CREATE TABLE email (
contact_id smallint(6) DEFAULT '0' NOT NULL,
Email char(20),
PRIMARY KEY (contact_id)
);
INSERT INTO email VALUES (1,'yamila@yamila.com');
INSERT INTO email VALUES (2,'nikki@nikki.com');
INSERT INTO email VALUES (3,'tia@tia.com');
CREATE TABLE names (
contact_id smallint(6) DEFAULT '0' NOT NULL auto_increment,
FirstName char(20),
LastName char(20),
BirthDate date,
PRIMARY KEY (contact_id)
);
INSERT INTO names VALUES (3,'Tia','Carrera','1975-09-18');
INSERT INTO names VALUES (2,'Nikki','Taylor','1972-03-04');
INSERT INTO names VALUES (1,'Yamila','Diaz','1974-10-13');
CREATE TABLE telephones (
contact_id smallint(6) DEFAULT '0' NOT NULL,
TelephoneHome char(20),
TelephoneWork char(20),
PRIMARY KEY (contact_id)
);
INSERT INTO telephones VALUES (1,'333-50000','333-60000');
INSERT INTO telephones VALUES (2,'444-70000','444-80000');
INSERT INTO telephones VALUES (3,'555-30000','555-40000');

Open in new window

0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 22816418
http://nerdvittles.com/trixbox123/zipcodes.zip - you should look for the license file here:
http://nerdvittles.com/trixbox123/ZIP_CODES_license.pdf

You may have to look elsewhere for a flashmap.  If you find a free one, I'd love to hear about it.

Also, the nerdvittles data set, while free, has some useless information like APO and FPO data.  You may want to cleanse it some.

Finally, if you are interested in geocoding, be award that zip codes are not geo-locations. They are postal carrier routes.  They sometimes change and occasionally overlap, etc.

Best of luck, ~Ray
0
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22816644
hmm...i got the question wrong i guess...
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 10

Expert Comment

by:dbmullen
ID: 22817103
I'm assuming you want a "web-service" interface to get a "list of State, County, City, Zip"
not a database table.

I did a google search, found the following:
http://www.zipcodedownload.com/
http://www.census.gov/geo/www/gazetteer/places2k.html
http://www.melissadata.com/

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 22817171
Actually it's not too big a data base once the extraneous stuff (PO Boxes, APO, etc) is thrown out.  I have the USA lower 48 in a table and it's only about 42,000 records.  Even with a lot of indexing, it's manageable on my server.
0
 

Author Comment

by:pmsguy
ID: 22822032
Ray_Paseur - thanks - this is exactly what I am looking for.

A couple of questions, do these indexes degrade the MySQL table?

What is the column zipclass used for?

Why do you have the column fullstate in this table and not some other table?

How up to date is the data?  Where do you find updates for the state,county,city,zip?

Do you have a flash map or some other presentation software where a user can click a state,county?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 22822113
The indexes do not degrade; they should help improve performance.  But they make the table larger.

The zipclass tells you what kind of zip code you're dealing with - APO/FPO, PO Box Only, etc.  Not much point in geocoding a PO Box ;-)

I don't have fullstate in any colunm - I dropped it when I used the nerdvittles files.

The data is about 80% accurate.  You can get better data from paid sources, or you can try geocoding the data from nerdvittles by using the Yahoo and/or Google geocoder API.  Updates for state, county, city, zip are infrequent - you can find this information from the USPS - just search for it on Google.

I don't have a flash map, however the Google Maps web services might have what you're looking for.

HTH, ~Ray
0
 

Author Closing Comment

by:pmsguy
ID: 31510503
Thanks Ray - exactly what I needed.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question