I am not sure if this is the most appropriate place to post this but here is the issue and feel free to tell me and easier way to do it if there is one.

I am trying to use API to mate parts according to hole location. I have made a macro that identifies hole locations and know i am trying to figure out how to match them up. down below I show two sets of data from different parts that go together.(I am only using excel to visually see the data the finished macro will only run in solidworks) I am stumped on how to go about further organizing the data to show that which holes mate together.

I'm assuming that your desired end-result is the user clicks on two components and your macro attempts to mate the hole patterns that happen to best align (without specifying corresponding holes). This is important because it means the algorithm has to accommodate all permutations of the holes (unless you can guarantee their order somehow on the design-side). Your datasets show that the holes are in order but this might be a coincidence. It is also possible that they are rotated and translated with respect to one-another.

What this looks like is comparing two rigid bodies and trying to find a rigid transform between them. The problem becomes more complex when one set includes un-paired points (series 1 has 6 points but series 2 has 12; 6 of which are not aligned with series 1).

I believe you can find the transform between two rigid bodies in the following way:

p1 is a sample point represented with homogeneous coordinates (put a 1 at the end)

d1 is a corresponding sample point.

p1 = [px1;py1;pz1;1]

d1 = [dx1;dy1;dz1;1]

P and D are [4x4] matrices whose columns are four sample points.

P = [p1,p2,p3,p4] = px1, px2, px3, px4

py1, py2, py3, py4

pz1, pz2, pz3, pz4

1, 1, 1, 1

T is a transform that maps p1 to d1, p2 to d2, p3 to d3, and p4 to d4.

T * p1 = d1

T * P = D

Therefore, we can solve for T by inverting P and pre-multiplying by D

T = D * inv(P)

My naive suggestion would be to select any 4 points from dataset 1 and then iterate over all (N choose 4) combinations of dataset 2, compute the transform for each, and check if it has the properties of a rigid transform.

One property of rigid transforms is that they are orthogonal, their transpose is equal to their inverse. So if T' is not equal to inv(T) then you can conclude that T is not a rigid transform. Another property is that they are affine, I can't think of a quick way of testing that.

There is probably an approach to this problem that doesn't require brute-force.