Find circle radius & circle center in 3D from (3) XYZ pts
5

Question

Find circle radius & circle center in 3D from (3) XYZ pts

DrMetal (Materials)

(OP)

24 Jun 09 02:54

GIVEN: X-Y-Z coordinates of 3 points in 3D space.  FIND:  Radius of circle on those 3 points & Circle center co-ordinatesBookmark:
Question: Can someone provide a mathematical step by step procedure to calculate and determine a circle radius and the center point coordinates from three GIVEN 3D points with X-Y-Z coordinates?

- I have searched the web extensively, and found lots of proposed solutions, but my college math is quite rusty and after several days, I still can not come up with the correct detailed solution.   I can do this easily from three points in 2D, in Excel using three simultaneous equations with TWO variables (i.e. find circle center @ h, j).   But I can't get it to work in 3D with a similar spreadsheet approach with THREE variables (i.e. find circle center @ h-j-k).  Seems I keep going in mathematical circles (no pun intended) with a modified Excel approach with the three quadratic equations.

- Is it possible to solve this mathematically with (3) simultaneous quadratic equations?  Or do I need a 4th equation, determinates, etc?.  Seems when I try the double elimination (or substitution) of variables approach, I keep coming up with unsolvable, or meaningless equations.   With as much time as we have spent on this problem, we would be happy to compensate someone for their time to solve this for us.

- We have several drawings with bent pipes in 3D where we have only THREE 3D points given to us.   We need to be able to determine the TRUE bend radius of the pipes.  These are special alloy pipes @ $25k each, so we want to be sure we have the correct 2D bend radius before we bend them.  I'd prefer an approach that could be put into a spreadsheet to make reiterative calcs go quickly.   I'd prefer to have a detailed step-by-step solution vs. being directed to one of the web solutions.  I have seen most of them, and still am stuck on this.
Thanks, MWP

GregLocock (Automotive) · Answer 1 · 2009-06-24 04:08:01

I'm not entirely sure that method will work

First use the 3d points to define the plane, then on that plane use the same 3 points to define the circle.

Note that you haven't beaten the requirement that it takes 4 triads to define a sphere, as this merely finds a section through an infinite number of spheres.

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

HEC (Mechanical) · Answer 2 · 2009-06-24 04:12:30

DrMetal,
Sounds an interessting and challenging problem. Are the three points given always the same three points on the arc i.e are they always 45deg apart or some other known constant? This may be the way to simplify the solution into a planar (2D) problem.

Mark Hutton

gerhardl (Mechanical) · Answer 3 · 2009-06-24 04:50:43

A question more than help, perhaps, but:

I presume that all bends that you want to determine are bends in one plane, and that the points given are points on the bend radius in that plane?

If you then first determine the plane and find the xy coordinates for the three points in that plane you should be able to go back to your 2D solution.

Are all the bends 90 degrees?

tygerdawg (Mechanical) · Answer 4 · 2009-06-24 06:53:06

Probably your simplest, fastest solution:  plot these three {X, Y, Z} points into a CAD package, put a spline through them, and then use the CAD package to measure the spline.  I know this would work with AutoCAD or SolidWorks or Alibre.

ANALYTICALLY:
A circle is a planar geometrical object, so you must resolve your 3D curve to 2D.

From my CRC Standard Math Tables 26th Edition (Analytic Geometry)

3-point form {x1,y1}, {x2,y2}, {x3,y3}:

solve the simultaneous equations matrix:

| (x^2 + y^2)          x   y   1  |
| (x1^2 + y1^2)     x1  y1  1  |     = 0
| (x2^2 + y2^2)     x2  y2  1  |
| (x3^2 + y3^2)     x3  y3  1  |

If you're still stumped, go to the library and check out a CRC math book...it's all in there.

TygerDawg
Blue Technik LLC
Virtuoso Robotics Engineering
www.bluetechnik.com

rb1957 (Aerospace) · Answer 5 · 2009-06-24 07:33:16

it's as "simple" as greg posts ... 3 non-colinear pts define a plane, transform the xyz co-ords of the three points into the new co-ord system (with (x1,y1) in plane and z1 out-of-plane. then 3 planar non-colinear pts define an arc.

williedawg (Mechanical) · Answer 6 · 2009-06-24 07:41:22

I tend to agree with GregLocock.
The OP said that these are bends defined by 3 points.
As such, they are coplaner.

Establish a Plane using said points, then find the radius using same.

As for the Excel-fired solution to h-j-k, I'm not sure what works....

Here's something I found on the 'net

http://mathforum.org/library/drmath/view/51753.html

rb1957 (Aerospace) · Answer 7 · 2009-06-24 07:57:46

there are tons (or tonnes in europe) of excel sites (xcalcs.com, mrexcel.com, excelcalcs.com, wiki) i'm sure you can find something to determine the transformation matrix for a plane given three points (a hint ... make one point the origin, one axis is from pt1 to pt2, the 2nd axis is normal, the 3rd axis is out-of-plane (and redundant); so now you have the new planar co-ords for the 3 points (one being (0,0), the other being (x,0), ...) again there are many available solution for an arc thru 3 pts.

of course, you could put the three co-ords into Auto-Cad and it'll solve this for you ...

williedawg (Mechanical) · Answer 8 · 2009-06-24 08:00:40

a few more comments.

If you have "several" and each is ~ $25k, your time spent on trying to get Excel to work might be better spent just modeling the parts up, and obtaining the h-j-k co-ordinate if this is indeed a requirement at all.

I've done some tubing modeling in both AutoCad & SW where the 3D points are given for every transition from straight to bend, and back to straight; each bend having a unique value not explicitly given, but geometrically correct.
(All bends being tangent to the straights)

None of this is in reference to a sphere.

It goes rather quickly, once you get onto it.

rb1957 (Aerospace) · Answer 9 · 2009-06-24 08:21:30

a quick google found the attmt (sigh, Y can't people do the simple stuff to help themselves?)

http://files.engineering.com/getfile.aspx?folder=acd98c00-ac90-4de5-96ef-b6

Wrenchbender (Mechanical) · Answer 10 · 2009-06-24 14:57:02

I think what you are trying to quantify is called a 'great circle'. If I get the picture, your section of pipe is bent to constant radius whose center lies in the plane defined by 3 known points on the pipe.

For an analytic solution, four equations are needed. Expanding on the previous posts, there are the three obvious equations plus a fourth that constrains all points to a plane. I skipped the tensor transforms and tried setting up something in 4eqns/4unkn. Here's a draft but be forewarned, its ugly. Maybe you can use it (or lose and go the CAD route).

http://files.engineering.com/getfile.aspx?folder=97e766e3-f322-4623-a859-84

TheTick (Mechanical) · Answer 11 · 2009-06-24 15:38:25

In 2D: for points A, B, and C, the center of the circle would be the intersection of the bisector of any two: AB, BC, AC.

In 3D:
The center is on plane ABC.
The center is on the bisector planes of segments AB, BC, AC (you would only need two of these planes).
The center of the circle is at the intersection of three planes: ABC, bisector of AB, and bisector of BC.

bat Honesty may be the best policy, but insanity is a better defense. bat
http://www.EsoxRepublic.com-SolidWorks API VB programming help

IRstuff (Aerospace) · Answer 12 · 2009-06-24 16:00:37

This is not that different than the GPS position solution.  However, since knowledge of absolute time is not needed here, you shouldn't need 4 pseudoranges.

There are 3 unknowns, so only 3 equations should necessary.  However, pathological configurations of the 3 points amy result in a poor solution.

Have you tried to structure the three 3D distance equations from the center, and have Excel solve for when all 3 distances are equal?  You don't, at this point, really care what the equation of the sphere looks like, you simply need to find the center.

However, as with GPS, you could get a poor geometrical dilution of precision, particularly if the 3 points are clustered within a small portion of the sphere.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

zekeman (Mechanical) · Answer 13 · 2009-06-24 16:19:48

First off, 3 points in space do not determine the surface of a unique sphere. You need 4 non co-planer points.
The center is found by first finding the plane that is the bisector of any two points,say point 1 and point 2. Then find the plane of the bisector of point 2 and point 3. Now since the intersection of these planes is a LINE and EVERY point on that line is equidistant to the 3 points so that, there is no unique center and therefore no unique sphere. Now you need a 4th point in space to determine that unique sphere. You get it obviously by finding a 3rd plane that bisects the line from that 4th point to any of the other points; the intersection of the 3 planes so formed is the center. which solves your problem. You get it by writing 4 equations:
(X-Xj)^2 +(Y-Yj)^2+(Z-Zj)^2=R^2, for j=1,2,3,4
where
X,Y,Z are the unknown coordinates of the sphere center
and R is the unknown radius.
4 equations and 4 unknowns should get the solution unless the 4 points are co-planer.

r

where

DrMetal (Materials) · Answer 14 · 2009-06-24 19:47:19

Gents .. lots of responses here ... all good info.  Just to clarify on some of the issues / questions.

1) I know we need 4 points to define a sphere.  But we are not looking for anything spherical.  We are only looking for a radius and the ceenter of that radius, as determined by the three XYZ defined points (non-colinear).

