Archives of the TeradataForum

Message Posted: Thu, 04 May 2006 @ 22:09:54 GMT

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

Armin, try this. It uses the law-of-cosines function, which is faster than Haversine, but needs at least 16 places of precision to give good results.

     SELECT
             p1.id,
             p2.id,
             (
                     ACOS(SIN(p1.lat*3.14159/180)*SIN(p2.lat*3.14159/180)+
                 COS(p1.lat*3.14159/180)*COS(p2.lat*3.14159/180)*
                     COS((p2.lon*3.14159/180)-(p1.lon*3.14159/180)))*
                     (3963-13*SIN(p1.lat*3.14159/180))
             ) AS dist
     FROM
             households p1,
             br_loc p2
     WHERE
             -- Limit to "Address" and "Zip+4" geocode match levels
             -- WARNING: This will exclude PO Boxes and some Rural Routes.

             p1.match_cd IN ('A','4') AND
             p2.match_cd IN ('A','4') AND

             p2.lon BETWEEN ( p1.lon - 0.1666 ) AND ( p1.lon + 0.1666 ) AND
             p2.lat BETWEEN ( p1.lat - 0.1666 ) AND ( p1.lat + 0.1666 )
     HAVING
             dist < 5.0;

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