Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2006
    Posts
    56

    Unanswered: Tables to cross-reference state-city-postal codes?

    I want to be able to match state-city-postal codes. So if a user enters a city\state, I can give them the correct zip codes for that area. Or the correct list of cities based on a zip.

    I would also like to do radii matches. So if someone in a city-state-zip wants a list of others within a certain radius, I can give them a matching list.

    Are there any ready-made schemas to do this?

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by mburke
    I want to be able to match state-city-postal codes. So if a user enters a city\state, I can give them the correct zip codes for that area. Or the correct list of cities based on a zip.

    I would also like to do radii matches. So if someone in a city-state-zip wants a list of others within a certain radius, I can give them a matching list.

    Are there any ready-made schemas to do this?
    You don't state what type of database you are using, but PostgreSQL has some nice spatial data types and operators pre-defined.

    As far as pre-defined schemas, I'm not aware of any. You'll just need to "roll your own"

    I have found downloadable csv files with city, state, zip5, latitude and longitude fields online.

    If you're ising postgresql, the spatial operators include one which returns a boolean value if a point lies within a circle or other shape. Since, if you have the lat/long of a zip, you could easily create a query which returns all zip code data where the latitude and longitude fall within a certain radius of the target zip's lat/long...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by mburke
    I want to be able to match state-city-postal codes. So if a user enters a city\state, I can give them the correct zip codes for that area. Or the correct list of cities based on a zip.

    I would also like to do radii matches. So if someone in a city-state-zip wants a list of others within a certain radius, I can give them a matching list.

    Are there any ready-made schemas to do this?
    You don't state what type of database you are using, but PostgreSQL has some nice spatial ddata types pre-defined.

    As far as pre-defined schemas, I'm not aware of any. You'll just need to "roll your own"

    I have found downloadable csv files with city, state, zip5, latitude and longitude fields online.

    If you're using postgresql, the spatial operators include one which returns a boolean value if a point lies within a circle or other shape. Since, if you have the lat/long of a zip, you could easily create a query which returns all zip code data where the latitude and longitude fall within a certain radius of the target zip's lat/long...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Aug 2006
    Posts
    56
    Well, I have not chosen a database yet. There are two parts to it: One is the data that drives the web site itself (a dating service), and the other using a radius value to pinpoint matches.

    If you have a suggestion on how to best handle this, please let me know.

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    assume that you have latitude and longitude of user zip code.

    although you COULD get into spherical trigonometry, it's easier to assume parallel grids. (this has the effect of all "circles" become elipses, and the further away from the equator, the more pronounced the ellipse. Oh Well - it still works acceptably for many purposes)

    At the equator, each degree of latitude/longitude represents approximately 70 miles (25000/360=69.44)

    again, assuming planar trig, x^2 + y^2 = z^2, and z=sqrt(x^2 + y^2)

    In our case, x is the difference in latitude between zip1 and zip2, and y is the delta longitude between zip1 and zip2.

    With your user1 zip, extract the lat & long, then write a sql statement that includes these factors. Something along the lines of

    Code:
    Select ZipCode, sqrt((lat1-lattitude)^2 + (long1-Longitude)^2) as Distance  From ZipCodes 
    Where sqrt((lat1-lattitude)^2 + (long1-Longitude)^2) <= 20/69.44
    (assuming you want to list all zip codes within a 20 mile radius)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Aug 2006
    Posts
    56
    Ok, that makes sense. Is there a table out there that will give me the lat-long associated with postal codes for US and Canada?

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I've seen a couple of text files that you could import. They're based on 1999 US data though. Although most zip codes would be included, there may be a few that aren't.

    Here's a site where you can download the csv file.

    http://red.phoxes.com/red/Downloads/...3/Default.aspx

    I haven't even looked for canadian postal codes.
    Last edited by loquin; 03-08-07 at 16:44.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    BTW. You could come up with a decent approximation of distance, even at latitudes approaching the artic circle, by dividing the longitude delta by the cosine of the latitude, I believe.

    In other words, I'm in the Phoenix, AZ area, at about 33 degrees north.

    Instead of looking for anyone in a radius of x degrees east/west/north/south, "stretch" the east/west delta a bit (the cosine of 33 degrees is .839)

    Try this

    Code:
    Select ZipCode, sqrt((lat1-lattitude)^2 + ((long1-Longitude)/cos(lat1))^2) as Distance  From ZipCodes 
    Where sqrt((lat1-lattitude)^2 + ((long1-Longitude)/cos(lat1))^2) <= 20/69.44
    The extra trig functions may make the query too slow, though. Also note that you WILL get an error at the poles... (divide by zero)
    Last edited by loquin; 03-08-07 at 16:41.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    canadian codes

    Dumped from an existing db. It would be dead easy to use this script with any database server.
    Last edited by loquin; 03-08-07 at 17:17.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Keep in mind. These could get you started, but, since you're developing this for a business, you really should get a subscription, where you can download updated data every month - it's very reasonable. (both countries, on the order of a $100 a year, total.)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  11. #11
    Join Date
    Aug 2006
    Posts
    56
    This is a huge help, I really appreciate your assitance. A am finding that a dating site is a complex undertaking. It requires much interaction between the system and user. I have already written a mountain of code, and I have not even gotten to the database layer yet.

  12. #12
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by mburke
    ...I have already written a mountain of code, and I have not even gotten to the database layer yet.
    Then, unfortunately, this probably means that a large part of your code will need to be scrapped or re-written.

    You really need to nail down the software design, INCLUDING the database design, before a single line of code gets written.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  13. #13
    Join Date
    Aug 2006
    Posts
    56
    I do have a software design. I also have a data dictionary and schema. I just did not have the spatial database part nailed down. I am still working on the View navigation and Controller code. I have not gotten to the Persistence layer. But I need to update the schema first with the info you gave me before I start the ORM process.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •