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:268742
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
