Archives of the TeradataForum

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>