Nearest Zip code

Results 1 to 3 of 3

Thread: Nearest Zip code

  1. #1
    Join Date
    Dec 1969

    Default Nearest Zip code

    Hi, <BR><BR>I am trying to retrieve records from my Access database. <BR>I want to retrieve those records whose zip = zipcode (from a form) <BR>or if that perticular zipcode is not present than find for a nearest zip code to display the record. <BR><BR>select * from table where country = &#039;" + cstr(trim(Request("country"))) + "&#039; and zip=&#039;" + cstr(trim(Request("zipcode"))) + "&#039; or zip= "nearest zipcode query" <BR><BR>Can anybody help me out. <BR><BR>r_patel <BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Nearest Zip code

    I don&#039;t think you can find the nearest code from a query.<BR>You could order the recordset, find the first code greater than the target zip code, save it and then move one record back. If the current record (the one you just moved back onto) is equal to the target zip code you have a match, other wise you have the zip code before and (previously saved) after.<BR><BR>If you are looking for the next locality you had check that they use incrementing zip codes. Post codes here don&#039;t always work that way.<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default google for it...

    You need a database of zip codes that has the latitude and longitude of each zip. Then you do a distance calculation to get the nearest one.<BR><BR>Something like:<BR><BR>&#060;%<BR>SQL = "SELECT lat, lon FROM zips WHERE zipcode = &#039;" & Request("zipcode") & "&#039;"<BR>Set RS = conn.Execute(SQL)<BR>lat = RS(0)<BR>lon = RS(1)<BR>RS.Close<BR><BR>SQL = "SELECT SQR( (lat-" & lat & ")^2 + (lon-" & lon & ")^2 ) AS distance, zip " _<BR> & " FROM zips ORDER BY distance"<BR>Set RS = Conn.Execute( SQL )<BR>... and then you can display distance to each zip ...<BR><BR>%&#062;<BR><BR>You have to convert the lat/long from degrees or radians or whatever they use in the DB into miles/kms. Personally, I&#039;d just do that *IN* the DB, ahead of time.<BR><BR>Just doing SQR( latDiff^2 + longDiff^2 ) isn&#039;t quite accurate, becuz of curvature of Earth, but it&#039;s close enough for practical purposes, and the real formula would slow your query to a crawl.<BR><BR>There are many ways to improve the performance of that query, but go find your zipcodes database and get started working on this first.<BR><BR>

Posting Permissions

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