How to take 1 row on table 1 and compute it against every row of table 2 and do this for each row in table 1

Hello all, I have table 1 with 30 rows, and I have table 2 with 50 rows. Each table contains an X, Y, and Z coordinate point. I need to take each row individually of table 1 and find the absolute difference from every row of table 2 between the coordinates and have it return the smallest value for that specific row in table 1.

Ideally i would like the code to return the ID number of the smallest value that was found. I will attempt to explain mathmatically below what I need:

For the first row in table 1, called ID:1 values could be [ 100, 200, 300] and the table 2 has 4 rows as such: ID:1 [120, 235, 480], ID:2 [150, 207, 380], ID:3 [109, 210, 391], ID:4 [108, 216, 310].

absolute(100-120)+absolute(200-235)+absolute(300-480)= 235

absolute(100-150)+absolute(200-207)+absolute(300-380)= 137

absolute(100-109)+absolute(200-210)+absolute(300-391)= 110

absolute(100-108)+absolute(200-216)+absolute(300-310)= 34

So the code would recognize that 34 is the smallest value and corelate it to ID:4 on table 2 and would return the output to be ID:4

The above example is for only 1 row in table 1 imagine having to do it 30 times like the attached file has and having to do it 50 times for each of the 30 rows.

Attached is the excel file where I included random numbers for each table.

I am really hoping this makes sense and someone can help me. My experience is low and this is for a work project that I was thrown into. Please help. Thanks

Answer 1

[Joel Lynch]

Assuming the data is read into two variables, d1(1:N1,1:3) and d2(1:N2,1:3) by

d1 = xlsread('Matlab Help.xlsx','Random','B2:D31'); % left Set
d2 = xlsread('Matlab Help.xlsx','Random','H2:J51'); % right set

Then the simplest and fastest solution I can think of is this:

% Invert d1 to 3xN1 and copy along the third dimension
%   so that d1b is size 3xN1xN2, where N1/N2 are number of rows
%   in d1/d2 respectively. 
d1b = repmat(d1',1,1,size(d2,1)); 

% Reshape d2 to be 3x1xN2
d2b = reshape(d2,[3,1,size(d2,1)]);

% Copy d2 along second dimension N1 times, such that d2b is 
%    also 3xN1xN2, matching size of d1b
d2b = repmat( d2b, 1,size(d1,1),1);

% Compute the 2nd norm (2, second argument of vecnorm)
%    of the difference in coordinates (d1b-d2b, first argument of vecnorm)
%    along the first dimension of the 3xN1xN2 matrix (1, third argument of vecnorm)
%    such that vecnorm returns 1xN1xN2 matrix, the distance between all combinations of d1
%    and d2. Then take the minimum along the third dimension (N2) and
%    return the value and index
[min_distance, jmin_index] = min( vecnorm(d1b-d2b,2,1) , [],3);

This might be hard to follow. You could also do something like this, but it will be slower

% Predefine as Not-A-Number
min_distance = NaN(size(d1,1),1);
jmin_index   = NaN(size(d1,1),1);

% Loop each value in d1
for i=1:size(d1,1)
    
    % Copy d1 so that it has the same size as d2, but with 
    % each row being the d1 coordinate
    d1b = repmat( d1(i,:), size(d2,1),1 );
    
    % Get the minimum of distance of the vector of distances 
    [min_distance(i), jmin_index(i)] = min( vecnorm(d1b-d2,2,2) );
    
end

You could even unroll this even more if this is still confusing:

% Loop each row in d1
for i=1:size(d1,1)
    
    % Get the local coordinate
    d1_local = d1(i,:);
    
    % Default min distance to a high value, index to NaN
    min_distance(i) = 1e9;
    jmin_index(i)   = NaN;
    
    % Loop each row in d2
    for j=1:size(d2,1)
        
        % Get distance 
        distance = norm( d1(i,:)-d2(j,:) ,2);
        
        % Check if smaller
        if (distance< min_distance(i))
            min_distance(i) = distance;
            jmin_index(i)   = j;
        end
        
    end
    
end

But this is a very inelegant solution compared to the first, and it will be hundreds of times slower for very large datasets.