?

Log in

No account? Create an account
coldfusion_mx
..::.:.:. ..: .........: .:::..:::.
January 2010
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

boxing_cat [userpic]
Select objects by radius around a ZIP code centroid

Assume that:

lat and lon - latitude and longitude of a selected zip code centroid you want to apply a radius selection

zip
- name for zipcode column in Stores table
r - radus for selection in miles
latitude and longitude - latitude and longitude column names in Stores table for zip centroids

In order to select all records from the table Stores by a particular radius, first you need to obtain a longitude and latitude of the zip centroid from the zip code table and then use the following query. The solution is easy, elegant, and fast in performance.  You can expand this query with different type of joins if the table that holds an object zip code is transitional one. 

<cfquery name="getRadius" datasource="datasource">
SELECT id, street, name, town, zip, state, longitude, latitude, (3963.0*acos(sin(#lat#/57.2958) * sin(latitude/57.2958) +cos(#lat#/57.2958) * cos(latitude/57.2958) * cos(#lon#/57.2958-longitude/57.2958))) AS Distance 
FROM Stores
WHERE (3963.0*acos(sin(#lat#/57.2958) * sin(latitude/57.2958) + cos(#lat#/57.2958) * cos(latitude/57.2958) * 
cos(#lon#/57.2958-longitude/57.2958))) < #r#
ORDER BY Distance
</cfquery>