2) The 3 pts do define a triangle on a plane.  The three points of any triangle will lie on a unique circle.

3)My primary focus was to see if anyone had a mathematical (vs. geometrical) solution that could be put into a spreadsheet.  As I stated in my 1st post ... my college math is quite rusty.  That's probably why I was having trouble coming up with the math way to define the planes, bisectors, and then solve the problem.   I think I am close with the simultaneous equations, but as someone stated above, I think I need one more point to make the 4th equation.

4) More clearly, the points we are given are totally random points in 3D space from some a common set of XYZ axes with 0,0,0 origin.  The three points for each pipe are given:
Pc (starting point of curvature, tangent to the bend centerline, and the next straight section),
Pt (Ending point of the curved section, tangent to the bend centerline, and to the next straight section), and
Pi (point of intersection of the two tangents above)

The curved section is a constant radius (once transformed into a 2D plane).  Out of plane view, it is a slight ellipse, or a straight line (if viewed parallel to the planar view.

- After bending, the pipe is tilted, offset, and rotated into positon to put the 3 points into proper position.  Then, these are welded to large fabrications, that are shipped 3000 miles away.  And the end points have to mate up excactly to someone elses pipe work, using the same drawings.

- The arc lengths and arc angles vary from pipe to pipe.

- I am sure that this be done graphically in Solid Works, but them I have no way to mathematicallly verify my CAD guys work is dimensinally correct.

- Lots of comments about this being a 2D planar solution ... and it is, but I don't know how to convert the 3D points into a 2D plane.  THis is kind of what I commented on in my first post ... rusty math that is.

- How is the plane mathematically "defined" by the three
points?  I can probablly find this ok.

- I think I have my old college CRC book ... good suggestion .. I will look at that also.

Willedawg ... I had already found that mathforum site, and had studied that particular solution .. but was still a little fuzzy on that onee.  Seems more complex than it needs to be ... but we are not trying to define a sphere.
I will pass your info to my SWks guy ... we don't have ACAD.

rb1957 ... that Engr.com link apppears to be for integer solutions, and only in 2D.  I don't see the relevance.  However, the Engr.com link from Bestwrench appears to give me the 4th equation I was missing to plug these into a simple simultaneous equation solution in Excel.  I will play with that one too.  I did a ton of google searching and never hit on the Engineering.com site (not sure why).

Zekeman ... again, I am looking for the circle, not the sphere, and I only have 3 points, so I don't think this will work for me.

Thanks again to everyone.  Big help overall. Any other comments still welcome.

cloa (Petroleum) · Answer 15 · 2009-06-24 21:54:36

I don't know if anyone has mentioned this but if this is a distorted pipe aren't you going to need some statistical component to the analysis as the points may be on a distorted part of the pipe or have the measurements been very careful to avoid a distorted part of the pipe.

GregLocock (Automotive) · Answer 16 · 2009-06-24 22:23:42

Ha, the solver method works very quickly, took 2 minutes to type it in.

    X    Y    Z
1    17    300    4
2    -99    56    2
3    8    5    -33

Centre    20.6    148.6    14.4
Radius    151.8

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

zekeman (Mechanical) · Answer 17 · 2009-06-25 00:07:02

OK, now I understand
But, you must know that in order to get a circle that is tangent to lines formed by pts 1 and 2 at pt 1 and pts 2 and 3 at pt 3, the lines 1-2 and 2-3 must be of equal length.
First write the equation of the plane (pts1,2,3)
eq1  x+ay+ bz=c
where a,b and c are determined by entering the coordinates of the 3 points, giving 3 equations whose solution of a,b,c determines the plane of eq1.

Now we get the direction cosines of the 2 lines 1-2 and 2-3
which are proportional to

line 1-2
(x1-x2),(y1-y2),z1-z2)

line 2-3
(x2-x3),y2-y3),z2-z3)

Next, we draw lines from a point  X,Y,Z  on the plane of eq1  to pt 1 and pt 3. Their respective direction cosines are proportional to:
(X-x1),(Y-y1), Z-z1) line Q  X,Y,Z to x1,y1,z1
(X-x3),Y-y3),Z-z3)   line R  X,Y,Z to x3,y3,z3
Since line Q is perpendicular the line 1-2 at point 1 and line R is perpendicular to line 2-3 at point 3,(definition of tangency), the dot product of these respective line pairs  are zero
Expanding
(X-x1)*(x1-x2)+(Y-y1)*(y1-y2)+(Z-z1)*(z1-z2)=0
(X-x3)*(x2-x3)+(Y-y3)*(y2-y3)+(Z-Z3)*(z2-z3)=0
From eq 1, X,Y, Z being in the plane, is constrained by
X+aY+bZ=c
Now we have 3 eq and 3 unknowns in X Y and Z
The solution X,Y,Z is the center of the circle.
A note of caution. I REPEAT, that you MUST have the 2 lines 1-2 and 2-3 of equal length; otherwise you wlll get wrong answers.
Hope this helps

rb1957 (Aerospace) · Answer 18 · 2009-06-25 07:37:20

i'd simply zeke's approach by redefining the three (xyz) points in terms of their common plane, (XY), removing one dimension.

now you know two tangents, (X1Y1-X2Y2) and (X1Y1-X3Y3), and two points of tangency, (X2Y2) and (X3Y3). determine the normals to the tangents thru the points of tangency. their intersection is the centre of the arc.

telecomguy (Mechanical) · Answer 19 · 2009-06-25 09:52:01

Ran across this equation while researching this post. I haven't performed a check on the validity of the equation, but thought it may be of assistance. As noted later in the discussion, this only provides the radius and not the coordinates of the center. Therefore an additional calculation would have to be performed.

http://www.physicsforums.com/showpost.php?p=1420798&postcount=9

-tg

rb1957 (Aerospace) · Answer 20 · 2009-06-25 09:58:36

actually i don't think that'll work with the data provided ... the OP has clarified that he knows only two points on the arc; his 3rd point defines tangents to the unknown arc thru the 1st 2 points.

telecomguy (Mechanical) · Answer 21 · 2009-06-25 10:08:02

