Posted: 2018-05-18

Automated design collision detection

Keywords: VBA, collision detection, design check, RPA, civil engineering

Civil engineering projects are huge endeavours, they require massive resources, cooperation of people from different fields and tons of designs. Because of these traits, CE projects tend to get really complex. This inherit complexity, leads to errors at all stages of the projects construction.


When it comes to civil engineering as of 2018 the name of the game is "value engineering"; this means building as fast and cheap as possible, while still making a profit. Add to it the utter failure of computerization in the XX and XXI century, which got confused bureaucratization; and you end up with poor designs plagued with errors, tight construction schedules, undermanned teams, and guestimated budgets; in other words total shitshow.

Thanks to these initial conditions, modern construction sites are full of "innovative" design solutions. Unfortunately due to the lack of experience when designing such wonders, accidents happen, things which work on paper - cannot be easily constructed in reality.

Figure 1. Typical expressway normal cross-section

Figure 1 presents an expressway cross-section; complex enough to look correct; not complex enough to foreshadow the upcoming problem (for that you need experience and knowledge which comes with time, and isn't as common amongst designers as common sense would suggest; big shoutout to my supervisor Sebastian Czarnik for sharing his experience and knowledge with me). Beneath a simplified isometric view of the same cross-section for those not in business/majority of designers ( ͡° ͜ʖ ͡°).

Figure 2. Drainage pierces the embankment reinforcement

Figure 3. Drainage doesn't pierce the embankment reinforcement

Earth reinforced structures rely on geosynthetics, long story short; as long as geosynthetics stay continuous they fulfil their role. Moreover roads and embankments are build from bottom to top. Knowing this two facts and looking at the Figures 2 and 3 we can start to understand the problem.


Check for collisions between the road's drainage system and the embankment reinforcement in an automated/semi-automated fashion.


Using the profile data of the drainage (chainage, well bottom level) and embankment cross-section (chainage, reinforcement top level), we can compare their relative position; in places where the well bottom lies below the reinforcement we most probably have a collision.

Figure 3. Collision check in action

Code grimoire

Code used to target collision between branches; namely road's and drainage.

Mod operation

Modulo operation returns the remainder after division.

Function Modulo(a As Double, b As Double) As Double
    Modulo = a - (b * Fix(a / b))
End Function

Chainage to chainage convention conversion

Converts standard Excel numeral data to the X+XXX engineering convention chainage format.

Function get_chainage(dist As Double) As String
    Dim result As String
    Dim rest As String

    rest = Modulo(dist, 1000)

    If Len(CStr(Fix(rest))) = 3 Then
        rest = rest
    ElseIf Len(CStr(Fix(rest))) = 2 Then
        rest = "0" & rest
    ElseIf Len(CStr(Fix(rest))) = 1 Then
        rest = "00" & rest
        rest = "000"
    End If

    If dist >= 1000 Then
        result = CStr(Int(dist / 1000)) & "+" & rest
        result = "0+" & rest
    End If

    get_chainage = result
End Function

Collision detection

The code beneath runs thru all the manholes getting their respective chainage x and level h. Then searches for the nearest cross-sections of the embankment and compares the level of the embankment reinforcement with the drainage level. If there are any collisions (the drainage lays below the reinforcement) we flag this well for further inspection.

Sub detect_conflics()
    Dim mh As Worksheet
    Set mh = ThisWorkbook.Sheets("manholes")
    Dim rt As Worksheet
    Set rt = ThisWorkbook.Sheets("reinforcement")

    Dim j As Integer
    Dim i As Integer
    Dim x1, x2, xk, h1, h2, hk As Double
    Dim dist1, dist2, max_cs_dist As Double
    Dim terminate As Boolean

    'since not all road cross-sections are avaible we'll limit
    'ourselfs to those that are less than 50m from the manhole
    max_cs_dist = 100

    mh_limit = mh.Range("A1048576").End(xlUp).Row 'gets the mahole list size
    rt_limit = rt.Range("A1048576").End(xlUp).Row 'gets the reinforcement CS list size

    'runs thru all the manholes
    For i = 2 To mh_limit Step 1
        terminate = False

        j = 2

        hk = mh.Cells(i, "C").Value 'drainage well bottom level
        xk = mh.Cells(i, "A").Value 'drainage well chainage

        'checks if there are any road cross-sections above and below the manholes' chainage
            j = j + 1
            x = rt.Cells(j, "B").Value 'embankment cross-section chainage
            'flips the termination value if there is no data about the manholes' surroundings
            If j = rt_limit + 1 Then
                terminate = True
                Exit Do
            End If
        Loop While x < xk

            h1 = rt.Cells((j - 1), "C").Value 'reinforment top level before the well
            h2 = rt.Cells(j, "C").Value 'reinforment top level after the well

            x1 = get_chainage(rt.Cells((j - 1), "B").Value)
            x2 = get_chainage(rt.Cells(j, "B").Value)

            dist1 = Abs(rt.Cells((j - 1), "B").Value - xk)
            dist2 = Abs(rt.Cells(j, "B").Value - xk)

        'flips the termination if the cross-sections are too far from the manhole
        If dist1 > max_cs_dist Or dist2 > max_cs_dist Then
            terminate = True
        End If

        'if the termination wasn't flipped it evaluates the
        'wells bottom level for a given manhole vs the reinforcement
        If terminate = False Then
            'possible collision
            If h1 >= hk Or h2 >= hk Then
                mh.Cells(i, "D").Value = "collision between cross-sections " _
                    & x1 & " h=" & h1 & "m a " & x2 & " h=" & h2 & "m"
                mh.Range("A" & i & ":H" & i).Interior.Color = RGB(242, 12, 12)
                mh.Cells(i, "E").Value = "collision"
                mh.Cells(i, "F").Value = x1 & ", level=" & h1 & "m"
                mh.Cells(i, "G").Value = x2 & ", level=" & h2 & "m"
                mh.Cells(i, "H").Value = WorksheetFunction.Max((h1 - hk), (h2 - hk))
            'no collision
            ElseIf h1 < hk And h2 < hk Then
                mh.Cells(i, "D").Value = "between cross-sections " _
                    & x1 & " h=" & h1 & "m a " & x2 & " h=" & h2 & "m"
                mh.Cells(i, "E").Value = "no collision"
                mh.Cells(i, "F").Value = x1 & ", level=" & h1 & "m"
                mh.Cells(i, "G").Value = x2 & ", level=" & h2 & "m"
            End If
        'some wells are out of the listed cross-section
        'range and their state is unknown
            mh.Cells(i, "D").Value = "out of reinforcement check range"
            mh.Cells(i, "E").Value = "indeterminate"
        End If
    Next i
End Sub


This kind of automation enables for fast design evaluation, additionally cutting down the time of mundane and tedious work which is so hated by engineers. With the rise of BIM and RPA, it should find a permanent place in the engineers tool belt.

However, this kind of design evaluation cannot beat engineering experience, since the two are like apples and oranges, incomparable by nature. Enter the modern centaur - engineers backed by computers.