>> SELECT zip FROM zipcodes WHERE
>> degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
>> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude))))*60*1.1515
>> < 5
>
> Ouch. You might want to calculate the rectange enclosing your target
> distance, add an index on lat (and/or long), and add the rectangle check to
> your where clause: WHERE latitude BETWEEN lt1 AND lt2 AND longitude BETWEEN
> ln2 AND ln2. That way mysql can use the index to pare down most of the rows
> without having to call all those trig functions for every zipcode.
I like this idea the best (it always bothered me running a query that
involved multiple mathmatical functions).
So... Here's the "scratch" php code I ended up with... Anyone see any
problems with it? The only problem I see is that I think the old code
was more "circular" this will be a square (within the limits of a
square on a non-spherical earth... etc.. etc..) ... so there will be
more zip codes included in the corners. If there are too many
complaints about that I might look at some sort of overlapping
rectangle scheme instead of a square.
function ChangeInLatitude($Miles) { return rad2deg($Miles/3960); }
function ChangeInLongitude($Lat, $Miles) { return
rad2deg($Miles/3960*cos(deg2rad($Lat))); }
$Miles = 5;
$OriginalLat = 39.0788994;
$OriginalLon = -77.1227036;
$ChangeInLat = ChangeInLatitude($Miles);
$ChangeInLon = ChangeInLongitude($OriginalLat, $Miles);
$MinLat = $OriginalLat-$ChangeInLat;
$MaxLat = $OriginalLat+$ChangeInLat;
$MinLon = $OriginalLon-$ChangeInLon;
$MaxLon = $OriginalLon+$ChangeInLon;
My only other question is... when I explained the new query... On the
dependent subquery it says possible keys are zip, longitude and
latitude but it used zip. It seems like a better index would be
longitude or latitude? On the primary query, even though there is an
index on custzip it doesn't say it's using ANY indexes. I should
probably leave well enough alone... but I'm curious.
Thanks again!
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
