Zip Code Lookups

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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

  • 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

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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.

  • 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

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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

  • 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

  • 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.

    http://www.myzipcodemap.com

    Kind regards,
    Gift Peddie

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • I did not provide that link to show multi county but it shows no multi state zip code very clearly.

    Kind regards,
    Gift Peddie

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 33938 records....seems like an awful lot of zip codes cross counties.

    here's a link to a tab delimited text file:

    CrossCountyZipCodes.txt

    here's my source table that I used; i did a distinct from my zip9 database:

    ZipCityStateCounty.txt

    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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply