Thanks Ronald. That would probably work.
I think I'm going to use zipfinder.cfc though. I've added the two
columns to by zip code table that represent each longitute/latitude pair
in radians.
The only reason I'm going to use this one, is because it offers
different functions that allow me to balance speed with accuracy. I'll
probably end up using the Haversine formula (which could be what you've
given me here... I don't know).
Cheers,
Chris :o)
Ronald-Thomas Krempetz wrote:
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] <mailto:[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/
_______________________________________________
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/
