Archives of the TeradataForum

Message Posted: Wed, 03 May 2006 @ 21:01:26 GMT

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

Geoffrey, thanks for the suggestion, I'll give it a try. It's probably "good enough" for marketing, especially considering the performance gain.

Armin, I don't have a distance function, UDF or otherwise. Seems like it should be possible, but I don't know how to do it. I was just trying to simplify the question. However, here is a sample SELECT statement to illustrate the formula:

     select -81.2238  (float) as deg_lon1
           , 35.25927 (float) as deg_lat1
           , -81.1492 (float) as deg_lon2
           , 35.22143 (float) as deg_lat2
           , cast((7912.5408*atan2(sqrt(1-(sin((deg_lat2*0.0174532925199
             -deg_lat1*0.0174532925199)/2.0)**2+(cos(deg_lat1*0.0174532925199)
             *cos(deg_lat2*0.0174532925199)*sin((deg_lon2*0.0174532925199
             -deg_lon1*0.0174532925199)/2.0)**2))),sqrt((sin((deg_lat2
             *3.14159/180-deg_lat1*3.14159/180)/2.0 )**2+(cos(deg_lat1
             *0.0174532925199)*cos(deg_lat2*0.0174532925199)*sin((deg_lon2
             *0.0174532925199-deg_lon1*0.0174532925199)/2.0)**2)))))
             as decimal(12,8)) as dist_miles

The degrees-to-radians constant is pre-calculated to 0.0174532925199 above. Again, I can't explain the details that went into this formula (meaning I don't understand them completely). And it's not 100 percent accurate, but it's fine for my marketing purposes.

All, thanks for the help,

Bob

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