mysql closest number

Discussion in 'OT Technology' started by Slid., Aug 2, 2004.

  1. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    Okay, so I'm writing a script that is making use of zipcodes. Basically somebody gives me a zipcode and I need to find the closest location to them - that's easy.

    Thing is that my <distance script> puts quite a load on my server so I figure I'll take a zipcode, and then scan for zipcodes within 2000 of it. (03835+1000, 03835-1000) then run a query on the ~5 closest zipcodes I can find.

    Problem is mysql doesn't seem to have a "closest" formula - that I know of. I am basically making two calls to the database, one for +1000 and one for -1000.

    I am using PHP to develop. If noone has any ideas my best so far is to toss the two results into two arrays then run a loop which steps from top to bottom and comparing the difference between the zipcodes and choosing the closest one, dropping it, then moving onto the next. :bowdown:

    Thanks!
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Hmmm - I did something very similar to this but I was only picking between one of two Postal Codes. There might be an easier way around this. First, are zip codes that are close to each other numerically, close to each other geographically? Second, how are you calculating the distance between two known zip codes?

    To answer the question of "closest", if you create a table with a number of distances, you can run a query that will tell you the minumum value of the distance field. You then take that value to find out the corresponding zip code (two step process - find the lowest value, then find the corresponding zip code).
     
  3. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    That is actually a great idea - I should be able to make a virtual table and select from that. Great!

    I am calculating distance using a script I made to query mapquest - it works great but I try not to abuse it in fear that they will shut me down! :big grin:

    The zipcodes will be across the US so theres no way to really crack down on them. I do only want within 50 miles most of the time and I'd say 9 times out of 10 a zipcode with a difference of over 1000 will be over 50 miles.

    Your idea should work great though, I'll give it a whirl.

    Thanks.
     
  4. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    Code:
    SELECT testzipcode, ABS(testzipcode-03261)
    FROM testbase
    GROUP BY testzipcode
    ORDER BY `ABS( testzipcode -03261  )` ASC
    LIMIT 5
    
    mm - without a temporary table and works great - thanks again. :big grin:
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I use mapquest to for a similar function (I calculate mileage from location to location using postal codes). :big grin:. I used wget and dos commands to retrieve the mileage.
     
  6. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    I myself just use fopen then eregi to search for the distance line - I made a class to return either distance or a directions link cause I use it so often, lol.

    eregi ("Distance:$nbsp;</font>(.*)</td>", $line, $out)

    $out now contains the magic number :big grin:
     
  7. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    Last edited: Aug 3, 2004
  8. bleak

    bleak Guest

    Code:
     SELECT * from ZIPCODE WHERE zipcode BETWEEN '03835-1000' AND '03835+1000';
     
    That seems like an awful lot of returns for a "closest distance" script, though. Maybe I misunderstand the question.
     
  9. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    Thing is - I won't have every one of those zip codes filled in - I might have 3-4 within the range of 1000 of each other. It is a script that is pulling from 300 car dealers in the US.

    Either way, my new version works by getting the 5 closest rather than doing a range.
     

Share This Page