How do I add data from one table to another table based on the distance

[#1]

I have two data tables for two set of measurement on the wafer. There are four columns in each table, X, Y representing the location of measurements, and two columns of measurement data for each table. the measurements  are not collected from the exact same positions (X,Y) of the wafer.  I would like to update ( add extra columns of the other table) either of the tables based on the proximity of measurement locations. To be more clear, I would like to have a combined table ( total 8 columns ) based on matching of  X1,Y1 to the closest point on X2, and Y2. Please see the picture I uploaded. I know Distance function probably can be utilized to make a matrix of all distances from a given X,Y but not sure how to write the jsl code to compute the updated table for me. 

[#2]

Check out following functions in addition to Distance

• VPTree() 
• KDTable() 
• And maybe Loc Sorted() 

Then add row based index to your "main" table, utilize one of these to find the closest index (basically row), add it to your second table and then finally use update/join to combine the tables.

[#3]

Based upon @jthi links I believe this does what you want

dt1=New Table( "Table 1",
	Add Rows( 20 ),
	New Column( "x",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Random Normal() )
	),
	New Column( "y",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Random Normal() )
	),
	Set Row States( [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0] )
);

dt2=New Table( "Table 2",
	Add Rows( 20 ),
	New Column( "x",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Random Normal() ),
		Set Display Width( 120 )
	),
	New Column( "y",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Random Normal() ),
		Set Selected,
		Set Display Width( 118 )
	),
	Set Row States( [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0] )
);

M1 = dt1 << GetAsMatrix();
MD1=vptree(M1);

M2 = dt2 << GetAsMatrix();
MD2=kdtable(M2);

//returns the nearest point of Table 1 to a point in table 2
//to reverse the look up, make MD1-> MD2 and M2->M1
{rows, dist} = MD1 << K nearest rows( 1, M2);

dt1<<new column("same as rows",
							Numeric,
							Ordinal,
								Set Values( rows )
									
			);
			
dt2<<new column("row number",
							Numeric,
							Ordinal,
								Formula(Row())
									
			);

Steve

[#4]

Hi Steve, and Jarmo,

Thanks for the suggestions. The script was also useful. However, when I run I don't think it finds the closest distance. For instance, in the first row of Table 1 matches to row 8 ( distance of ~1.37) of Table 2 but there are many much closer points such as row number 1 ( distance of 0.69)  ( let distance to be sqrt[(x-x0)^2+(y-y0)^2])

[#5]

I think I mixed up the data tables at the end of the script, try this:

dt1=New Table( "Table 1",
	Add Rows( 20 ),
	New Column( "x",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Random Normal() )
	),
	New Column( "y",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Random Normal() )
	),
	Set Row States( [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0] )
);
wait(0.0);
dt1:x << suppress eval( true );
dt1:y << suppress eval( true );

dt2=New Table( "Table 2",
	Add Rows( 20 ),
	New Column( "x",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Random Normal() ),
		Set Display Width( 120 )
	),
	New Column( "y",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Random Normal() ),
		Set Selected,
		Set Display Width( 118 )
	),
	Set Row States( [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0] )
);
wait(0.0);
dt2:x << suppress eval( true );
dt2:y << suppress eval( true );

M1 = dt1 << GetAsMatrix();
MD1=vptree(M1);

M2 = dt2 << GetAsMatrix();
MD2=kdtable(M2);

//returns the nearest point of Table 1 to a point in table 2
//to reverse the look up, make MD1-> MD2 and M2->M1
{rows, dist} = MD1 << K nearest rows( 1, M2);

dt2<<new column("same as rows",
							Numeric,
							Ordinal,
								Set Values( rows )
									
			);
			
dt1<<new column("row number",
							Numeric,
							Ordinal,
								Formula(Row())
									
			);
	

I virtual linked them and did some visual checks so I believe we are good now

