Solved

Database of State, County, City, Zip

Posted on 2008-10-27
8
1,610 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 108

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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 108

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 108

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now