How about this one...
<cffunction name="getZips" access="public">
<cfargument name="zip" type="string" default="" />
<cfargument name="zipradius" type="numeric" default="50" />
<cfquery name="qryZips" datasource="#request.dsn#">
SELECT
z.ZIP,
(3958.75 * acos(sin(z.latitude/57.2958) * sin(z1.latitude/57.2958)
+ cos(z.latitude/57.2958) * cos(z1.latitude/57.2958) * cos
(z1.longitude/57.2958 - z.longitude/57.2958)))
as Distance
FROM
zipcodedata as z
LEFT OUTER JOIN
zipcodedata as z1 ON (z1.zip = '#trim(arguments.zip)#')
WHERE
(3958.75 * acos(sin(z.latitude/57.2958) * sin(z1.latitude/57.2958)
+ cos(z.latitude/57.2958) * cos(z1.latitude/57.2958) * cos
(z1.longitude/57.2958 - z.longitude/57.2958))) <=
#val(arguments.zipRadius)#
ORDER BY
Distance
</cfquery>
<cfreturn valueList(qryZips.zip,",") />
</cffunction>
this expects latitude and longitude to be numeric data... but a few
cast as floats will fix it if they are not
r-t
On Mar 20, 2007, at 4:17 PM, Christopher Jordan wrote:
Hi folks,
I've inherited a piece of code that does a zip code proximity
search, and as it turns out: it's busted.
I've been searching the web (in general) and RIAForge and CF-Talk
(where there have been a couple discussions on this).
Unfortunately, I've not found a solution I like. I did find
something called zipfinder.cfc (which is the sort of solution I'm
looking for) that was written by Robert Capili, but he includes a
zip database that has a couple of fields that my existing zip
database does not contain - namely the rlatitude and the rlongitude
- and these are in addition to the normal latitude and longitude
fields. Without these two special fields his cfc doesn't work. :o
( I've emailed him to see if the component could be modified to
work without these fields, but I've not heard from him yet.
So I'm wonderin'... what does everyone here (whose had a need to do
so) use to do proximity searches?
I'm hoping for a CFC or something rather than a bunch of sql
statements. I suppose that if I have to I can take some of the SQL
examples that I've seen and put them into a component myself, but
I'd rather not reinvent the wheel if I don't have to.
What are everybody's thoughts on this subject?
Cheers,
Chris
_______________________________________________
Reply to DFWCFUG: [email protected]
Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/
listinfo/list
List Archives: http://www.mail-archive.com/list%
40list.dfwcfug.org/ http://www.mail-archive.com/list%
40dfwcfug.org/
DFWCFUG Sponsors: www.instantspot.com/
www.teksystems.com/
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/
www.teksystems.com/
