The following query, from Donald J Organ IV, is an elegant
answer, but it involves a full table scan:
SELECT distinct zipcode,
ROUND((ACOS((SIN($lat/57.2958) * SIN(latitude/57.2958)) +
(COS($lat/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - $long/57.2958)))) * 3963, 3) AS distance
FROM usa
WHERE (latitude >= $lat - ($miles/111))
AND (latitude <= $lat + ($miles/111))
AND (longitude >= $long - ($miles/111))
AND (longitude <= $long + ($miles/111))
ORDER BY distance
Basically, mysql has to go through every zipcode in the database (a
few thousand), do a mathematical calculation, sort the results, then
pick out the top few. You ought to benchmark this. Since mysql is the
fastest database in the west, you might find that the performance is
acceptable -- but it won't scale well when the number of points in the
database gets larger.
Mysql 5 has spatial extensions that create special index structures
to do this kind of query:
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
If you're running a recent version of mysql, you may find that you
can get better performance this way.
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
