3D Intersections

Following a discussion at the Eng-Tips Forum, I have written an Excel User Defined Function (UDF) to find the intersection point of two 3D line segments.  The UDF has been added to the IP2 spreadsheet, which is available for free download (including full open source code) from IP2.ZIP.

The method of calculation of the intersection point is:

1. Check if either End of Line2 is coincident with either end of Line1.
2. Check if either end of Line2 lies on Line1.
3. Check for quick 2D solution, if both lines lie on a plane parallel to the XY, XZ or YZ planes.
4. Check that lines lie in the same plane, i.e distance of End2 of Line2 from the plane formed by Line1 and End1 of Line2 is zero.
5. Find 2D IP of lines projected on to XY, XZ, and YZ planes.
6. Check that the IP is within the length of each line segment.
7. Extract the IP X,Y, and Z coordinates and assign to the function return value.

The screen shots below show for two 3D line segments:

The intersection point found using the new UDF (IP3D):

IP3D example

On spreadsheet calculation for the same two lines, using the UDF IP() (Example 2), and using only built in Excel Functions (Example 3)

and graphs of the two lines projected on to the three axis planes:

Projected intersecting lines

This entry was posted in Coordinate Geometry, Excel, Maths, Newton, UDFs, VBA and tagged , , , , . Bookmark the permalink.

7 Responses to 3D Intersections

1. Lori Miller says:

Interesting problem. It’s not too difficult to extend to the question of finding the nearest points on two lines in 3D space. Given two pairs of 3×1 vectors of points on each line A,B and C.,D, array-enter in two 3×1 ranges:
=A+(B-A)*S
=C.+(D-C.)*T
where S,T are the least squares estimates that minimise the distance between the lines, i.e.:
=LINEST(C.-A,(B-A)*{0;1}+(C.-D)*{1;0},0)

Matrix operations can greatly simplify these type of calculations. It’s fun to try to extend to higher dimensions too, I’ve sent a hypercube example that might be of interest.

PS. The vba code isn’t compiling for me in 64-bit office 2010 version, probably due to a 32-bit windows api call.

Like

2. dougaj4 says:

Hi Lori – your post got sent to spam for some reason. Don’t know why, the filter is usually spot on.

Where did you send the hypercube? I haven’t received anything.

You might try deleteing the timer class from the VBA, I think that would be the only thing with any api calls.

Like

3. Nitin Patel says:

While trying to get intersection point of lines
Line 1 X Y Z
A -3000 2000 0
B 2000 4000 0

Line 2 X Y Z
C 3000 2000 0
D -2000 4000 0

I am getting
Method 1
IP3D Function:
X Y Z
No IP No IP No IP

Method 2
1. Find IP of lines projected onto each plane through the origin using IP function
X Y Z
XY Plane: 0 3200
XZ Plane No IP No IP
YZ Plane No IP No IP

Two IP’s found? FALSE FALSE FALSE
IP’s equal?
Lines intersect? TRUE
X Y Z
Intersection point 0 3200 No IP

Method 2 — Z should be zero.
Method 1 – Some modification required…

Thanks

Nitin Patel

Like

4. dougaj4 says:

Nitin – if the lines are in the XY plane the method won’t work, but you can just use a 2D intersection function in that case.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.