Archives of the TeradataForum

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

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

Hi,

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.

Thanks!

Bob

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