Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Distance calcuations
 
From:   Christopher.Hamilton

  I have a question regarding your function my_dist. Is it a udf > and if so, are willing to share the code. I have to do the same > thing within the next month. :-)  


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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023