Hmmm... I missed that detail. So keeping it 2d for a minute, with two intersecting lines, you can define a circle that is tangent to both and passes thru one of the points, but the other point will over constrain the circle won't it?

rb1957 (Aerospace) · Answer 22 · 2009-06-25 10:23:02

i don't think so ... the information provided by the 3 pts (two tangents, and 2 points of tangency) is slightly over-defined, but it does set up a very easy solution (the intersection of normals through the pts of tangency).

you only need the two tangents and one point of tangency, as the centre is on the bisector of the two tangents.

TheTick (Mechanical) · Answer 23 · 2009-06-25 10:24:39

With two tangent points and a virtual intersection:

Given: tangent points A & C, intersection point B...

•Center of arc lies on the bisector of ABC
•Bisector can be determined by line from B to midpoint of segment AC.
•Radius is distance from A or C to bisector.
•Center is found on line perpendicular from A or C to bisector (may also be found w/ point-to-curve distance calculation).

zekeman (Mechanical) · Answer 24 · 2009-06-25 14:12:49

I forgot to give the radius of the circle which is simply the vector length between X,Y,Z and x1,y,,z1 or
sqrt[X-x1)^2+(Y-y1)^2+(Z-z1)^2]

zekeman (Mechanical) · Answer 25 · 2009-06-26 01:15:35

1957 quote

"i'd simply zeke's approach by redefining the three (xyz) points in terms of their common plane, (XY), removing one dimension.

now you know two tangents, (X1Y1-X2Y2) and (X1Y1-X3Y3), and two points of tangency, (X2Y2) and (X3Y3).  determine the normals to the tangents thru the points of tangency.  their intersection is the centre of the arc."
-------------------------------------------------------------
Do you really think you simplify the solution by working  in the x-y plane?
What about the transformation  into the x-y plane? I don't really think so.
The 3D solution is fairly simple these days thanks to the quick ability of any of the math programs to solve linear equations.
I could easily set up the solution in EXCEL - first solving my 2x2 set  and then the final 3x3 set.

GregLocock (Automotive) · Answer 26 · 2009-06-26 07:16:36

So given the example in my earlier post, is it right or wrong?

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

rb1957 (Aerospace) · Answer 27 · 2009-06-26 07:40:27

greg, if think you were working on the assumption that the three points were on the arc. now we know two points are on the arc, and the 3rd point is the intersection of the tangents. this implies that there is order to the points (ie, pts 1 & 2 are on the arc, and pt 3 is the intersection). without knowing how you set up the test, we can't check it.

zeke,
actually, yes, i do think working in 2D is easier than 3D. i think working with co-ords like (0,0), (x,0), and (x1,y1) is easier. i think looking on the arc for the normal (the true view) is easier to visualise. admittedly, the math is moved to the transformation matrices, but none of this math is "hard".

GregLocock (Automotive) · Answer 28 · 2009-06-26 07:52:36

I missed that. The solution was of the original misstated problem. OK, reboot.

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

chicopee (Mechanical) · Answer 29 · 2009-06-26 15:57:33

See my attachment regarding your problem. It is based on your revised thread. I realized that your solution is actually a fundamental surveying problem with PC,PT,POI,T,
R and included angle I. To find position of center you will need to solve three simultaneous equations.

http://files.engineering.com/getfile.aspx?folder=6638aa78-943b-4086-8a5b-28

DrMetal (Materials) · Answer 30 · 2009-06-29 22:26:24

Gents,
- Sorry for the delay to say thanks to all for the great responses to this thread. I have not completed this yet, but am very confident that we have sufficient information to complete the solution I was after. So thanks again to all. Very nicely done! MWP

Kapitan (Aerospace) · Answer 31 · 2009-07-05 12:47:31

Finding the centre and radius of the circumscribed circle of a triangle (ABC) is something I first did in the mid-1980s — in BASIC, I'm retired now but I've still got the notes written at the time. The normal form of the plane equation wasn't used — just the direction cosines of the axis system that the three points are in. The diameter of the circle that fits the corners of the triangle is the length of any side divided by the sine of its opposite angle; the centre of the circle is found by solving one more triangle.

The following code is modified BASIC; if it's pasted into an Excel VBA macro it will run — it is basic stuff in more ways than one. I'm sure there are slicker ways of doing this task, but I've used direction cosines; the code has been run with various values (and signs) for the three points — and has outputted the correct (so far...) radius and radius centre coordinate each time. (I've had it checked against a CATIA model)

I've posted the code because an original requirement was for a solution that could be pasted in and run.

CODE

Sub CircumscribedRadius_04()
        Range("A1:Z512").NumberFormat = "###0.000000"
'   ———————————————————————————————————————————————————————————————————
'   Given three points in space (A,B,C)
         Xa = 47: Ya = -61: Za = 137
         Xb = -6: yb = 19: Zb = -28
         Xc = 83: Yc = 40: Zc = -55
'   Lengths of AB, AC, AC
         AB = (((Xa - Xb) ^ 2) + ((Ya - yb) ^ 2) + ((Za - Zb) ^ 2)) ^ 0.5
         BC = (((Xb - Xc) ^ 2) + ((yb - Yc) ^ 2) + ((Zb - Zc) ^ 2)) ^ 0.5
         AC = (((Xa - Xc) ^ 2) + ((Ya - Yc) ^ 2) + ((Za - Zc) ^ 2)) ^ 0.5
'   Direction cosines of AB(ABi,ABj,ABk)
        ABi = (Xb - Xa) / AB
        ABj = (yb - Ya) / AB
        ABk = (Zb - Za) / AB
'   Direction cosines of AC(ACi,ACj,ACk)
        ACi = (Xc - Xa) / AC
        ACj = (Yc - Ya) / AC
        ACk = (Zc - Za) / AC
'   Cosine of angle BAC
     cosBAC = (AB ^ 2 + AC ^ 2 - BC ^ 2) / (2 * AB * AC)
         AD = cosBAC * AC
         CD = (AC ^ 2 - AD ^ 2) ^ 0.5
'   Position of point D, which is C projected normally onto AB
         Xd = Xa + (AD * ABi)
         Yd = Ya + (AD * ABj)
         Zd = Za + (AD * ABk)
'   Direction cosines of CD(Cdi,CDj,CDk)
        CDi = (Xc - Xd) / CD
        CDj = (Yc - Yd) / CD
        CDk = (Zc - Zd) / CD
'   Direction cosines of normal to AB and CD
'   — to be used for rotations of circle centre
         Ni = (ABk * CDj) - (ABj * CDk)
         Nj = (ABi * CDk) - (ABk * CDi)
         Nk = (ABj * CDi) - (ABi * CDj)
'   # Diameter of circumscribed circle of a triangle is equal to the
'   the length of any side divided by sine of the opposite angle.
'   This is done in a coordinate system where X is colinear with AB, Y is // to CD,
'   and Z is the normal (N) to X and Y, and the origin is point A
'         R = D / 2
     sinBAC = (1 - cosBAC ^ 2) ^ 0.5
          R = (BC / sinBAC) / 2
'   Centre of circumscribed circle is point E
         X2e = AB / 2
         Y2e = (R ^ 2 - X2e ^ 2) ^ 0.5
         Z2e = 0
'   Transform matrix
'                    Rotations                 Translations
'             ——————————————————————————————————————————————
'               ABi  ,   ABj  ,  ABk                 Xa
'               CDi  ,   CDj  ,  CDk                 Ya
'                Ni  ,    Nj  ,   Nk                 Za
'             ——————————————————————————————————————————————
'   Position of circle centre in absolute axis system
         X_centre = Xa + (X2e * ABi) + (Y2e * CDi) + (Z2e * Ni)
         Y_centre = Ya + (X2e * ABj) + (Y2e * CDj) + (Z2e * Nj)
         Z_centre = Za + (X2e * ABk) + (Y2e * CDk) + (Z2e * Nk)
