Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database of State, County, City, Zip

Posted on 2008-10-27
8
Medium Priority
?
1,680 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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