Consulting

Results 1 to 9 of 9

Thread: Solved: Triangulation help needed.

  1. #1
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location

    Solved: Triangulation help needed.

    I need to calculated the height at intermediate grid co-ordinates where I have 3 sets of enclosing co-ordinates and heights.

    Formula or UDF solutions welcome!
    Last edited by mdmackillop; 12-09-2009 at 11:17 AM. Reason: X1, X2 co-ordinates corrected.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  2. #2
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    I've also posted in a Maths forum
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    I know nothing about 3d geometry, but I used this site to create this function (not a very elegant one, I know):
    [vba]Function HeightAt(x4, y4, x1, x2, x3, y1, y2, y3, z1, z2, z3)
    A = y1 * (z2 - z3) + y2 * (z3 - z1) + y3 * (z1 - z2)
    B = z1 * (x2 - x3) + z2 * (x3 - x1) + z3 * (x1 - x2)
    C = x1 * (y2 - y3) + x2 * (y3 - y1) + x3 * (y1 - y2)
    D = -(x1 * (y2 * z3 - y3 * z2) + x2 * (y3 * z1 - y1 * z3) + x3 * (y1 * z2 - y2 * z1))
    HeightAt = -((A * x4 + B * y4 + D) / C)
    End Function[/vba] This gave results that are very credible. I was pleased to see that the estimates I made of those heights before I saw the results were very close indeed to the heights calculated.

    See attached.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    I'm not sure what you are looking for.
    Is this an accurate restatement of the problem?

    Given three points that define a plane, and given an X coordinate and a Y coordinate.
    What is the Z coordinate such that (X,Y,Z) in that plane?

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Thanks P45, I'll give it a go.

    Mike,
    A much neater explanation!
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    Here's my approach,
    we are given 3 points (P1, P2, P3)
    (These are vectors, each Pi has 3 coordinates)

    We have a (partialy) unknown V =(given X, given Y, unknown Z)

    Calculate
    Q1 = P1-V
    Q2 = P2-V
    Q3 = P3-V

    Then take the inner products
    Q1Q2
    Q1Q3
    Q2Q3

    And divide by the product of the lengths of Qi, Qj to give us the cosines of the angles between Qi and Qj

    (Q1Q2) / Len(Q1) / Len(Q2)
    (Q1Q3) / Len(Q1) / Len(Q3)
    (Q2Q3) / Len(Q2) / Len(Q3)

    Sum the arcsines of these values.

    If V is interior to the triangle P1 P2 P3, i.e. lies in the plane defined by the 3 points, then the sum of those arcsines = 360°

    So use Solver to find the Z value that will make that sum = 360
    (Edit: Corrected this line and attached spreadsheet)

    This method has significant round-off error. On one test, Excel returned .998 when 1 was the answer.
    Last edited by mikerickson; 12-11-2009 at 10:23 PM.

  7. #7
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    I've found a more robust approach. It
    a) does not require that X be interior to the triangle.
    b) avoids dividing by lenghts (a.k.a round off error)

    It utilizes the cross-product and uses Solver to find the Z, such that Q3 • (Q1 X Q2) = 0

    If the Button doesn't work, Solver can be called manualy or the Solver Add-in should be active in the VBE Tools>References.

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    Doing this as a UDF is another way.

    I set it to pass ranges, but you can easily change that or make it a sub,


    [vba]
    'ref: http://www.jtaylor1142001.net/calcja...nes/VP3Pts.htm
    'P,Q,R as 3 Col by 1 Row, X as 2 Col, 1 Row
    Function Triang(P As Range, R As Range, Q As Range, X As Range) As Variant
    Dim i As Long
    Dim Z As Double

    Dim PQ(1 To 1, 1 To 3) As Double, PR(1 To 1, 1 To 3) As Double, PQxPR(1 To 1, 1 To 3) As Double
    Dim Coeff(1 To 1, 1 To 3) As Double, D As Double

    On Error GoTo NiceExit
    'get vectors PQ and PR
    For i = 1 To 3
    PQ(1, i) = Q(1, i) - P(1, i)
    PR(1, i) = R(1, i) - P(1, i)
    Next i

    'Normal vector to PQ and PR via cross product
    PQxPR(1, 1) = PR(1, 2) * PQ(1, 3) - PR(1, 3) * PQ(1, 2)
    PQxPR(1, 2) = PR(1, 3) * PQ(1, 1) - PR(1, 1) * PQ(1, 3)
    PQxPR(1, 3) = PR(1, 1) * PQ(1, 2) - PR(1, 2) * PQ(1, 1)

    'use first point
    D = 0#
    For i = 1 To 3
    D = D + PQxPR(1, i) * P(1, i).Value
    Next i

    'solve aX + bY + cZ = D for Z, given X and Y (passed from X range), a,b, and c from cross product, and D solved above
    With X
    Z = D - (PQxPR(1, 1) * .Cells(1, 1)) - (PQxPR(1, 2) * .Cells(1, 2))
    Z = Z / PQxPR(1, 3)
    End With

    Triang = Z

    Exit Function
    NiceExit:
    Triang = CVErr(xlErrNA)
    End Function

    [/vba]

    Paul

  9. #9
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Thanks all,
    I've gone with P45Cal's solution, as I need to finish this project. I'll check out the other's in detail. I'm attaching part of the final solution incorpporating the Function do demonstrate how it was used.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •