Solved

Database of State, County, City, Zip

Posted on 2008-10-27
8
1,667 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 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

624 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