Database & Application Miscellaneous: Zipcode distance problem

• gladiator2043 / 216 / Sat, 30 Jan 2010 14:55:00 GMT / Comments (10)
• I am developing an app that gets a zipcode from a user and then uses that zipcode to find the nearest store locations in miles based on the stores zipcode.
  
  I have a database that has all the zipcodes with there corresponding latitudes and longitudes.
  I also have a calculation that computes distance between the zipcodes.
  
  The problem I am facing is that I need the result to be sorted by distance. The issue at hand is that distance is calculated on the fly and how do I sort something that is computed on the fly by a PHp function.
  
  Any help will be highly appreciated.
• Keywords:

  zipcode, distance, database, application

• http://database.itags.org/database-application/158222/«« Last Thread - Next Thread »»

• 1. i assume your are doing your distance calc in sort sort of a loop. if so, in each iterartion add the distance to an array and then sort the array.

     devinemke | Fri, 16 Nov 2007 01:52:00 GMT |

  2. When you say the results are caculated "on the fly" do you mean in the select list on the database or in php code?
     
     If you're doing this in the SQL select list, then something like:
     
     select distance(area1,area2) as distance order by distance
     
     If your distance calculation is done in PHP, look at moving it to being in the SQL statement.

     sxooter | Fri, 16 Nov 2007 01:53:00 GMT |

  3. How do I move my code to SQL ?
     
     The function that I am using to calculate distance is is :
     
     function distance($lat1, $lon1, $lat2, $lon2, $unit) {
     
     $theta = $lon1 - $lon2;
     $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
     $dist = acos($dist);
     $dist = rad2deg($dist);
     $miles = $dist * 60 * 1.1515;
     $unit = strtoupper($unit);
     
     if ($unit == "K") {
     return ($miles * 1.609344);
     } else if ($unit == "N") {
     return ($miles * 0.8684);
     } else {
     return $miles;
     }
     }
     
     I get the zipcode from the user and i compare it to a row of data that contains the store zipcode and I want a list of ordered stores calculated on the fly. I do like your Select statement.

     gladiator2043 | Fri, 16 Nov 2007 01:54:00 GMT |

  4. Both PostgreSQL and MySQL appear to support cos/sin et. al. they also appear to be working in RADs, not degrees.
     
     So, convert the numbers in your database from degrees to rad in PHP, then feed it to your database kinda like this:
     
     select (sin(lat1)*sin(lat2)+cos(lat1)*cos(theta)) as distance from table <where clause> order by distance;
     
     And create a where clause that will pick the right row (I'm assuming you've already got a where clause there somewhere in your own code.)

     sxooter | Fri, 16 Nov 2007 01:55:00 GMT |

  5. This (http://www.rsbauer.com/webdev.php) (at the very bottom) might help explain querying distance. There's two approaches described. The one you'd probably be interested in is the one with all the math being done in the SQL. The other method uses a user function which makes the query go a bit quicker and tends to be easier to use and read. But adding a user function to your database may not be an option.
     
     You can also play with the data (http://www.rsbauer.com/zip/). If you do a zip code search with radius query (the top most search option) and check off "Show Query," it'll show the query at the very bottom of the page. By default, it uses the user function approach. Click on "Use SQL Calculations" and it'll use SQL math to calculate the distance.
     
     You'll find the results are ordered with the shortest distance zip at the top. If you do the "show query," you'll see how this works.

     astroteg | Fri, 16 Nov 2007 01:56:00 GMT |

  6. Almost there.
     
     Ok I have two databases.
     
     database 1 has a stores table.
     
     store name store address store zip
     
     database 2 has a zipcode lookup table
     
     zipcode latitude longtiude
     
     user comes to a page and has to enter a zipcode to find nearest stores.
     The next page should come up with a list of all the stores in database 1 that are arranged from nearest to farthest store distance. any insights.
     
     I have looked at a lot of code but am still a tad bit confused. thank you.

     gladiator2043 | Fri, 16 Nov 2007 01:57:00 GMT |

  7. Where are you stuck at?
     
     You know you'll need the lat/long of the user's zip code. Then you'll need to get the lat/long of the zips your stores have. As a side note, you might add a lat/long value to each store to reduce query times (but remember to update it if the zip changes). Otherwise, you'll have to query the stores and join the stores' zip codes with the zip codes in your other database to get the lat/long values. Once you do this, you'll have the user's lat/long and the stores lat/long, you can now do some math on it and determine the distance.

     astroteg | Fri, 16 Nov 2007 01:58:00 GMT |

  8. My only recommendation would be to put all these data into one database so you CAN join it all together quickly and easily.

     sxooter | Fri, 16 Nov 2007 01:59:00 GMT |

  9. Ok thank you both for your valuable advice. I will see what I can do. And if i get stuck I will post some code...thank you again

     gladiator2043 | Fri, 16 Nov 2007 02:00:00 GMT |

  10. I'd sure like to see this in action...
      
      I've been trying to find something in php that is like this:
      http://bullseye.electricvine.com/demo.asp

      kidgeek_dfw | Fri, 16 Nov 2007 02:01:00 GMT |