Page 1 of 1
USA states, counties, and cities
Posted: Thu Aug 10, 2006 9:42 am
by shiznatix
Does anyone know where I can get a database that has all the states, counties in each state, and cities in each county? That would make my life like 10000 times easier than going through wikipedia and getting all of them by hand.
On a side note, if I have at table of cities that has 18000 rows in it (the cities table) will that be slow if I try to select all the cities for a county? Is there a better way to set it up than 1 table for states, 1 for counties, and 1 for cities?
Re: USA states, counties, and cities
Posted: Thu Aug 10, 2006 10:00 am
by s.dot
shiznatix wrote:On a side note, if I have at table of cities that has 18000 rows in it (the cities table) will that be slow if I try to select all the cities for a county? Is there a better way to set it up than 1 table for states, 1 for counties, and 1 for cities?
Nope, won't be slow. I run a database of zip codes that selects from ~900,000 rows, and the query is relatively fast.
As far as DB structure, that would be the most logical way of doing it.
Posted: Thu Aug 10, 2006 10:08 am
by Luke
census.gov has that information. They even have lat/lon coordanates for boundary lines of cities, states, and counties. It's on their site somewhere in dbf format as well as a few other formats.
Posted: Thu Aug 10, 2006 10:20 am
by RobertGonzalez
If your tables are optimized and you have the right keys set up, you can select 40K records in a SELECT query in about .2 seconds. Could be faster on a fast server. That is just from my local machine.
Posted: Thu Aug 10, 2006 10:30 am
by shiznatix
oh wow. alright then my database is fine.
i have been looking at this census.gov website and even downloaded the dataferret but its quite strange all the way around. anyone have any expierence with that?
Posted: Thu Aug 10, 2006 10:38 am
by RobertGonzalez
Do a search for 'zip codes' with author 'burrito' on our forums. He posted a link to a mysql table dump of zips. Not sure if it has counties, but the cities and zips are in it. And it is free and ready for installation.
Posted: Thu Aug 10, 2006 10:56 am
by Luke
yea the data at census.gov is a PAIN to work with, but it's there.
Posted: Thu Aug 10, 2006 10:03 pm
by RobertGonzalez