'   ———————————————————————————————————————————————————————————————————
                    Cells(1, 1) = "Radius    =": Cells(1, 2) = R
                    Cells(3, 1) = "X_centre  =": Cells(3, 2) = X_centre
                    Cells(4, 1) = "Y_centre  =": Cells(4, 2) = Y_centre
                    Cells(5, 1) = "Z_centre  =": Cells(5, 2) = Z_centre
End Sub

rb1957 (Aerospace) · Answer 32 · 2009-07-06 09:11:18

very interesting, but doesn't help the OP ... he doesn't have 3 points on the arc (as he explained on 19:47 24 Jun)

DrMetal (Materials) · Answer 33 · 2009-07-06 13:05:30

Chicopee ... thanks for the attachment too.  I think this works, but I think there was an error in Eq 4, to find "R".

Shouldn't that formula be:
T/R = TAN(I/2) ... therefore, R = T / TAN(I/2)?

I plugged these formulas into Excel.  When I ran it this way, it seems to come out correct.  But let me know if you disagree.

Kapitan .. thanks for that post too ... on occasion, we do have the "3 points on the radius" problem. That will help.  In this case, we only have the 2 points on the arc, and the point of intersection of the two tangents at those points.

williedawg (Mechanical) · Answer 34 · 2009-07-10 16:56:11

I'm determined to understand this, but my math skills ended with HS.

I drew up a random 3-D Sketch in SW, obtained all the Points, Lengths, & curve data, etc....

then, to check these,

I used Chicopee's method in Excel (and substituting R = T / TAN(I/2) for formula #4 as DrMetal mentions...which worked for me too)

Fine so far. What stumps me is step #6. The formulas all look good and make sense, but how does one put them into the 3 simultaneous equations to solve for x4,y4,z4 ?

abs(R) = (x1-x4)^2 + (y1-y4)^2 + (z1-z4)^2 and
abs(R) = (x2-x4)^2 + (y2-y4)^2 + (z2-z4)^2

abs(V) = abs(R)^2 + abs(T)^2
abs(V) = (x3-x4)^2 + (y3-y4)^2 + (z3-z4)^2

how is all this set up in Excel ?

rb1957 (Aerospace) · Answer 35 · 2009-07-12 12:47:38

let me preface this post by saying i haven't read chicopee's post.

the solution is (i think) very simple
given two points of tangency of an arc, and
given the point of intersection of these two tangents, then

two point form of a line (see wiki) gives you the equation of the tangents,
the two radii are normal to the tangents thru the points of tangency (normal to a line, slope/point form of a line, see wiki)
the center of the arc is the intersection of the two radii
the length of the radius is the distance from a point of tangency to the centre

DrMetal (Materials) · Answer 36 · 2009-07-13 21:19:12

Williedawg,
- I have not finished going thru all this to solve for the circle center.  But I believe the answer is here.  I have an Excel SS set up to solve for Xc,Yc in 2D ... using 3 simulataneous equations, it works fine to solve for the 2 points (Xc,Yc) ... so I assumed I need 4 equations for the 3D points.  But my math is a bit rusty too ... so I am still working on it ... just have not had the time yet.
The first 3 eq are easy:
Eq1: (X1-Xc)^2 + (Y1-Yc)^2 + (Z1-Zc)^2 = R^2
Eq1: (X2-Xc)^2 + (Y2-Yc)^2 + (Z2-Zc)^2 = R^2
Eq1: (X3-Xc)^2 + (Y3-Yc)^2 + (Z3-Zc)^2 = R^2
for ALL 3 points ON THE RADIUS
or
Eq2: (X2-Xc)^2 + (Y2-Yc)^2 + (Z2-Zc)^2 = V^2
for the Point of Intersection not on the radius

- We've already solved for R and V, so it looks easy, but when I try to solve for Xc, Yc, Zc, using only 3 equations, the math just runs me in circles and does not lead me to a solution.

- It's the lack of a 4th equation that is stumping me. I think we may have the answer in this thread, I just have not had time to do it.  With 4 equations, you can solve for two variables 1st , then solve again for the one of these two variables in terms of the 2nd, and plug back in for the value of the 1st, and so on.

- What might also help would be to find the XYZ coordinates of the point of intersection of the circle arc and the bisector line between the line of POI (Point of Intersection of the 2 tangents) to the circle center.  That would give a 3rd point on the arc too and might make it easier.  But I have not looked at this yet.

rb1957 (Aerospace) · Answer 37 · 2009-07-13 23:42:20

intriguing ... how do you relate V (the distance from the centre to the intersection of the tangents) to R (the radius) ... you can do it, it's just that it's way more complicated than you need to be, IHMO.

