NYCPHP Meetup

[Paul Houle]

    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.