Russ - can you help me out here a little bit? I'd like to be able to run a query like this:
select * from prospects where prospects.zipcode in (select zipcode from zipcodes where getDistance(prospect.zipcode, zipcode.zipcode) <= 25) How would I do that? On 2/5/07, Russ <[EMAIL PROTECTED]> 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:268798 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
