7 Replies Latest reply on May 6, 2020 7:24 AM by John Forlow

    Map Social Distancing on a Floor plan

    John Forlow

      I am working on a side project for work that has to do with Social Distancing displayed on our Floor Plan.

       

      I want to show a 5 pixel radius around all of the seats on the floor plan.

       

      Here is what I have:

      A floor plan of each floor, using as a background image

      I have already mapped out each cube, office, and huddle room in the building. I am using polygons to place these on the grid. I also have a point for the occupant’s chair location.

       

      This works quite well. I can hover over and the tooltip tells me who sits in that cube, phone number, etc.

      I have had this going for about 9 months now.

       

      Now that things have changed in the world, I would like to be able to show how close people are to each other.

      Our cubes are 6ft * 7.5ft. which breaks down to about 26 * 35 pixels. This works out to 1 foot equaling 4.3 or 4.6 pixels. so rounding up, makes it 5 pixels.

       

      I would like to show a 6 foot circle around each persons chair. This is about 5 pixels on my grid overlay.

       

      Then I am going to identify groups of people that we think are going to go back to work on a given day.

      This could be all males/all females (this probably would not work as we have (by far) a higher female workforce)

      Odd/even cube numbers

      Break up into 5 groups by last name…

      What I want to see is the overlapping circles (or lack thereof)

       

      Any thoughts?

      The circles are not the problem, it is the size of them. How can I make the radius exactly 5 pixels?

      Should I use a specially created shape and import it into Tableau?

       

      Thank you very much

      John Forlow

        • 1. Re: Map Social Distancing on a Floor plan
          Ken Flerlage

          A shape might be an option with it sized appropriately, but shape sizing is hard to get right. I'd probably draw the circles using trig. Beyond "Show Me" Part 2: Trigonometry - The Flerlage Twins: Analytics, Data Visualization, and Tableau . Happy to help if you could share a sample packaged workbook. This definitely sounds like a good project. I'm thinking something like this could be of value at my place of business as well.

          1 of 1 people found this helpful
          • 2. Re: Map Social Distancing on a Floor plan
            John Forlow

            Ken --

             

            Thanks for your interest.

            I am creating a mock-up. I will post a .twbx later today.

             

            John

            • 3. Re: Map Social Distancing on a Floor plan
              John Forlow

              Ken --

               

              It took a little bit of time, but here is the Mock-up.

              I have included the Excel workbook and the twbx file and the floor plan image file.

              It is a simple 4 cube and an office setup.

              What I want to do now is draw circles around the Seat coordinates to show where the 6 foot radius would be and to see where the overlap is.

              I think in this example, the diagonal seats in the cubes are "OK". The ones side by side (vertically or horizontally) are within that 6 foot of each other.

               

              Some of the calculations and parameters can, and should, be combined. I was using them to validate my calculations.

              I have included a parameter for Scale (how many pixels are in 1 foot). In this example I am making 35 pixels = 1 foot

               

              Thanks for your thoughts and any help.

               

              John

              • 4. Re: Map Social Distancing on a Floor plan
                Ken Flerlage

                Sorry for the delay in responding to this. Just now getting a chance to look at it in detail. As noted above, We'll need to do some trig to draw these circles at the correct radius. If you haven't read the blog I shared earlier about trig, then I'd suggest taking a look at it first as I used all those concepts here. The starting point is that we need to use some data densification to draw the circles, so I created a new sheet in your Excel file called Densification with 50 points like this:

                 

                Densification Point
                1
                2
                3
                4
                5
                6
                7
                8
                9
                10
                11
                12
                13
                14
                15
                16
                17
                18
                19
                20
                21
                22
                23
                24
                25
                26
                27
                28
                29
                30
                31
                32
                33
                34
                35
                36
                37
                38
                39
                40
                41
                42
                43
                44
                45
                46
                47
                48
                49
                50

                 

                (Note: I'm a big fan of Century Gothic font too!!)

                 

                You then join that into your data using a cross join, which can be done using a 1=1 join calculation. This will duplicate your records 50 times so be aware of that and any impacts it might have.

                Now we create a bunch of calculated fields to do the math. I'm not going to write them all out here. I've put them into a folder called "Circle Drawing Calcs" in the workbook. These calculate the spacing between the angles (for our 50 points), the actual angle of each point, the radius of each circle (based on your scale parameter), then the x and y coordinates for each point. When then plot them using a polygon mark type like this:

                You can now clearly see that most of the cubes are actually okay, but there is some slight overlap between the ones that are next to each other horizontally. What this won't do is actually tell you where the overlap exists. That would require some more math, but it does allow you to visually find those cases.

                 

                See attached. If you have any questions, let me know.

                • 5. Re: Map Social Distancing on a Floor plan
                  Charles Saulnier

                  Hi John,

                   

                  Sorry if this adds nothing to the conversation, but I was wondering what tool do you use to extract the pixel locations from your image? Looks like the screen-capture tool I use (Snagit) does not have anything handy to do this.

                   

                  Cheers and great work from you and Ken on this topic!

                   

                  Charles

                  • 6. Re: Map Social Distancing on a Floor plan
                    Ken Flerlage

                    You could use just about any image editing software. I use www.getpaint.net for example. Another option is the Interworks Drawing Tool for Tableau.

                    1 of 1 people found this helpful
                    • 7. Re: Map Social Distancing on a Floor plan
                      John Forlow

                      Hi Charles –

                       

                      My floor plan at work contains about 900 locations. These can be cubes, offices, huddle rooms, and desk out on the floor.

                      I wen old school to start off with and just eyeballed it.

                       

                      I started by adding the image as a background in Tableau, then zoomed in looking at a corner of a cube.

                      It will not be a crisp solid line, but you can make an educated guess.

                       

                      I decided to plot the interior of the cube (not where the actual walls are).

                      Since I have the axis set on, I can determine the x and y coordinates.

                      I did this for one cube to finding each of the 4 corners of the cube. Now I know what one cube looks like.

                      I then put this information into Excel. As points for the polygon.

                      Now on to the next cube. (mind the gap… the wall between the cubes)

                       

                      As you would imagine, doing this manually for 900 locations would be very time consuming.

                      So… I created an Excel macro that would calculate the positions for me.

                      I knew the height and width of one cube, so I was able to calculate the points of the cube next door.

                      I even had the macro “calculate” the cube numbers (we have a sequential pattern).

                      The macro even creates blocks of cube location 2 wide and as many long as you want.

                      Save the Excel spreadsheet.

                       

                      I kept the Tableau workbook open and refreshed the data source. All of the new cube polygons should appeared. Rinse and repeat with the next set of cubes.

                      You will start to see a pattern as the y coordinate for the starting point of all of your cubes should be the same, only the starting x should change. Using the macro to calculate really saved me time in the long run.

                      Likewise, when you move down a block of cubes to the next block,  the x coordinate should not change.

                       

                      Good luck.

                      John

                      1 of 1 people found this helpful