i'll assume you didn't (couldn't?) follow my solution ...

DrMetal (Materials) · Answer 38 · 2009-07-14 00:04:00

RB,
- I thought that since we can solve for R and V, that the numbers could be used in the simultaneous equations to find the center point coordinates, but maybe not. Again, maybe my rusty math at work. I still haven't been able to put the solution together yet. Some related to time to work on this, but I don't see it clearly spelled out here.

- BTW ... is solving a set of simultaneus equations identical to solving similar problems using Cramers rule or similar?

rb1957 (Aerospace) · Answer 39 · 2009-07-14 07:53:42

RE: Find circle radius & circle center in 3D from (3) XYZ pts

rb1957 (Aerospace) 14 Jul 09 07:53

how about my post 12 july 12:47 ?

IDS (Civil/Environmental) · Answer 40 · 2009-07-18 21:45:50

I have posted a solution to this problem at:
http://newtonexcelbach.wordpress.com/2009/07/18/two-new-functions-for-ip-xls/

Including a spreadsheet with an on-spreadsheet solution, and two User Defined Functions that will find the 3D circle centre given either any 3 points on the circle, or two tangent points and the intersection of the tangents.

The method used for 3 points on the circle is:
1 Read the 3D coordinates for three points on the circle
2 Translate Point 2 and Point 3 for an origin at Point 1
3 Find polar coordinates of Point 3
4 Rotate Points 2 and 3 about the Z axis so that Point 3 is on the XZ plane
5 Rotate Points 2 and 3 about the Y axis so that Point 3 is on the X axis
6 Find angle of Point 2 from XY plane
7 Rotate Points 2 about the X axis so that Point 2 is on the XY plane
8 Find the XY coordinates of the mid-points of lines 1-2 and 3-2.
9 Find a second point on the perpendiculars through mid-points
10 Find the XY coordinates of the intersection of the perpendiculars. This is the centre of the circle.
11 Find the radius of the circle
12 Rotate and translate the centre point back to the original axes
13 Assign the 3D coordinates of the circle centre, and the circle radius, to the function return value as a 1×4 array

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

IDS (Civil/Environmental) · Answer 41 · 2009-07-18 23:59:58

I've taken the liberty of modifying Kapitan's routine to work as a function, and added it to the IP spreadsheet, which can be downloaded from the link in the previous post, and I've posted the code below.

The functions give identical answers, and also take almost exactly the same time to run.

CODE

Function ArcCenP3_2(Point1 As Variant, Point2 As Variant, Point3 As Variant) As Variant
' Based on "CircumscribedRadius_04() posted by "Kapitan" on the Eng-Tips Forum, 5 Jul 09
' http://eng-tips.com/viewthread.cfm?qid=248118&page=1
Dim XA As Double, YA As Double, ZA As Double
Dim XB As Double, YB As Double, ZB As Double
Dim XC As Double, YC As Double, ZC As Double
Dim AB As Double, BC As Double, AC As Double
Dim ABi As Double, ABj As Double, ABk As Double
Dim ACi As Double, ACj As Double, ACk As Double
Dim cosBAC  As Double, AD As Double, CD As Double
Dim XD As Double, YD As Double, ZD As Double
Dim CDi As Double, CDj As Double, CDk As Double
Dim Ni As Double, Nj As Double, Nk As Double
Dim sinBAC As Double, R As Double, X2e As Double, Y2e As Double, Z2e As Double
Dim X_Centre As Double, Y_Centre As Double, Z_Centre As Double
Dim Cent(1 To 1, 1 To 4) As Double

    If TypeName(Point1) = "Range" Then Point1 = Point1.Value2
    If TypeName(Point2) = "Range" Then Point2 = Point2.Value2
    If TypeName(Point3) = "Range" Then Point3 = Point3.Value2
'   ———————————————————————————————————————————————————————————————————
'   Given three points in space (Point1, Point2, Point3)
XA = Point1(1, 1)
YA = Point1(1, 2)
ZA = Point1(1, 3)
XB = Point2(1, 1)
YB = Point2(1, 2)
ZB = Point2(1, 3)
XC = Point3(1, 1)
YC = Point3(1, 2)
ZC = Point3(1, 3)
'   Lengths of AB, AC, AC
AB = (((XA - XB) ^ 2) + ((YA - YB) ^ 2) + ((ZA - ZB) ^ 2)) ^ 0.5
BC = (((XB - XC) ^ 2) + ((YB - YC) ^ 2) + ((ZB - ZC) ^ 2)) ^ 0.5
AC = (((XA - XC) ^ 2) + ((YA - YC) ^ 2) + ((ZA - ZC) ^ 2)) ^ 0.5
'   Direction cosines of AB(ABi,ABj,ABk)
ABi = (XB - XA) / AB
ABj = (YB - YA) / AB
ABk = (ZB - ZA) / AB
'   Direction cosines of AC(ACi,ACj,ACk)
ACi = (XC - XA) / AC
ACj = (YC - YA) / AC
ACk = (ZC - ZA) / AC
'   Cosine of angle BAC
cosBAC = (AB ^ 2 + AC ^ 2 - BC ^ 2) / (2 * AB * AC)
AD = cosBAC * AC
CD = (AC ^ 2 - AD ^ 2) ^ 0.5
'   Position of point D, which is C projected normally onto AB
XD = XA + (AD * ABi)
YD = YA + (AD * ABj)
ZD = ZA + (AD * ABk)
'   Direction cosines of CD(Cdi,CDj,CDk)
CDi = (XC - XD) / CD
CDj = (YC - YD) / CD
CDk = (ZC - ZD) / CD
'   Direction cosines of normal to AB and CD
'   — to be used for rotations of circle centre
Ni = (ABk * CDj) - (ABj * CDk)
Nj = (ABi * CDk) - (ABk * CDi)
Nk = (ABj * CDi) - (ABi * CDj)
'   # Diameter of circumscribed circle of a triangle is equal to the
'   the length of any side divided by sine of the opposite angle.
'   This is done in a coordinate system where X is colinear with AB, Y is // to CD,
'   and Z is the normal (N) to X and Y, and the origin is point A
'   R = D / 2
sinBAC = (1 - cosBAC ^ 2) ^ 0.5
R = (BC / sinBAC) / 2
'   Centre of circumscribed circle is point E
X2e = AB / 2
Y2e = (R ^ 2 - X2e ^ 2) ^ 0.5
Z2e = 0
'   Transform matrix'                    Rotations                 Translations
'             ——————————————————————————————————————————————
'               ABi  ,   ABj  ,  ABk                 Xa
'               CDi  ,   CDj  ,  CDk                 Ya
'                Ni  ,    Nj  ,   Nk                 Za
'             ——————————————————————————————————————————————
'   Position of circle centre in absolute axis system
X_Centre = XA + (X2e * ABi) + (Y2e * CDi) + (Z2e * Ni)
Y_Centre = YA + (X2e * ABj) + (Y2e * CDj) + (Z2e * Nj)
Z_Centre = ZA + (X2e * ABk) + (Y2e * CDk) + (Z2e * Nk)

'   ———————————————————————————————————————————————————————————————————

Cent(1, 1) = X_Centre
Cent(1, 2) = Y_Centre
Cent(1, 3) = Z_Centre
Cent(1, 4) = R

ArcCenP3_2 = Cent
End Function

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Kapitan (Aerospace) · Answer 42 · 2009-07-19 12:18:51

An excellent solution, Doug — and many thanks for taking the time to update my old BASIC routine.

rb1957 (Aerospace) · Answer 43 · 2009-07-20 08:49:20

IDS,

maybe i'm being unusually thick, and i haven't gone through your solution line-by-line, however, if i have three points on a plane i can make to different arcs through them depending on the tangency condition. i'm surprised that a math procedure can determine the right choice ?

zekeman (Mechanical) · Answer 44 · 2009-07-20 13:19:41

They are still doing the OP problem of circumscribing a circle, not the tangency problem, albeit too inefficiently.

rb1957 (Aerospace) · Answer 45 · 2009-07-20 13:34:55

IDS's earlier post says "will find the 3D circle centre given either any 3 points on the circle, or two tangent points and the intersection of the tangents."

IDS (Civil/Environmental) · Answer 46 · 2009-07-20 19:01:10

rb1957 - no, 3 points define a triangle on a plane (unless they are co-linear), and you can only draw one circle through the vertices of a triangle.

And yes, the spreadsheet has a function to find the circle defined by two tangent points and their intersection point (in fact it works with one tangent point and any point on the other tangent line past the tangent point). Thanks for reading :)

zekeman - as noted above my functions do address the two tangent question. If you read the posts you would also have seen that the calculation time using the approach of rotating the points onto a plane was almost identical to that using kapitan's routine, which used the direction cosine approach. To my mind the rotation approach makes it more intuitively obvious what is going on and why it works. I might have a go at putting your method into a function when I have some time, but not today.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

williedawg (Mechanical) · Answer 47 · 2009-07-20 20:15:11

After some fumbling around, I was able to get Kapitan's Code to work (This is the first time I've used VB)

Just as he suggested, it was pasted into a Macro in VB and then Run. In order to use some other 3D points in space, I invoked the Macro, then used "Step Into" to edit in some different values; Ran it, noted the new values, then went back and compared to a SW drawing. All looked OK. Is this the "normal" way to do this?

Now,... how do I get the 2nd Code written by Doug in this thread to work ?
It starts out as a Function, not a Sub. How is this implemented or made into a Macro in Excel Basic ?
Or is some other Excel method/procedure needed?

I looked at the IP spreadsheet and downloaded it, but am having a hard time separating out only what's needed just for the circle and how to initialize it with the variable 3 point inputs.

Thanks for all the time and effort you have put into this.

GregLocock (Automotive) · Answer 48 · 2009-07-20 23:16:24

No that is a truly horrible way to use a user-function!

It should work exactly like a built in function. The 'help' on user functions describes what to do.

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

IDS (Civil/Environmental) · Answer 49 · 2009-07-21 00:58:47

williedawg - There are two ways to use a VBA function, either as a function in the spreadsheet, just like the Excel built-in functions, or call it from a VBA subroutine.

You can either follow the instructions below using the IP.xls spreadsheet, or if you prefer you can copy and paste the code into a Visual Basic module in a new spreadsheet, but if you do that you will need to also copy the functions Rotate() and IP(), which are called from the other functions.  It's probably easier just to use the IP spreadsheet for a start.

