Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Database of State, County, City, Zip

Posted on 2008-10-27
8
Medium Priority
?
1,688 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 111

Accepted Solution

by:
Ray Paseur earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 111

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 111

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
Suggested Courses

886 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