How to use loop to get the minimum value from two cross joined tables?

Database
New Member

How to use loop to get the minimum value from two cross joined tables?

Hi,

I have table A with three columns: location ID, lat and long. I also have a table B, with station ID, station_lat and station_long. I want to find the nearest station for each location in table A. How to get a table C with location ID and it’s nearest station ID? Since the number of locations and stations are so big, I ran into the issue of no enough space without using loop calculations.

Thanks!
1 REPLY 1
Teradata Employee

Re: How to use loop to get the minimum value from two cross joined tables?

Hi SJ001,

 

Don't use loop, use GeoSpatial here.

 

The best scenario for you would involve the following steps :

  1. add ST_Geometry columns in both your tables (for example Location_Pos & Station_Pos)
  2. populate the columns using this expression :
    new ST_Geometry('ST_Point', long, lat)
  3. add indexes and statistics on those columns
  4. add a filter on a maximum distance where you seek your relation between location and station

When meeting those conditions, Teradata can use the GeoSpatial index to run the query which would look like :

 select ta.Location_Id
      , tb.Station_Id
   from TableA as ta
   join TableB as tb on ta.Location_Pos.ST_Distance(tb.Station_Pos) < 10
qualify row_number() over(partition by ta.Location_Id
                              order by ta.Location_Pos.ST_Distance(tb.Station_Pos) asc) = 1
;