To use any of the three functions enter the X, Y, and Z coordinates of three points on three separate lines.  You can use any sheet.  Suppose you enter the data in the ranges: A1:C1, A2:C2, and A3:C3, with row 1 and 3 being points on the circle and line 2 either an intermediate point on the circle or the tangent intersection point.  Then to use the functions enter in any convenient cells:

=ArcCenT2IP(A1:C1, A3:C3, A2:C2)
=ArcCenP3(A1:C1, A2:C2, A3:C3)
=ArcCenP3_2(A1:C1, A2:C2, A3:C3)

Note the different order for ArcCenT2IP

In each cell the X coordinate of the circle centre will appear.  ArcCenT2IP will give a different result to the other two of course.

The functions return an array; 1x4 for ArcCenP3 and ArcCenP3_2 and 2x4 for ArcCenT2IP.  To get the other results select 4 adjacent columns and 1 or 2 rows, with the function entered in the top left cell, press F2 to enter edit mode, then press ctrl-shift-enter.  There are examples of this on the IP spreadsheet.

An example of using the function called from another subroutine is given below:

CODE

Sub ArcCen()
Dim TP1 As Variant, TP2 As Variant, TIP As Variant
Dim Cent As Variant, Res As Range

TP1 = Range("A1:C1").Value2
TP2 = Range("A2:C2").Value2
TIP = Range("A3:C3").Value2

Set Res = Range("A5:D6")

Cent = ArcCenT2IP(TP1, TP2, TIP)

Res = Cent

End Sub

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

DrMetal (Materials) · Answer 50 · 2009-07-21 01:23:24

Hi Doug,
- See what I got you into! Heck-of-a thread on this subject. I had no idea that it would be this "intriguing" to all the posters. Thanks for all of your input on this, and your emails too, and everyone else for the postings. We are getting ready to do the original PIPE developments and BENDS as described in earlier posts. FYI ... these are 8" x XXS (0.875"THK) 2205 DUPLEX SSTL PIPES x 20'L to bend and put into these 3D positions. We'll be using this thread to finally figure out and mathematically verify the correct 2D bends. Will post the final results of our success (or degree of success) later. Thanks again to all. Welcome to keep commenting if there is a need. Mike P.

williedawg (Mechanical) · Answer 51 · 2009-07-21 14:18:09

(Phew)
Got the IP.xls to work.
Entered some values in a new Sheet and compared to SW drawing...accurate to 4 plcs.

"To get the other results select 4 adjacent columns and 1 or 2 rows, with the function entered in the top left cell, press F2 to enter edit mode, then press ctrl-shift-enter."

This part didnt seem to work for me. (maybe something I don't understand about the array function)

Instead of "select 4 adjacent columns", I selected the cell containing =ArcCenT2IP(A1:C1, A3:C3, A2:C2), then drag-selected 3 more columns to the right, but only 1 row, then did the F2 ctrl-shift-enter thing, and it filled the cells with the correct values for a 2pt circle with tangent lines intersecting at the 3rd pt.

For the next row down, I did the same thing, and it filled in the values for the 3pt circle situation

Doug, Kapitan and Greg, Thanks so much for the help on this topic and your patience with an Excel novice.

http://files.engineering.com/getfile.aspx?folder=fc9b1310-6a52-4e17-b2b7-e8

IDS (Civil/Environmental) · Answer 52 · 2009-07-21 19:00:17

Quote:
I selected the cell containing =ArcCenT2IP(A1:C1, A3:C3, A2:C2), then drag-selected 3 more columns to the right, but only 1 row, then did the F2 ctrl-shift-enter thing, and it filled the cells with the correct values for a 2pt circle with tangent lines intersecting at the 3rd pt.

That's what I meant. I usually hold down the shift key and use the arrow keys to select. So I start on the cell with the function in it, hold down shift and press -> 3 times then F2 and ctrl-shift-enter. The mouse works just as well of course. I need to find a way to describe the process more clearly.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

DrMetal (Materials) · Answer 53 · 2009-07-22 00:10:45

Hi Doug,
- Quick question ... I must be doing something wrong ... when I plug in new numbers into the points for XYZ in your spreadsheet, the results all return the "#NAME" error, as if it can't read my inputs. Do I need to format the numbers that I enter a certain way? Or something else simple that I am doing wrong? I was just playing around with it a little tonight and could not get the Excel sheet to show numbers. As soon as I change even one of your sample entries, I get nothing but cells of errors. Sorry for the basic question, but I am still an Excel novice, and remember very little about basic and almost nothing about VB for that matter. Thanks again for the work. Mike P.

zekeman (Mechanical) · Answer 54 · 2009-07-22 00:32:58

IDS,
Sorry, I didn't read your post but I don't like rotating and unrotating stuff which to me is unnecessary.

My take  is:
The center of the circumscribing circle is simply at the intersection of 3 planes, namely
1) the plane determined by the 3 pts 1,2,3 in space
2) the plane perpendicular to line 1-2 containing the midpoint of 1-2
3) the plane perpendicular to line 2-3 containing the midpoint of 2-3.

First write the equation of the plane (pts1,2,3)
eq1  x+ay+ bz=c
where a,b and c are determined by entering the coordinates of the 3 points, giving 3 equations whose solution of a,b,c determines the plane of eq1.(from my previous post)

Now we get the direction cosines of the 2 lines 1-2 and 2-3
which are proportional to

line 1-2
(x1-x2),(y1-y2),z1-z2)

line 2-3
(x2-x3),y2-y3),z2-z3)

Next we write the equation of the plane perpendicular to line 1-2
eq2 (x1-x2)X+(y1-y2)Y+(z1-z2)Z=D1
where D1 is determined from the midpoint location (x1+x2)/2,y1+y2)/2,(z1+z2)/2 in this plane; so substituting for X,Y,Z I get D1 to be
[(x1^2-x2^2+y1^2-y2^2+z1^2-z2^2]/2=D1
Similarly the plane perpendicular to 2-3 containing the midpoint of 2-3 is
eq3)(x2-x3)X+(y2-y3)Y+(z2-z3)=D2
and
D2=[x2^-x3^2+y2^2-y3^2+z2^2-z3^2]/2
So the problem boils down to solving the 3x3 for for a,b,c, then evaluating D1 and D2 and finally solving for X.Y, Z from the 3x3 set of the 3 planes, which is formally more concise.
But I have no problem for those who need more clarity and don't feel comfortable working in space. and as far a time to do it, it's not that much greater, but why all the additional steps.

IDS (Civil/Environmental) · Answer 55 · 2009-07-22 17:22:55

DrMetal - it sounds like you don't have macros enabled, which is easy to do in Excel 2007 because that is the default state. In 2007 when you start up you get a small message above the spreadsheet saying:

Security Warning Macros have been disabled [Options]

You need to click on the Options box and select "Trust this content"

In earlier versions the option to enable macros is much more obvious.

Pleae let me know what version you are using, and if enabling macros fixes the problem.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Kapitan (Aerospace) · Answer 56 · 2009-07-23 12:37:39

I've modified my Basic subroutine to deal with the intersection point case — by a different method, and what I think is far simpler than before. It's all done in 3D using direction cosines: it uses no auxiliary axis system, no transform matrix, no rotations, and no plane equations.

The centre of the required bend radius lays somewhere along the bisector of the two tangent lines that create the point (B) of intersection of the tangents — and this distance, from point B, of this radius centre along this bisector, is the hypotenuse of a right-angled triangle (either ABD or CBD) — depending which is the shorter of the two lines. By finding the bisector of lines AB and BC between A and C, the direction cosines of the bisector are calculated, This distance from B to the radius centre is the length of the line from the closest given point (A or C) to the intersection point, divided by the cosine of half the angle ABC.

