Calculation distances
Hello, we are working on a small project which needs to calculate the closest distances from an address and/or zipcode entered into a search box. It will read from a MySQL database of companies, which store their address and zipcodes. looking for the best way to approach this. I've seen some zipcode Perl modules on Cpan, but nothing for helping calculation distances. Can someone point me in the right direction to accomplish this ... thx's :) TIA, Mike(mickalo)Blezien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculation distances
[snip]
we are working on a small project which needs to calculate the closest
distances from an address and/or zipcode entered into a search box. It
will read from a MySQL database of companies, which store their address
and zipcodes.
looking for the best way to approach this. I've seen some zipcode Perl
modules on Cpan, but nothing for helping calculation distances. Can
someone point me in the right direction to accomplish this ... thx's :)
[/snip]
We have started storing latitude and longitude coordinates on our
databases which lends itself to distance calculations. For example, here
is a PHP function which performs the distance calculation
function distance($lat1, $lon1, $lat2, $lon2) {
$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;
return $miles;
}
You may be able to do it entirely in a query by utilizing MySQL's math
functions;
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculation distances
Mike Blezien wrote: Hello, we are working on a small project which needs to calculate the closest distances from an address and/or zipcode entered into a search box. It will read from a MySQL database of companies, which store their address and zipcodes. looking for the best way to approach this. I've seen some zipcode Perl modules on Cpan, but nothing for helping calculation distances. Can someone point me in the right direction to accomplish this ... thx's :) The zip code tables usually give a lat long location somewhere near the center of that zip code (which can be HUGE in rural areas) You can then use some math that with give you the distance between the coordinates of 2 zip codes. Here is a web site I found for help when doing similar calculations. Remember that this type of math usually wants angle measurements in radians, not degrees. http://williams.best.vwh.net/avform.htm -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculation distances
Chris, this is something very similar to what we are attempting to accomplish: http://www.papajohnsonline.com/restlocator/RestaurantLocator Mike - Original Message - From: Chris W [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED]; MYSQL General List mysql@lists.mysql.com Sent: Sunday, August 20, 2006 8:59 PM Subject: Re: Calculation distances Mike Blezien wrote: Hello, we are working on a small project which needs to calculate the closest distances from an address and/or zipcode entered into a search box. It will read from a MySQL database of companies, which store their address and zipcodes. looking for the best way to approach this. I've seen some zipcode Perl modules on Cpan, but nothing for helping calculation distances. Can someone point me in the right direction to accomplish this ... thx's :) The zip code tables usually give a lat long location somewhere near the center of that zip code (which can be HUGE in rural areas) You can then use some math that with give you the distance between the coordinates of 2 zip codes. Here is a web site I found for help when doing similar calculations. Remember that this type of math usually wants angle measurements in radians, not degrees. http://williams.best.vwh.net/avform.htm -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculation distances
Mike Blezien wrote: Chris, this is something very similar to what we are attempting to accomplish: http://www.papajohnsonline.com/restlocator/RestaurantLocator The math in the link I gave you will still work for the distance calculations, however if you want what that site does you will need a GIS database that can give you lat long of an address. I am about 90% sure something like that is going to cost you a lot of money and cost a lot to keep it up to date. I'm afraid I don't know where to look for a vendor though. I would search for GIS software or maybe contact some of the mapping people like Google maps or Street Atlas USA, if they don't have a product they can sell you to provide lat long of street addresses, maybe they can tell you who provides them with data and contact them. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
