SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Los Angeles
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP/MySQL zip code proximity query

    So, I'm trying to get a zip code proximity query that will be the most efficient way to select a number of users within a certain zip code (w/ PHP/MySQL). Here's my setup:

    * Each user in the users table has a zipcode assigned to him/her.
    * I have a zipcodes table which contains columns zip code, latitude and longitude (found here for those interested.
    * The search/browse lists users that fit a certain criteria.
    * The form used passes variables like whether the users listed should be male or female, age range, etc., and two variables pertaining to zip code location: miles from the zip code and the zip code itself.
    * The function the form passes those values to returns a result set of users which fit the criteria.

    So far, I start with this query:

    Code:
    SELECT user_id, fname FROM users, zipcodes WHERE
    Then I add on whatever conditions the user chose on a case by case basis:

    Code:
    AND country_id = $cid
    
    AND name LIKE '%$name%'
    Etc. Now, for the zip code, here's the pseudo-query I constructed:

    Code:
     AND (degrees(acos(
    	   (sin(  zipcodes.latitude(user's zip)     / 57.29577951)
    	  * sin(  zipcodes.latitude($requestedzip) / 57.29577951)
    	  + cos(  zipcodes.latitude(user's zip)     / 57.29577951)
    	  * cos(  zipcodes.latitude($requestedzip) / 57.29577951)
    	  * cos( (zipcodes.longitude(userszip) - zipcodes.longitude($requestedzip) ) / 57.29577951))
    	     ))) < $milesfromzip
    As you can see, I want to only select users whose zipcode lat and long are within $milesfromzip distance of the $requestedzip. My question is, how do I resolve the zip codes? I would have to access the zip code database basically for each user (ideally, having resolved the $requestedzip's lat and long). Is this the most efficient way to do this? Any help or suggestions is strongly appreciated. Thanks very much!

    -Sam

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Los Angeles
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So here's what I have it whittled down to (in a case where I alerady have the longitude and latitude of the target zip code (47.604718, -122.335230) and miles from zip (50)):

    Code:
    SELECT user_id
    FROM users, zip_codes
    WHERE users.zip = zip_codes.zip
    AND degrees(acos(
    sin( radians(zip_codes.latitude) )
    * sin( radians(47.604718))
    + cos( radians(zip_codes.latitude))
    * cos( radians(47.604718))
    * cos( radians(zip_codes.longitude - -122.335230) ) 
    ) ) < 50
    I really thought this would do it, but it returns every user, not those within a 50 mile radius of the target zip code. Any ideas as to why this doesn't work? Or perhaps a more efficient query? Thanks in advance.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Los Angeles
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it! I forgot a part of the formula: * 69.09. Final code:

    Code:
    SELECT user_id
    FROM users, zip_codes
    WHERE users.zip = zip_codes.zip
    AND degrees(acos(
    sin( radians(zip_codes.latitude) )
    * sin( radians(47.604718))
    + cos( radians(zip_codes.latitude))
    * cos( radians(47.604718))
    * cos( radians(zip_codes.longitude - -122.335230) ) 
    ) ) * 69.09 < 50
    And then maybe a subselect query to get the long and lat of $target_zip? Again, suggestions on efficiency much appreciated. Thanks!

  4. #4
    SitePoint Zealot Dorsey's Avatar
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the details. I know this is mathematically accurate, but how would it perform searching a table of a million rows? We decided to implement an approximation using scaled integer lat and long values and the between clause. We also hard-coded miles/degree ignoring the curvature of the Earth.

  5. #5
    SitePoint Enthusiast homesonsale's Avatar
    Join Date
    Jan 2004
    Location
    Burlington, Toronto - former UK
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just wondering if anyone knows where I can get the Canadian zip codes in database form? And which I doubt is available without having to remortgage - the UK postal codes. Looking for longitude & latitude more than addresses

    Iain

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Los Angeles
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dorsey
    Thanks for the details. I know this is mathematically accurate, but how would it perform searching a table of a million rows? We decided to implement an approximation using scaled integer lat and long values and the between clause. We also hard-coded miles/degree ignoring the curvature of the Earth.
    Could you give information on how to convert to scaled integer, as well as how you coded miles/degree? That would be very helpful. Thanks!

  7. #7
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dorsey
    Thanks for the details. I know this is mathematically accurate, but how would it perform searching a table of a million rows? We decided to implement an approximation using scaled integer lat and long values and the between clause. We also hard-coded miles/degree ignoring the curvature of the Earth.
    I personally would pull out the latitudes and longitudes and perform the calculation in PHP.

    The formula that is being used calculates with the curve of the Earth factored in. (to do this it needs to be in Radians, using the arc cosine function).

  8. #8
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Any current activity on this; latitude longitude calculation

    I'm new to PHP and this forum but I have the "same" application. That is, finding records in a database representing sites w/in a specified distance of the user.
    I can get the database items' latitudes and longitude (A) from their zip code and a table of zip code L & L's , or (B) from Microsoft's Terra Server, or (C) I believe that Google now has an API that will provide it given the address information.
    Is the above info in this thread still good or is there a preferred way of doing it?
    Would having 50 tables (one for each state) which contained only the records which HAD to be w/in (say) 50 miles of that state's border (e.g. contigous states) make any sense? (Only use table for state that the user was located in.)
    TIA

  9. #9
    SitePoint Member
    Join Date
    May 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi homsonsale,
    u can get the Canadian zip codes in database from http://www.zipcodeworld.com or http://search.zipcodeworld.com

  10. #10
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    www.zip-codes.com has US for $39.

    www.zip-codes.com has US for $39.

  11. #11
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have been tryin to get Ceptor's query to work but I just cant seem to get it to.
    I have a table called phpbb_users and phpbb_zipcodes.

    Here is the code I am trying to implement. I also have taken out the variables that will be passed from the form and replaced tehm with static for testing.

    PHP Code:
    $zip_query mysql_query ("SELECT latitude, longitude FROM phpbb_zipcodes WHERE zip = 92648");
    while(
    $coords mysql_fetch_array($zip_query))
    {
    $lat $coords["latitude"];
    $long $coords["longitude"];
    }
    $results mysql_query ("SELECT username FROM phpbb_users, phpbb_zipcodes WHERE phpbb_zipcodes.zip = phpbb_users.user_zip AND degrees
        (acos(sin( radians(phpbb_zipcodes.latitude) )
        * sin( radians('" 
    $long "';))
        + cos( radians(phpbb_zipcodes.latitude))
        * cos( radians('" 
    $long "';))
        * cos( radians(phpbb_zipcodes.longitude - '" 
    $lat "';) ) )) * 69.09 < 5");
    while(
    $advisors mysql_fetch_array($results))
    {
    $ads $advisors["phpbb_users.username"];
    }
    echo 
    $ads;
    echo 
    $lat;
    echo 
    $long
    The page this is on is here: http://www.equitythinktank.com/search_results.php

    The line 17 error is my last WHILE statement.

    Any help is most appriciated!!

    Thank you,
    Rob

  12. #12
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have the basics of the distance calculation working.
    However, later, I upgraded MySQL and it won't work. I'll get back to you/all when MySQL is solved.
    (Have you tried the formula in a SQL in something like SQLYog or another MySQL front-end?)
    Bob

  13. #13
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by WebRob
    I have been tryin to get Ceptor's query to work but I just cant seem to get it to.
    I have a table called phpbb_users and phpbb_zipcodes.

    Here is the code I am trying to implement. I also have taken out the variables that will be passed from the form and replaced tehm with static for testing.

    PHP Code:
    $zip_query mysql_query ("SELECT latitude, longitude FROM phpbb_zipcodes WHERE zip = 92648");
    while(
    $coords mysql_fetch_array($zip_query))
    {
    $lat $coords["latitude"];
    $long $coords["longitude"];
    }
    $results mysql_query ("SELECT username FROM phpbb_users, phpbb_zipcodes WHERE phpbb_zipcodes.zip = phpbb_users.user_zip AND degrees
        (acos(sin( radians(phpbb_zipcodes.latitude) )
        * sin( radians('" 
    $long "';))
        + cos( radians(phpbb_zipcodes.latitude))
        * cos( radians('" 
    $long "';))
        * cos( radians(phpbb_zipcodes.longitude - '" 
    $lat "';) ) )) * 69.09 < 5");
    while(
    $advisors mysql_fetch_array($results))
    {
    $ads $advisors["phpbb_users.username"];
    }
    echo 
    $ads;
    echo 
    $lat;
    echo 
    $long
    The page this is on is here: http://www.equitythinktank.com/search_results.php

    The line 17 error is my last WHILE statement.

    Any help is most appriciated!!

    Thank you,
    Rob
    Which one is line 17? The formatting that the forum applies makes it hard to guess.

    This is the code I created to do this function, it is implemented with some XMLHttpRequest manipulation:
    PHP Code:
    <?php
    $dbHost 
    "localhost";
    $dbUser "*********";
    $dbPass "*********";
    $dbName "**********";

    mysql_connect($dbHost$dbUser$dbPass);
    mysql_select_db($dbName);

    $zip1 $_GET['zip1'];
    $zip2 $_GET['zip2'];
    //$selectZip1 = "SELECT latitude, longitude FROM zipcodes WHERE zip = $zip1";
    //$selectZip2 = "SELECT latitude, longitude FROM zipcodes WHERE zip = $zip2";

    $selectCoords "SELECT latitude, longitude FROM zipcodes WHERE zip = $zip1 OR zip = $zip2";


    $coordsRS mysql_query($selectCoords);
    $i 1;
    while(
    $aCoords mysql_fetch_array($coordsRS))
    {
        
    $lat[$i] = $aCoords['latitude'];
        
    $long[$i] = $aCoords['longitude'];
        
    $i++;
    }
        


    //$zip2RS = mysql_fetch_array($zip2RS);
    //$lat2 = $zip2RS['latitude'];
    //$long2 = $zip2RS['longitude'];

    $distance 3963.0 * (acos(sin($lat['1']/57.2958) * sin($lat['2']/57.2958) + cos($lat['1']/57.2958) * cos($lat['2']/57.2958) * cos($long['2']/57.2958 $long['1']/57.2958)));
    $distance round($distance,2);
    $return_value '<?xml version="1.0" standalone="yes"?><distance><author>Stephan Segraves</author><locations><location1><zip>'.$zip1.'</zip><latitude>'.$lat[1].'</latitude><longitude>'.$long[1].'</longitude></location1><location2><zip>'.$zip2.'</zip><latitude>'.$lat[2].'</latitude><longitude>'.$long[2].'</longitude></location2></locations><miles>'.$distance.'</miles></distance>';
    header('Content-Type:text/xml');
    echo 
    $return_value;

    ?>

  14. #14
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bbenjamin
    I have the basics of the distance calculation working.
    However, later, I upgraded MySQL and it won't work. I'll get back to you/all when MySQL is solved.
    (Have you tried the formula in a SQL in something like SQLYog or another MySQL front-end?)
    Bob
    Awesome Benjamin, that helps alot.

    I actually have been replacing the variables with constants and dropping the query into phpmyAdmin and I get no results. I think it is something in my PHP code.


    Quote Originally Posted by Hartmann
    Which one is line 17? The formatting that the forum applies makes it hard to guess.

    This is the code I created to do this function, it is implemented with some XMLHttpRequest manipulation:
    PHP Code:
    <?php
    $dbHost 
    "localhost";
    $dbUser "*********";
    $dbPass "*********";
    $dbName "**********";

    mysql_connect($dbHost$dbUser$dbPass);
    mysql_select_db($dbName);

    $zip1 $_GET['zip1'];
    $zip2 $_GET['zip2'];
    //$selectZip1 = "SELECT latitude, longitude FROM zipcodes WHERE zip = $zip1";
    //$selectZip2 = "SELECT latitude, longitude FROM zipcodes WHERE zip = $zip2";

    $selectCoords "SELECT latitude, longitude FROM zipcodes WHERE zip = $zip1 OR zip = $zip2";


    $coordsRS mysql_query($selectCoords);
    $i 1;
    while(
    $aCoords mysql_fetch_array($coordsRS))
    {
        
    $lat[$i] = $aCoords['latitude'];
        
    $long[$i] = $aCoords['longitude'];
        
    $i++;
    }
        


    //$zip2RS = mysql_fetch_array($zip2RS);
    //$lat2 = $zip2RS['latitude'];
    //$long2 = $zip2RS['longitude'];

    $distance 3963.0 * (acos(sin($lat['1']/57.2958) * sin($lat['2']/57.2958) + cos($lat['1']/57.2958) * cos($lat['2']/57.2958) * cos($long['2']/57.2958 $long['1']/57.2958)));
    $distance round($distance,2);
    $return_value '<?xml version="1.0" standalone="yes"?><distance><author>Stephan Segraves</author><locations><location1><zip>'.$zip1.'</zip><latitude>'.$lat[1].'</latitude><longitude>'.$long[1].'</longitude></location1><location2><zip>'.$zip2.'</zip><latitude>'.$lat[2].'</latitude><longitude>'.$long[2].'</longitude></location2></locations><miles>'.$distance.'</miles></distance>';
    header('Content-Type:text/xml');
    echo 
    $return_value;

    ?>
    Thanks to you too Hartman! The only thing is that I am querying for results within a radius of a certain ZIP code, essentially a store locator.

    I tried using Ceptor's formula, but grabbing the variables "ZIP" and what will become the radius "5" "20" "50" "100" etc.. and using them.

    If you notice the first query, I am looking up the latitude and longitude of the ZIP. then taking those coordinates and i need to find all people that are whatever radius away.

    When looking at the query, the "92648" is the ZIP that will be a variable (but for explainations sake I have made a constant) and the last part of teh second query (where it says < 5) I want the results within 5 miles of 92648.

    I hope this helps. Thank you again for your help so far!!!

    Rob

  15. #15
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    latitude longitude distance sequel sql

    I'll post it tomorrow.
    ---
    select id, city,state,3963 *
    acos(
    cos(radians(90-latitude ))
    *cos(radians(90-34.200001))
    +
    sin(radians(90-latitude ))
    * sin(radians(90-34.200001))
    *cos(radians(longitude- -118.370003))
    )as dist

    from zipcodes having dist < 100000 order by dist ;
    ---
    The 3963 is for statute miles.
    The 34.200001 and -118.370003 are the lat/long for the Burbank, CA airport
    Note the double minus (-) as we are subtracting a "negative" longitude.
    I checked it against a website with airmiles between airports and it was within 9 miles out of 2460.
    The above was against my db of zipcodes (72,000) with id, city, state, latitude, longitude. You could also limit it more by distance and also by "LIMIT 20" or whatever.

    Bob
    Last edited by bbenjamin; Jun 2, 2006 at 09:38. Reason: Update

  16. #16
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bbenjamin
    I'll post it tomorrow.
    ---
    select id, city,state,3963 *
    acos(
    cos(radians(90-latitude ))
    *cos(radians(90-34.200001))
    +
    sin(radians(90-latitude ))
    * sin(radians(90-34.200001))
    *cos(radians(longitude- -118.370003))
    )as dist

    from zipcodes having dist < 100000 order by dist ;
    ---
    The 3963 is for statute miles.
    The 34.200001 and -118.370003 are the lat/long for the Burbank, CA airport
    Note the double minus (-) as we are subtracting a "negative" longitude.
    I checked it against a website with airmiles between airports and it was within 9 miles out of 2460.
    The above was against my db of zipcodes (72,000) with id, city, state, latitude, longitude. You could also limit it more by distance and also by "LIMIT 20" or whatever.

    Bob
    Thanks again....now how do I get rid of my error I am experiencing in my WHILE statement??

  17. #17
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Huntington Beach, CA
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This worked great...thank you guys for all of your help!!

  18. #18
    SitePoint Member
    Join Date
    Jun 2006
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    select Members.Name, Members.Zip, zipcodes.id, zipcodes.city,zipcodes.state,3963 *
    acos(
    cos(radians(90-latitude ))
    *cos(radians(90-34.200001))
    +
    sin(radians(90-latitude ))
    * sin(radians(90-34.200001))
    *cos(radians(longitude- -118.370003))
    )as dist
    from zipcodes, Members having dist < 10000 AND Members.Zip = zipcodes.zip

    Why is this code not working? I know it is an error with my added code for pulling data from another table. Thanks in advance.

  19. #19
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are your table structures?
    How will the sql find the latitude and longitude?
    Have it work with one table with the latitudes and longitudes first and then add you joins.

  20. #20
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    CREATE TABLE zip_codes
    (zip varchar (16unsigned primary key,
    city varchar (30),
    state varchar (30),
    latitude decimal(10,6),
    longitude decimal(10,6),
    timezone tinyint(2),
    dst tinyint(1),
    country char (2)
    ); 
    Excuse my syntax it probably is not correct. But it should give you the idea.

  21. #21
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Free Database

    http://www.cfdynamics.com/cfdynamics/zipbase/index.cfm

    I got mine else where for free but I don't remember where.

  22. #22
    SitePoint Member
    Join Date
    Jul 2006
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This solution to the ZIP proximity question was posted some time ago, and the code works for me, but I am a little confused about the units. What are the units for distance? It seems unlikely that you would search for something 100000 miles away.

    I am coming up with distances of like 6671.778364 for locations that are a few miles apart.

    Thanks.

    Quote Originally Posted by bbenjamin
    I'll post it tomorrow.
    ---
    select id, city,state,3963 *
    acos(
    cos(radians(90-latitude ))
    *cos(radians(90-34.200001))
    +
    sin(radians(90-latitude ))
    * sin(radians(90-34.200001))
    *cos(radians(longitude- -118.370003))
    )as dist

    from zipcodes having dist < 100000 order by dist ;
    ---
    The 3963 is for statute miles.
    The 34.200001 and -118.370003 are the lat/long for the Burbank, CA airport
    Note the double minus (-) as we are subtracting a "negative" longitude.
    I checked it against a website with airmiles between airports and it was within 9 miles out of 2460.
    The above was against my db of zipcodes (72,000) with id, city, state, latitude, longitude. You could also limit it more by distance and also by "LIMIT 20" or whatever.

    Bob

  23. #23
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    check your code.

    I don't know what you mean by "the code works for me" when combined with your other statement.
    A. The 3963 multiplier in the formula is what to use if you are interested in statute miles (as opposed to a different number for kilometers).
    B. I 'hard coded' the 34.200001 and -118.370003 , the lat/long for the Burbank, CA airport, as I had it handy and knew what the mileages should be for surrounding areas. These numbers should be substituted with the latitude and longitude for your particular 'from' or source location.
    C. I was testing various distances and just stuck in the 10000000 number to include anything in my database. I could have eliminated the 'having' clause for that test but I was lazy. the units for distance are statute miles.

    Get back to me by PM if this doesn't answer your question.

  24. #24
    SitePoint Member
    Join Date
    Jul 2006
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the clarification. I got it figured out.

    I too was using the hard-coded lat/long for the Burbank A/P since I am familiar with its location. I thought I could test my values with your defaults, but something was goofed up on my end. I don't know what it was. I changed the values to my own lat/long, and somehow that magically fixed the problem.

    Thanks again for you response and your code. It was a life-saver!!!

  25. #25
    SitePoint Addict jamus's Avatar
    Join Date
    Jul 2004
    Location
    Devon, UK
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could someone provide a complete example? Im getting a bit lost in this thread.


Bookmarks

Posting Permissions

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