NYCPHP Meetup

[nycphp-talk] Radial Zip Code Search

Paul Houle paul at devonianfarm.com
Sun Jan 21 02:24:40 EST 2007


    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.




More information about the talk mailing list