Well I didn't get a response to my question but I fixed my problem and wanted to post what I ended up using. I also want to mention that this calculates in miles not kilometers. To use kilometers, change 3963 to 6378.7.
First I query the db to get the lat long for the zipcode. <cfquery name="passedzip" datasource=""> SELECT zip, lat, long, city, state FROM zips WHERE zip='#form.passedzipcode#' </cfquery> Then I query my dealer table to get the dealers in the specified range. <cfquery datasource="" name="getlocs"> SELECT lat, long, name, city, state, zipcode, area_code, phone, 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 dealers_geo WHERE 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)))) <= #form.passedradius# ORDER BY distance </cfquery> The 3963 is the assumed radius of the earth in miles. 6378.7 is the radius in kilometers. The value of 57.2958 is used to convert lat and long into radians. I have to say that I am very impressed with the speed of the query. If you have any questions let me know. Phillip B. www.LoungeRoyale.com www.FillWorks.com ----- Original Message ----- From: "Phillip B" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, September 02, 2003 2:49 PM Subject: Zipcode lookup Which query? > 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
