select blah
from blah
where sqrt(blah)<=radius
try this:
select blah
from blah
where blah<=(radius^2)
In game programming this is a pretty sweet trick to squeeze some more efficiency out of distance calculations because squaring a number is far faster than square rooting a number, but depending on how many locations you have etc, there might not be a noticeable difference, but then again there might be! (:
On 4/27/05, Matt Muro <[EMAIL PROTECTED]> wrote:
Stefan,
I use the following MySQL Query for a PHP site I run (plug:
www.lifeknot.com :) to determine all results within N miles of the zip
code the user has entered.
And, yes, you will need to procure a zip code database. I got mine from
ZipCodeWorld.
http://www.zipcodeworld.com/
FIRST I get the latitude and longitude for the zip code they entered
// get city, state, latitude and longitude of zipcode entered
$zipQuery = " SELECT city,
state,
latitude,
longitude
FROM zipcodes
WHERE zipcode = '" . $_POST['zipcode'] . "'";
$zipResult = mysql_query ($zipQuery) or trigger_error("SQL ERROR ....",
ERROR);
$zipEntered = mysql_fetch_assoc($zipResult);
DO THE MATH
to find all entries in the database where (in my case user's zipcode where
they live) is within N miles of the zipcode user performing search has
entered.
$SQL= "Select username, age, blah, blah, blah
FROM a bunch of tables
WHERE a bunch of joins
and SUBSTRING(user_addresses.zip,1,5) = zipcodes.zipcode
and sqrt(power(69.1*(zipcodes.latitude - " . $zipEntered['latitude'] .
"),2) +
power(69.1* (zipcodes.longitude-" . $zipEntered['longitude'] . ") *
cos(zipcodes.latitude/57.3),2)) < " . $_POST['within'] ";
There may be a better way to do this, but I do recall scouring the web
when setting this up two years back and coming to find this method
suggested over and over again.
- Matt "another shamless plug below" Muro
___________________________________________________
Share your interests and passions in any activity imaginable
http://www.lifeknot.com
--------------------------------------------------
Hi All,
Does anyone know how to do a zip code distance search in a
database? For instance, a customer enters their zip code, and
you return the closest stores within a 20 mile radius. I assume
that some kind of zip code table has to be procured as well.
Does anyone know where to get this?
Thanks,
Stefan
________________________________________________________
Matt Muro [EMAIL PROTECTED]
Harvard University phone: 617.998.8522
Division of Continuing Education fax: 617.495.9176
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
