Get lat and long for the zipcode you are searching for, then pass them to a
stored procedure:
CREATE PROCEDURE dbo.getTopStores
@lat1 as float,
@lng1 as float
AS
select top 10 abs((3959 * ATAN(power((1-(power(((sin(@Lat1/57.3) *
sin(zipcodes.Lat/57.3)) + (cos(@Lat1/57.3) * cos(zipcodes.Lat/57.3) *
cos(zipcodes.Lng/[EMAIL PROTECTED]/57.3))),2))),0.5)/((sin(@Lat1/57.3) *
sin(zipcodes.Lat/57.3)) + (cos(@Lat1/57.3) * cos(zipcodes.Lat/57.3) *
cos(zipcodes.Lng/[EMAIL PROTECTED]/57.3)))))) as distanceInMiles,
stores.storeID, stores.NumTechs, stores.Prob_Code,
stores.Site_Number, stores.county, stores.name, stores.address, stores.city,
stores.state, stores.activityType, stores.Franchise, stores.Zip,
stores.OpenDate, stores.storeType
from zipcodes left join stores on zipcodes.zipcode=stores.zip
where zipcodes.lat is not null and zipcodes.lng is not null and
stores.storeID is NOT NULL
order by 1
GO
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 23, 2005 8:09 PM
To: SQL
Subject: Fw: Zipcode radius sqlcode
Hy Jann,
Thanks for your feedback, yes its much better to let the DB do the
processing. Is it possible to see the sql code you used for the stores
procedure?
I need to display results like this for a real estate agents database:
Realtor A - Phone - Zipcode - Distance
Realtor B - Phone - Zipcode - Distance
Realtor C - Phone - Zipcode - Distance
The realtors closest to the search zipcode show up at top, the ones farthest
at bottom. So the results are sorted by distance.
Thanks,
Pardeep.
----- Original Message -----
From: "Jann E. VanOver" <[EMAIL PROTECTED]>
To: "SQL" <[email protected]>
Sent: Wednesday, February 23, 2005 5:18 PM
Subject: Re: Zipcode radius sqlcode
> FYI, the accuracy of your distance calculations is dependant on the
> number of decimal places in your Zipcode data.
>
> I know this because I work with zip code tables with 6 digits of
> precision, and the distances I can compute with those are VERY
> inaccurate. They're all relatively correct, they're just not right.
>
> I've put my code into a stored procedure so I can link the zips with the
> tables that define the retailers I'm covering. That way the relation
> between Zip distance and Retailer location can be done inside SQLServer
> before sending anything back to CF.
>
>
> J
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2168
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
