## Message Posted: Fri, 27 May 2005 @ 20:22:28 GMT

 < Last>>

 Subj: Re: Distance based on latitude and longitude From: Duell, Bob

Hi,

Not surprisingly, the two posted solutions yield different results. The first one (from Geoffrey) comes closest to matching the result I've used for a few years in SAS (not Teradata directly). I'm guessing the difference is that I use a more precise value for PI with my SAS programs.

The second one (from Jim Ford) looks more efficient. At least it involves fewer function calls.

Without going into too much detail, does anyone have an opinion? Or, does anyone have a set of coordinate pairs with the "correct" distance?

I merged the two methods into one example:

select 35.25927 as lat1
, -81.2238 as lon1
, 35.22143 as lat2
, -81.1492 as lon2
,  3.14159 as pi

/* Method 1, Geoffrey Rommel */
/* First convert degrees to radians */
, lat1 * pi / 180 as lat1r
, lon1 * pi / 180 as lon1r
, lat2 * pi / 180 as lat2r
, lon2 * pi / 180 as lon2r

, (sin((lat2r - lat1r)/2.0)**2) + (cos(lat1r) * cos(lat2r) * sin((lon2r - lon1r)/2.0)**2) as A
, (7917.53141) * atan(sqrt(a)/sqrt(1-a)) as MILES1

/* Method 2, Jim Ford */
/* Convert to radians inside formula */
, 3963*ACOS( COS(lat1*pi/180)
*COS(lat2*pi/180)
*COS( (pi/180)*(lon1-lon2) )
+ ( SIN(lat1*pi/180)
*SIN(lat2*pi/180))) as MILES2

I appreciate this topic. It's timely for something I'm working on.

Thanks,

Bob

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2005 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback