by the way I know the below wouldn't work...
What I need is a function or stored proc that returns a list of zip codes
within a given distance from a specified zip code.
Ie,
select * from zipcodes where getDistanceFrom('27502') < 10
On 2/6/07, Rick Root <[EMAIL PROTECTED]> wrote:
>
> 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:268802
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