To avoid using the other of the two bisectors of the two lines, I've found the bisector line using the mid-point of two points spaced equal distances along the lines AB and BC (I've used an arbitary 1000 units, which is preferably longer than AB or BC.)

The position of the bend radius centre is found by multiplying the direction cosines of the bisector by the length of the hypotenuse - and adding them to the x,y,z values of the intersection point (B).

As before, it will run in an Excel VBA macro: I've tried to keep the thing 'readable' . I haven't (yet) done a check for the three point co-linear case, although it will flip through the straight-line situation. Once again, I've had someone check it against a 3D model — and it works — whether the next pipe bend is a LH or RH one. It also outputs identical results to Doug's method.

CODE

Sub CornerRadiusCentre_02()
        Range("A1:Z512").NumberFormat = "###0.000000"
'   ———————————————————————————————————————————————————————————————————
'   Given three points in space (A,B,C)
         Xa = -71: Ya = -37: Za = 47: '   Start Point
         Xb = 59: Yb = -40: Zb = 0 '      Tangent Intersection Point
         Xc = 100: Yc = 7: Zc = 88: '     Finish Point
'   Lengths of AB, AC, AC
         AB = (((Xa - Xb) ^ 2) + ((Ya - Yb) ^ 2) + ((Za - Zb) ^ 2)) ^ 0.5
         BC = (((Xb - Xc) ^ 2) + ((Yb - Yc) ^ 2) + ((Zb - Zc) ^ 2)) ^ 0.5
         AC = (((Xa - Xc) ^ 2) + ((Ya - Yc) ^ 2) + ((Za - Zc) ^ 2)) ^ 0.5
'   Direction cosines of AB
        ABi = (Xa - Xb) / AB
        ABj = (Ya - Yb) / AB
        ABk = (Za - Zb) / AB
'   Direction cosines of BC
        BCi = (Xc - Xb) / BC
        BCj = (Yc - Yb) / BC
        BCk = (Zc - Zb) / BC
'   From point B create two points (G and H) 1000 units along AB and BC
         Xg = Xb + (1000 * ABi)
         Yg = Yb + (1000 * ABj)
         Zg = Zb + (1000 * ABk)
'
         Xh = Xb + (1000 * BCi)
         Yh = Yb + (1000 * BCj)
         Zh = Zb + (1000 * BCk)
'   Find mid-point (M) of G and H
         Xm = (Xg + Xh) / 2
         Ym = (Yg + Yh) / 2
         Zm = (Zg + Zh) / 2
'   Length of line BM (Tangents bisector)
         BM = ((Xm - Xb) ^ 2 + (Ym - Yb) ^ 2 + (Zm - Zb) ^ 2) ^ 0.5
'   Direction cosines of BM
        BMi = (Xm - Xb) / BM
        BMj = (Ym - Yb) / BM
        BMk = (Zm - Zb) / BM
'   Cosine of angle ABM (Half angle ABC)
     cosABM = (ABi * BMi) + (ABj * BMj) + (ABk * BMk)
'   Find the shorter of AB and BC
        If AB < BC Then Base = AB Else Base = BC
'   Right-angled triange ABD or CBD
Hypotenuse = Base / cosABM
'   Find coordinates of Bend Radius Centre
   X_centre = Xb + (Hypotenuse * BMi)
   Y_centre = Yb + (Hypotenuse * BMj)
   Z_centre = Zb + (Hypotenuse * BMk)
'   Find Bend Radius
   Bend_Rad = (Hypotenuse ^ 2 - Base ^ 2) ^ 0.5
'   Results:—
                    Cells(1, 1) = "Bend Radius": Cells(1, 2) = Bend_Rad
                    Cells(2, 1) = "X_centre   ": Cells(2, 2) = X_centre
                    Cells(3, 1) = "Y_centre   ": Cells(3, 2) = Y_centre
                    Cells(4, 1) = "Z_centre   ": Cells(4, 2) = Z_centre
                    Cells(6, 1) = "Given Points"
                    Cells(7, 1) = "Point A(x,y,z)": Cells(7, 2) = Xa
                    Cells(7, 3) = Ya: Cells(7, 4) = Za
                    Cells(8, 1) = "Point B(x,y,z)": Cells(8, 2) = Xb
                    Cells(8, 3) = Yb: Cells(8, 4) = Zb
                    Cells(9, 1) = "Point C(x,y,z)": Cells(9, 2) = Xc
                    Cells(9, 3) = Yc: Cells(9, 4) = Zc

End Sub

rb1957 (Aerospace) · Answer 57 · 2009-07-23 12:43:07

from the direction cosines of AB and BC,
and the co-ords of A and C,
you know the line thru A (and C) normal to AB (and BC)
these two lines intersect at the centre (O),
and the radius is the distance OC.

IDS (Civil/Environmental) · Answer 58 · 2009-07-23 23:41:19

I have also had a go at simplifying the whole thing, and done away with all the rotations. The code below makes use of the fact that there are three similar right angled triangles that allow the distance from the tangent intersection point to the arc centre to be found from the known coordinates by simple ratios.

More than half the code in the new function is to check that the two tangent points are the same distance from the IP, and if necessary moving the further one so that they are.

The new function runs about 7 times quicker than the earlier one.

CODE

Function ArcCenT2IP2(TP1 As Variant, TP2 As Variant, IntP As Variant) As Variant

' Find the centre and radius of a circular arc defined by two tangent points
' and the intersection point of the tangents

    Dim DL1(1 To 4) As Double, DL2(1 To 4) As Double, DL3(1 To 3) As Double, DL4(1 To 3) As Double
    Dim MP(1 To 3) As Double, a As Double, b As Double, c As Double, d As Double
    Dim NDims As Long, i As Long
    Dim Arccen(1 To 2, 1 To 4) As Double
    Const MaxDiff As Double = 0.0000000001

    If TypeName(TP1) = "Range" Then TP1 = TP1.Value2
    If TypeName(TP2) = "Range" Then TP2 = TP2.Value2
    If TypeName(IntP) = "Range" Then IntP = IntP.Value2

    NDims = UBound(TP1, 2)

    ' Check that tangent points are equidistant from IntP
    For i = 1 To NDims
        DL1(i) = IntP(1, i) - TP1(1, i)
        DL2(i) = IntP(1, i) - TP2(1, i)
    Next i
    DL1(4) = (DL1(1) ^ 2 + DL1(2) ^ 2 + DL1(3) ^ 2) ^ 0.5
    DL2(4) = (DL2(1) ^ 2 + DL2(2) ^ 2 + DL2(3) ^ 2) ^ 0.5

    ' If not, scale the longer line towards the IP
    If Abs(DL2(4) / DL1(4) - 1) > MaxDiff Then
        If DL2(4) > DL1(4) Then
            ScaleFact = DL1(4) / DL2(4)
            For i = 1 To NDims
                DL2(i) = DL2(i) * ScaleFact
                TP2(1, i) = IntP(1, i) - DL2(i)
                Arccen(2, i) = TP2(1, i)
            Next i
            DL2(i) = DL2(i) * ScaleFact
            Arccen(2, 4) = 2
        Else
            ScaleFact = DL2(4) / DL1(4)
            For i = 1 To NDims
                DL1(i) = DL1(i) * ScaleFact
                TP1(1, i) = IntP(1, i) - DL1(i)
                Arccen(2, i) = TP1(1, i)
            Next i
            DL1(i) = DL1(i) * ScaleFact
            Arccen(2, 4) = 1
        End If
    End If

' Point A = TP1, B = IP, C = mid-point of TP1-TP2
'Find side lengths of triangle ABC, a, b, c
'Find coordinates of Point C, midpoint of TP1-TP2