[#6]

I still see it is not picking up the closest points: 
Names Default To Here( 1 );
Close All(data tables);
Clear Log();
Clear Symbols();
dt1= Open(
		"C:\Users\Table 1.jmp");
wait(0.0);
dt1:X1 << suppress eval( true );
dt1:Y1 << suppress eval( true );
dt2= Open(
		"C:\Users\Table 2.jmp");

wait(0.0);
dt2:X2 << suppress eval( true );
dt2:Y2 << suppress eval( true );

M1 = dt1 << GetAsMatrix();
MD1=vptree(M1);


M2 = dt2 << GetAsMatrix();
MD2=kdtable(M2);

//returns the nearest point of Table 1 to a point in table 2
//to reverse the look up, make MD1-> MD2 and M2->M1
{rows, dist} = MD1 << K nearest rows( 1, M2);

dt1<<new column("same as rows",
							Numeric,
							Ordinal,
								Set Values( rows )
									
			);
			
dt2<<new column("row number",
							Numeric,
							Ordinal,
								Formula(Row())
									
			);
dt1 << update(
     with(dt2),
     match(:same as rows=:row number),
     ignore missing,
     add columns from update(none)
);

[#7]

Hey @spinup 

I think the issue is my code assumed you only had the x and Y values in the table.  If you start with your tables that have extra numeric columns then k nearest neighbors is trying to find the closest point for X,Y, A, B vs X, Y, C, D.

I think that's why @hogi codes works so well if it is only looking at X and Y.

If you made the non-X/Y columns character and ran the my second code it would work, you'd just need to change them back to numeric once you joined them. 

[#8]

To get the matrix with the coordinates, you can use data table subscripting, like Jarmo does in his example:

row = 0 -> all rows
columns: x and y

coordinates as matrix = dt1[0, {"x", "y"}];

[#9 · Accepted solution]

dt1=New Table( "Table 1",
	Add Rows( 20 ),
	New Column( "x",
		Formula( Random Uniform(0,10) )
	),
	New Column( "y",
		Formula( Random Uniform(0,10) )
	)
);

dt2=New Table( "Table 2",
	Add Rows( 64 ),
	New Column( "x2",
		Formula( floor(row()/8)+1 )
	),
	New Column( "y2",
		Formula( mod(row(),8)+1 )
	)
);


// M2 = dt2 << GetAsMatrix();
// better - see below
M2 = dt2[0, {"x2", "y2"}];
MD2=kdtable(M2);

dt1 << New Column( "row_orig",Nominal, Formula( Row() ));
dt2 << New Column( "row_2",Nominal,Formula(  Row() ));


dt1 << New Column( "table",Nominal, set each value(1));
dt2 << New Column( "table",Nominal, set each value(2));
wait(0);

// step by step, for each row in dt1, use MD2 to find the closest position in dt2
dt1 << new column("row_2",Nominal, set each value(
pos = Matrix({:x, :y});
(MD2 << K nearest rows( 1, pos))[1][1]));


// concatenate the tables to generate a combined plot
dt2:x2 << set name("x");
dt2:y2 << set name("y");
check = dt1  << Concatenate(dt2);
check << Graph Builder(
	Show Control Panel( 0 ),
	Variables( X( :x ), Y( :y ), Overlay( :row_2 ), Color( :table ) ),
	Elements(
		Line( X, Y, Row order( 1 ) ),
		Points( X, Y, Overlay( 0 ), Legend( 2 ) )
	),
	SendToReport(
		Dispatch( {}, "400", ScaleBox,
			{Legend Model(
				2,
				Properties( 0, {Marker( "FilledSquare" )}, Item ID( "1", 1 ) ),
				Properties( 1, {Marker( "Circle" )}, Item ID( "2", 1 ) )
			)}
		)
	)
);

[#10]

Thanks for cleaning this up for me @hogi !

Question, can you walk me though what this line of code is doing?

dt1 << new column("row_2",Nominal, set each value(pos = Matrix({:x, :y});(MD2 << K nearest rows( 1, pos))[1][1]));

Thank!