January 22, 2009 at 9:05 am
I remember a few posts that I am currently unable to find on this site regarding the use of zipcodes and how they can span counties and even states in the US.
I'm looking for any examples of them spanning states that people know of.
Any help would be greatly appreciated.
TIA
-Luke.
January 22, 2009 at 9:45 am
Zip-code prefixes are state-specific. Can't span states. Lots of them span counties, but they don't span states. Even cities that span state-lines, have different Zip codes the parts of the city in one state vs the part in another.
A good example is Texarkana, which is a city on the Texas-Arkansas state line. "State Line Ave" runs through the middle of the city. Their TX Zip codes start with 75, while their AR Zip codes start with 71, because those numbers are state-specific.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 10:06 am
As an addendum, zip-codes.com and usps.com have all kinds of data and tools for Zip code lookup, rules on Zip codes, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 11:02 am
I was out on the zip-codes.com site and was looking at their demo files. In the business version on of the new fields is Multiple County Data in the Business Database...
their description found here states that:
"Each ZIP Code that falls into more than one State/County will be listed in this table for each State and County it covers. "
I understood the county bit as it's pretty common, but for some reason I thought I remembered someone coming up with a zip that spanned not just counties but states as well. I guess I was hoping someone out there was using their (zip-codes.com's) database and could just run a quick query for me on that table, without me having to pay for it for a 1 off query.
Thanks,
-Luke.
January 22, 2009 at 12:57 pm
We use Tigerzip. The data they provided us has latitudes and longitudes so that when a user plugs in a zip code they can also choose a distance from the zip code in their serach. So if they are looking for childcare within a 10 mile radius of their selected zip code, using the data entered and a little algorithm using cos and sin, we can produce the desired reults.
-- You can't be late until you show up.
January 22, 2009 at 1:32 pm
I had access to a copy of the zip-codes.com database till a few weeks ago, and then changed jobs, so I can't check it for you now. I know someone who can (my replacement at the prior job), and I'll ask her to check it out for you.
Their database does have lat-long-elevation data in it, as well as a ton of other stuff. Very useful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 1:36 pm
Yeah I'm aware of their product and have brought it up a few times for address validation and such rather than trying to maintain our own homebrew system, but when "free" works "well enough" it's hard to justify the expense.
Thanks
-Luke.
January 22, 2009 at 1:58 pm
Hi there,
I'm GSquared's replacement and I ran that query on the zips database. There are no zips that cross state lines.
It also looks like there are no zips that cross county lines either. The Post Office is constantly adjusting Zip Codes and what area they cover, but I'm pretty sure they follow county lines.
(edited to modify signature)
-- Kit
January 22, 2009 at 2:04 pm
Luke L (1/22/2009)
Yeah I'm aware of their product and have brought it up a few times for address validation and such rather than trying to maintain our own homebrew system, but when "free" works "well enough" it's hard to justify the expense.Thanks
-Luke.
When I worked for a direct mail marketing company, $39.95/year for their standard model was an expense that was very easy to justify. If you have a dev or whatever spending more than about an hour or two a year on your home-grown system, you're actually losing money on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 2:13 pm
Zip codes cannot cross state lines because as Terry have covered it is very complex to calculate distance, it just appears to cross state lines because there is 73 in Oklahoma and 73 in Texas but 733 is only in Texas.
I once joined a project one developer claimed to be doing distance and I said no you are not because I don't see the math, a solution was later found to actually implement it. SQL Server 2008 comes with some built in but I have not used it and in 2005 there was some existing T-SQL code look for some and modify as needed. Here is the free site that makes lookup easy click on each state.
Kind regards,
Gift Peddie
January 22, 2009 at 2:17 pm
thanks for the quick response.
I appreciate your time, however it seems odd that your db is showing no multi county codes as http://www.zip-codes.com/zip_database_fields.asp#multicountydata states that approx 25% of Zips span counties... They state this is only avail in the Business version (the $140 one). The sample seemed to be a separate table than the normal zipcode data...
As for the homegrown vs purchased system, yes I understand the logic behind that, but people at my current and previous employers haven't always thought of soft costs when looking at the bottom line. They figure they're paying my salary already if I do this or something else soooo... Not that my time might be better served doing that something else...
But anyhows thanks for the input and the quick query.
-Luke.
January 22, 2009 at 2:32 pm
I did not provide that link to show multi county but it shows no multi state zip code very clearly.
Kind regards,
Gift Peddie
January 22, 2009 at 2:43 pm
Well, I did a bit more looking into the table, and there is a column in the database called "CountyFIPS" which is supposed to be a unique county identifier. http://en.wikipedia.org/wiki/FIPS_county_code
Looking at "CountyFIPS = 001", there quite a few zip codes in several counties in several states that have "001" as their CountyFIPS. But, each Zip Code is only in one county. Sample result below:
zipcode statecounty countyfips
04274MEANDROSCOGGIN001
04280MEANDROSCOGGIN001
04282MEANDROSCOGGIN001
04288MEANDROSCOGGIN001
04291MEANDROSCOGGIN001
05443VTADDISON 001
05456VTADDISON 001
05469VTADDISON 001
05472VTADDISON 001
Perhaps that is where the "cross county lines" comes from with the shared County FIPS number.
The query I did for your request was
select z1.zipcode, z2.state
from zips z1
inner join zips z2
on z1.zipcode = z2.zipcode
and z1.state = z2.state
and z1.county != z2.county
(and just without the county line and "state != state" to see if zips crossed state lines)
I got 0 rows for both queries.
And yes, we have just the basic zip code database, not the business edition.
-- Kit
January 22, 2009 at 3:23 pm
i've got a massive zip+4 database; I just started essentially the same query, and will post a link to the file with the results; had to do it on my dev machine, which only has a gig-o-ram, and the table is like 13 gig ; joined against it self it's 26 gig of thrashing that i gotta wait for.
Lowell
January 22, 2009 at 4:02 pm
33938 records....seems like an awful lot of zip codes cross counties.
here's a link to a tab delimited text file:
here's my source table that I used; i did a distinct from my zip9 database:
here's where the data looks wierd, for example tallahassee FL, which sits only in Leon County, appears according to the zip data to be in 3 counties:
citynamestateabbrcountyname
TALLAHASSEE FLJefferson County
TALLAHASSEE FLLeon County
TALLAHASSEE FLLiberty County
my query to get the data was:
select distinct
a.zipcode,
a.cityname,
a.stateabbr,
a.countyfips,
a.countyname,
b.zipcode As AltZipCode,
b.cityname As Altcityname,
b.stateabbr As Altstateabbr,
b.countyfips As Altcountyfips,
b.countyname AS Altcountyname
from ZipCityStateCountya
left outer join ZipCityStateCountyb
on a.stateabbr = b.stateabbr
and a.zipcode = b.zipcode
where a.countyfips != b.countyfips
Lowell
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply