I have a stored procedure that someone wrote for me. This is the core of the sql from it SELECT TOP 10 dbo.dealers_geo.*, SQRT(SQUARE(69.1 * (lat - @lat)) + SQUARE(69.1 * (long - @long) * COS (@lat / 57.3))) AS DistanceMiles FROM dbo.dealers_geo ORDER BY DistanceMiles
I found a site that shows to do it using this sql SELECT zip, lat, long, state, city, 3963 * (ACOS((SIN(#passedzip.lat#/57.2958) * SIN(lat/57.2958)) + (COS(#passedzip.lat#/57.2958) * COS(lat/57.2958) * COS(long/57.2958 - #passedzip.long#/57.2958)))) AS distance FROM zips WHERE (lat >= #passedzip.lat# - (#form.passedradius#/69)) And (lat <= #passedzip.lat# + (#form.passedradius#/69)) AND (long >= #passedzip.long# - (#form.passedradius#/69)) AND (long <= #passedzip.long# + (#form.passedradius#/69)) ORDER BY distance I believe that the second is more accurate. The problem is I cant seem to get the second to work correctly. :-/ Something with the radius part causing it to get weird results. If they both worked, which do you think is the best to use? I would be really happy if you can help make the second query work. I'm using CF5 and SQL 2000. Also, I will be willing more than willing to share the finial SQL once I have it working. :) Phillip B. www.LoungeRoyale.com www.FillWorks.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm