Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 02 May 2006 @ 18:44:31 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Distance calcuations
From:   Duell, Bob


I'd like to follow-up on a topic from last year: distance calculations between two points using longitude and latitude.

Assume I have a function (my_dist) that calculates distance in miles between two pairs of coordinates.

Now suppose I have two tables. The first, STATIONS, has the coordinates of the 200 gas stations in our county. The second, CUSTOMERS, has the coordinates of the home address of all my customers. I want to find the "closest" STATION for each CUSTOMER, which requires calculating the distance to each station for each customer, then selecting the smallest distance.

The SQL solution I can think of requires a product join, such as:

     select a.CUSTOMER_ID, b.store, my_dist(a.long,a.lat,b.long,b.lat) as distance
     from   CUSTOMERS a, STATIONS b
     group by 1
     qualify csum(1,distance) = 1

Is there a better way? If I have millions of customers and hundreds of stations, it looks very inefficient. I'm doing this today using SAS and in-memory arrays (after extracting the CUSTOMER data), but I thought I'd ask if it can be done entirely inside Teradata.



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