Russ, I probably should, and maybe sometime I will (especially since you've provided the code. thanks!). But my client isn't paying me to do that right now, and the queries I posted earlier are working for them. I'll grant that I don't like having queries in-line with my display code (as those two queries are), but I didn't write that page, and as of yet have not re-written it to be better and more maintainable. I'll get to it. :o)
Thanks again Russ! Chris Russ wrote: > Christopher, I highly recommend you put in some kind of function to > calculate the distance into SQL serer. > > CREATE FUNCTION [dbo].[getDistance] > ( > @lat1 numeric(9,6), > @lon1 numeric(9,6), > @lat2 numeric(9,6), > @lon2 numeric(9,6) > ) > RETURNS NUMERIC( 10, 5 ) > AS > BEGIN > DECLARE @x decimal(20,10) > DECLARE @pi decimal(21,20) > SET @pi = 3.14159265358979323846 > SET @x = sin( @lat1 * @pi/180 ) * sin( @lat2 * @pi/180 ) + cos( > @lat1 [EMAIL PROTECTED]/180 ) * cos( @lat2 * @pi/180 ) * cos( abs( (@lon2 * > @pi/180) - > (@lon1 [EMAIL PROTECTED]/180) ) ) > SET @x = atan( ( sqrt( 1- power( @x, 2 ) ) ) / @x ) > RETURN ( 1.852 * 60.0 * ((@x/@pi)*180) ) / 1.609344 > END > > > > Then your can fairly easily write a query that brings zip codes within a > certain distance. > > Russ > > >> -----Original Message----- >> From: Christopher Jordan [mailto:[EMAIL PROTECTED] >> Sent: Monday, February 05, 2007 4:08 PM >> To: CF-Talk >> Subject: Re: Postal Code database and proximity >> >> Rick, >> >> I bought a zip code table from some company for $39 bucks or there >> abouts. The following queries then get run against the table: >> <cfquery name="zipcode" datasource="MyDSN"> >> SELECT DISTINCT latitude,longitude >> FROM AmericanZipCodes >> WHERE zip = '#form.zipcode#' >> </cfquery> >> >> >> <cfquery name="ZipCode" datasource="MyDSN"> >> SELECT zip,ROUND((ACOS((SIN(#latitude#/57.2958) * >> SIN(latitude/57.2958)) + (COS(#latitude#/57.2958) * >> COS(latitude/57.2958) * COS(longitude/57.2958 - #longitude#/57.2958)))) >> * 3963, 0) AS distance >> >> FROM AmericanZipCodes >> >> WHERE (latitude >= #latitude# - (#form.radius#*.009009)) AND (latitude >> <= #latitude# + (#form.radius#*.009009)) AND (longitude >= #longitude# - >> (#form.radius#*.009009)) AND (longitude <= #longitude# + >> (#form.radius#*.009009)) >> >> ORDER BY distance >> </cfquery> >> >> I did not do the math on this. Thankfully that was done by the folks who >> previously did work for my client... otherwise I'd have been in the same >> boat as you looking for how the hell to do this sort of thing. :o) >> >> I hope this helps. If you need to know where I got the database from. >> Just give a holler and I'll try to dig it up. :o) >> >> Cheers, >> Chris >> >> >> Jordan Michaels wrote: >> >>> We're currently putting something together like this that checks to see >>> if two addresses are within a certain mile radius using the Google Maps >>> >> API. >> >>> Not a simple process, but we think we can make it do what we want it to. >>> >>> HTH! >>> >>> Warm regards, >>> Jordan Michaels >>> Vivio Technologies >>> http://www.viviotech.net/ >>> Blue Dragon Alliance Member >>> [EMAIL PROTECTED] >>> >>> >>> Rick Root wrote: >>> >>> >>>> Is anyone out there using "within 25 miles of <zipcode>" for searching >>>> address database? >>>> >>>> I've gotten a request where someone is visiting a certain zip code and >>>> >> they >> >>>> want to know all the prospects within a 25 mile radius of the zip code >>>> they're visiting. >>>> >>>> I know there are ways to do this.. just wondered what people out there >>>> >> are >> >>>> using. >>>> >>>> Thanks! >>>> >>>> Rick >>>> >>>> >>>> >>> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268746 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