For i = 1 To NDims
MP(i) = (TP1(1, i) + TP2(1, i)) / 2
DL3(i) = (MP(i) - IntP(1, i))
DL4(i) = (MP(i) - TP1(1, i))
Next i

a = (DL3(1) ^ 2 + DL3(2) ^ 2 + DL3(3) ^ 2) ^ 0.5
b = (DL4(1) ^ 2 + DL4(2) ^ 2 + DL4(3) ^ 2) ^ 0.5
c = DL1(4)

' Radius = bc / a
' d = (distance arc centre to IP)/a = Rc / ba
R = b * c / a
d = R * c / (b * a)

For i = 1 To NDims
Arccen(1, i) = IntP(1, i) + DL3(i) * d
Next i
Arccen(1, 4) = R

    ArcCenT2IP2 = Arccen
End Function

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

rb1957 (Aerospace) · Answer 59 · 2009-07-24 00:40:01

"check that the two tangent points are the same distance from the IP, and if necessary moving the further one so that they are."

well that's pretty redundant, 'cause they have to be, congruent triangles.

IDS (Civil/Environmental) · Answer 60 · 2009-07-24 07:16:10

Quote:
"check that the two tangent points are the same distance from the IP, and if necessary moving the further one so that they are."well that's pretty redundant, 'cause they have to be, congruent triangles.

If both the points are tangent points to the same circle then they will be eqidistant from the intersection point, but it's perfectly possible to enter points that are not equidistant from the intersection point, in which case the function would return the centre of a circle which was not tangential to either point, and you wouldn't have any idea about the mistake. By checking that the points are equidistant it picks up that potential error, and by reporting the coordinates for the adjusted point it gives an indication of the error.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

rb1957 (Aerospace) · Answer 61 · 2009-07-24 08:00:06

if the co-rods are entered in error, how can you correct them ? (other than by guessing) wouldn't it be better (if the tangency points aren't equidistant) to return an error code (rather than a radius which is probably not correct) ??

ginsoakedboy (Mechanical) · Answer 62 · 2009-07-24 14:41:31

Apologize if this is already mentioned, I haven't read thru all responses in detail.

Here is the simplest hand calc solution according to me:

1. |C^-X1^| = |C^-X2^| = |C^-X3^|
2. C must lie on plane described by X1, X2 and X3.

The above 2 conditions describe a problem with 3 equations and 3 unknowns.

Where
C^, X1^, X2^ and X3^ are the position vectors of centers and the 3 points respectively. C = [c1 c2 c3], X1 = [x11 x12 x13] ... and so on.

IDS (Civil/Environmental) · Answer 63 · 2009-07-24 19:47:10

Quote:

if the co-rods are entered in error, how can you correct them ? (other than by guessing)

By assuming that one point is a true tangent point and the other point lies on a tangent, but is not on the circle. From examining the coordinates of the adjusted point (which are in the second row of the function return array) it will be obvious whether this is a reasonable assumption or not.

Quote:

wouldn't it be better (if the tangency points aren't equidistant) to return an error code (rather than a radius which is probably not correct) ??

Depends on the application. It would be very easy to modify the code to do that. If you did you'd want to allow the passing of an acceptable error value (which would obviously depend on the units). For the application being discussed in this thread, which uses physical meaurements of a bent pipe, the accuracy would almost never match that required by the function, so it would almost always return an error, which wouldn't be very useful. You could easily re-write it to allow an error of up to some realistic value, with an adjustment of the further tangent point, and to return an error if the discrepancy in the tangent lengths was greater than that specified.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

DrMetal (Materials) · Answer 64 · 2009-07-24 21:31:56

Doug,
- On the error issue ... this is actually what we are dealing with. We are given the 3 pts by an outside Engr party. When checking first for both tangent lines being equal, they are not equal (in all 4 cases for us). What we did was to move the point of intersection (POI) in the direction that had the most effect on the length, and the least affect on the positon of the pipe in space. So the small errors are corrected mathematically very quickly, but with little physical effect on the structure. We are dealing very large diameters (22'R to 580'R), and relatively small circle arc lengths (only 6'L to 20'L). We found that some very slight movements or errors in the 3 point's coordinate positions can have very large affects on the bend raddii. For example, if the POI "height" above the arc changes very slightly (e.g. 1"), it can change the arc radius by several feet. This is because it is a very "flat" and short arc. For example, taking this to extremes, if you had a 10' arc length on a 10,000' radius, the POI almost rests right on the circle. Raise that POI by just 1" and see how mach that radius has to change to accomodate that. Again, thanks for all this great help. Mike P.

rb1957 (Aerospace) · Answer 65 · 2009-07-24 22:54:22

let's sign off ... the problem moved from 3 points on an arc to two points of tangency and their intersection to the real world problem of points close to being where they should be ... quite the journey.

with your dimensions i would've thought the problem was in accurately measuring the dimensions, i would've expected at a 3D CAD program (or even a surveying program) would have been able to accurately dimension the required geometry. i suspect the real problem is how to physically create the required shape, rather than how to mathematically define it.

DrMetal (Materials) · Answer 66 · 2009-07-28 17:55:40

RB,
- I agree .. I think we have beat this one to death. But to answer your question ... we have to define it before we can make it ... and making this shape is not a big problem ... we have bent the 1st two short pipes and can make them lay on a true cut template within 1/16". Accuracy is important in our case because these pipes are not just out in free space. They are attached to a heavy fabicated cradle system, with 2"T support plates every 24". The support plates have half-circle cutouts that "cradle" the pipes. The support structure with the cradle plates are all fabricated as an assembly first. Then the bent pipe has to lay into this 3D structure. So we need to do both ... DEFINE the curve, and then PRODUCE the curve to a fair degree of accuracy for all of this to come together properly. I could have said this early on, but didn't want to cloud the issue any more than it was. Thanks again, Mike P.

IDS (Civil/Environmental) · Answer 67 · 2009-08-17 21:28:35

Coincidentally I had a use for the Excel functions I posted earlier on a real job recently, so I have refined them, and also added two more, to find the circle centre and radius (in 3D) based on one tangent line and one other point on the circle, or a radial line and one other point.

More details and download from:
http://newtonexcelbach.wordpress.com/2009/08/18/finding-circle-centres-in-3d/

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

electricpete (Electrical) · Answer 68 · 2009-08-17 22:18:59

Dr. Metal is a way cooler handle than MikeyP

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

DrMetal (Materials) · Answer 69 · 2009-08-17 22:29:12

Doug,
- Thanks again for postings. Most helpful.
- By any chance do you have a couple of related formulas?

1) After determining the circle radius and center point, do you have an Excel solution to determine the coordinates of any other point on the circle given any one of the X-Y-Z coordinates of the unknown point, OR given the arc angle from another known point on the circle?

2) Knowing the coordinates of 2 points in 3D (i.e. a line in 3D), an Excel method to determine the coordinates of any other point on the line by just plugging in a distance along the line from one of the two known points? This is pretty simple with the distance formula. If you don't already have this written, I can write it. I thought I'd check first to see if you already have one done in Excel.

Thanks again,
Mike P.

IDS (Civil/Environmental) · Answer 70 · 2009-08-18 01:19:06

Mike - I don't have those functions right now, although the ptor (polar to rectangular) and rtop (rectangular to polar) functions would go most of the way to doing the calculation.

My plan is to split out the interpolation part of the IP spreadsheet, and add in additional geometric functions and line plotting functions, and make it a complete geometric tool-box, but I don't know when I'll get round to doing that.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Come Join Us!

Posting Guidelines

Related Projects

Find circle radius & circle center in 3D from (3) XYZ pts 5