Build a Social Distancing Floor Plan in Tableau


A couple weeks ago, I stumbled upon an interesting question on the Tableau Community Forums. John Forlow, the original poster, had been hearing about the need for social distancing with the COVID-19 pandemic and wondered how he might be able to analyze the adherence to these guidelines at his own workplace. The goal was simply to provide a visual starting point for facilities to think through their back-to-work schemes. John had an image of his company’s floor plan, complete with the location of each desk chair, so he wanted to draw circles around each chair to show which locations are less than 6 feet from another location. I thought this was an interesting idea so I helped him to use trigonometry to create the visual. After helping John create this, I decided to publish an example on my Tableau Public page. So I found an office floor plan online1 and created my own version.


I then decided to share on social media and it kind of blew up, garnering a lot of interest. Many people expressed a desire to create similar floor plans for their own offices, so I decided to write a blog showing how to build it. I’ll get to the how-to in a moment. But, before that, I received some fantastic advice from four-time Tableau Zen Master, Bridget Cogley and public health data viz expert, Amanda Makulec (see their bios below) so I’ve invited both of them to join me on this blog to provide their thoughts and to discuss potential ethical concerns of building this for your own office space. The rest of this blog, prior to the how-to section, will be a combination of Amanda, Bridget, and I, so you’ll see an indicator of who’s writing before each paragraph or group of paragraphs.

First, a little about our two guests:

Bridget Cogley is an interpreter turned analyst. She brings an interdisciplinary focus to data ethics and semantics in data visualization. She focuses on ethics, data literacy, effective data communication, and functional aesthetics in design and regularly shares her knowledge on her blog, Tableaufit.com. She has been a Senior Consultant for the last 6 years and is a 4-time Tableau Zen Master. She also has experience in American Sign Language interpreting, training, HR, management, and sales support.

Amanda Makulec is the Data Visualization Lead at Excella. She has created and taught about data visualization for more than a decade. She holds a Masters of Public Health and spent eight years working in evaluation, research, and health information systems around the world. In recent months, she has written and spoken about the need for responsible visualization of COVID-19 data. Amanda is also the Operations Director at the Data Visualization society, which “aims to collect and establish best practices, fostering a community that supports members as they grow and develop data visualization skills.” For more information or to join, see https://www.datavisualizationsociety.com/join.


Ken: I’ll let Bridget share her thoughts in a moment, but one key point she made was that this type of visual only scratches the surface of what will be required to transition offices into safe working environments. Other factors are also quite critical—the movement of people around the office, narrow hallways, the usage of shared spaces and resources (conference rooms, copiers, restrooms, water coolers, refrigerators, etc.), air movement and ventilation, usage of masks (and ensuring that masks are actually used properly and consistently). Physical distance is only one such factor that must be understood before office spaces can be considered safe working environments.

Bridget: An additional challenge with much of this data is that there are so many unknowns and we have a very natural human desire to return to what waswhat is comfortable and feels “normal.” As we make these visualizations, it’s key to consider the ethics involved and the risks in how this will be used, as well as to provide guardrails to our users. The more proactive we are with incorporating ethics and clarifying uncertainty, the better equipped those using this analysis will be. As we examine what returning to the office looks like, we need to be careful not to choose habit over reason and we must consider all of the data that is missing.

We’re also seeing a lot of discussions about how effective physical distancing is within closed, confined spaces. The CDC recently published a paper, COVID-19 Outbreak Associated with Air Conditioning in Restaurant, which examined how distance, in closed spaces for prolonged periods of time, may not be enough due to ventilation and other factors. Additionally, it is not entirely clear who is susceptible and many families are without resources for childcare. This is a new diseasewhen epidemiologists refer to COVD-19 as “novel coronavirus”, that literally means that it is a new strain of coronavirus. So much is simply unknown and will remain unknown for a long time, so we must be careful when making decisions, even when those decisions are “data-informed.”

Amanda: A theme of analysis and decision-making around COVID-19 seems to be unknowns. What are the known unknowns? What are the unknown unknowns? Bridget points to a number of different concerns around the complexity of how to maintain a safe workspace that goes far beyond seating arrangements. Modern workspaces can make this even more complex: what about offices where “hotdesking” (not having a fixed desk) is common and each desk is occupied by a different person each day? What other requirements are being put in place around personal protection, like wearing masks? How are chairs arranged in conference rooms?

This type of visualization is a great template as a starting place for people rethinking their office seating arrangements. Reflecting on just how close runners and other passers-by come to me and my stroller while taking walks, I don’t think we have a great gut feel for just how wide a six foot radius is to create that protective bubble. I imagine office managers may be surprised to find how hard it is to create a safe distance for everyone in the office. This visual does help to tell that story.

Similar to re-opening the economy, we’ll only see office workplaces re-open and have employees agree to come back to work if they have confidence that their office managers and leadership are taking steps to protect the health and wellness of employees. Seeing this kind of visualization adapted for my office would be one piece of a puzzle needed to instill that confidencebut it would fall flat if I didn’t see a whole package of other measures and interventions to manage physical distance and promote healthy behaviors, like those Bridget recommended. Plus, how often do we move our chairs and roll a foot or two one way or another? It’s likely that, to maintain a 6 foot minimum distance following typical desk-sitting/standing behavior patterns, we would need to pad in extra space as a starting point.

Looking at research emerging around how the virus spreads, our best protection must include widespread testing and contact tracing to understand who may have been exposed to those who test positive. This “test-trace-isolate” approach has greater potential to limit the spread of COVID-19 than adjusting where chairs are located throughout an office. As data visualization designers, we have a responsibility to think about how our charts and maps could be misinterpreted or misused, including visual tools like this map that could inform reopening an office.

Ken: I’ve been lucky to have received feedback from Bridget and Amanda in the past and, as always, their feedback this time was spot-on. To help address some of their points, I added a disclaimer to the top of my visualization. And, since you’re now a captive audience, I just want to be sure to state this once again on this blog. This type of mapping, if you choose to do it yourself, should only be seen as just a starting point. If your goal is to make an argument for why it’s okay to return to work, then this type of visual is a poor way to make that argument. This exercise simply allows you to get an idea of how social distancing might look in your office—nothing more, nothing less. In fact, I think it’s quite likely that many office spaces will look very similar to my example, where the vast majority of locations do not maintain adequate social distancing, even without considering the factors of movement and typical uses of space. And, hopefully, that will lead to better conversations about how we begin to reopen our offices as well as consideration of other options such as staggered days onsite or continued work from home.

Before getting to the how-to, I want to note one final bit of feedback Bridget provided. I had initially used blue to indicate locations that adhere to social distancing guidelines, but Bridget argued that blue, in this context, seemed to indicate “safe” or “good.” Given what we discussed above, this is not a great way to describe them. There is still risk associated with these people being physically in the office. Thus, I changed the visualization to use orange instead of blue. So, if you choose to build one of these for your own office, I’d also encourage you to be diligent about your colors and the meanings typically associated with them.

Helpful Resources
Before showing how to build a social distancing floor plan, here are a few resources that we highly recommend reading, which deal with some of the topics discussed above.

The Ethics of Visualizing during a Pandemic - Article by Bridget Cogley, on the ethical implications of visualizing data during a time of fear and social upheaval.

Recommendations for Adding a Disclaimer - Article by Amanda Makulec, on the Tableau blog, which discusses how to write a good disclaimer for COVID-19 related visualizations and includes some valuable sample text.

A Data Designer’s Responsibility During a Global Crisis - Article by Tobias Van Schneider which considers our responsibilities in a pandemic, including how our work could be misused.

The Risks - Know Them - Avoid Them - Article by Erin S. Bromage, Ph.D., Associate Professor of Biology at the University of Massachusetts Dartmouth, which discusses many of the additional factors we mentioned above.

How To
OK, now let’s talk about how to build this. It will all start with a map of your office floor plan. Ideally, your map will include the specific locations of each desk and chair as that chair location will be the center point of the circle we draw. Once you have this, you’ll need to capture the x and y coordinates of each chair location (If you’re lucky enough to have geolocation data, your job becomes a little easier, but I won’t be dealing with that here). Unfortunately, capturing these coordinates is a manual process. To do this, I opened my floor plan image in a free graphics editor, Paint.net. Another option would be to use Dave Hart's Drawing Tool for Tableau, which I’ll show below. Open your image, then place your cursor over the center of each chair. The tool will show you the x and y coordinates.


If you click on that point, it will be recorded in the "Point Data" section on the bottom left. Once you've clicked all the locations, you can copy the data from this section into a spreadsheet (I named the worksheet tab Occupants). I chose to create a sequential ID for each chair and include a descriptive Name.


Repeat this process until you’ve collected the coordinates of every chair. This can be a bit time-consuming, but once you get the hang of it, it goes pretty quickly.

In order to draw the circles to an exact radius, we’re going to require some data densification, so create a second sheet in your spreadsheet called Densification. This should have one column called Densification Point with values from 1 to 50.


Note: We’re going to use these points to draw an approximation of a circle. If you are not familiar with the concept of data densification or how you can use it to draw circles, then I’d highly recommend that you first read my previous blogs, An Introduction to Data Densification and Beyond Show Me:Trigonometry.

Now we’ll connect to this spreadsheet in Tableau. We’ll add both tables and perform a cross-join. We can mimic this by using a 1=1 join calculation:


This will match each row from Occupants to each row from Densification, essentially duplicating our data 50 times. We could do this using bins, as detailed in my introduction to data densification, but I prefer this method since it’s more straightforward. Of course, this duplication of your data could be a problem for large data sets, but that’s highly unlikely for our floor plan use case.

Next, we’ll create two parameters. The first, Distance Requirement, will contain the number of feet (or meters or whatever measure you prefer) we wish to use as our social distancing guideline. I’ll be using 6 feet but you may wish to increase that in order to force a larger buffer (as discussed by Amanda above). In fact, I’d strongly recommend adding a buffer as this 6 foot distance assumes that a person is a single point in the middle of the bubble, which is obviously not true. So, to account for the fact that people take up some space, I’d probably recommend increasing the distance to 7 or 8 feet. 

The second parameter, Pixels Per Foot, will indicate the number of pixels that represent a foot on our image. Unfortunately, this could be a bit tricky. If you’re lucky, your image has a key that indicates what constitutes a foot:


If so, use a graphics tool or the Drawing Tool for Tableau to capture the x coordinates for 0 and 1 feet, then do the math to get the number of pixels. If you don’t have such a key, it’ll be more difficult. Your best bet will be to find some object for which you have a known width or length, such as a desk or table. Then, like the key, you’ll determine the number of pixels then do the math to get the pixels per foot. Once you have this measurement, record it in the Pixels per Foot parameter.

Now we’ll create a handful of calculated fields needed to draw the circles around each chair location.

Angle Spacing
// Angle spacing between points.
360/({FIXED : MAX([Densification Point])}-1)

Angle
// Angle for the given point
([Densification Point]-1)*[Angle Spacing]

Radius
// Radius for all circles (i.e. 6 feet is the diameter).
[Pixels per Foot]*[Distance Requirement (Feet)]/2

Note: With a diameter of 6 feet, the radius will be 3 feet. You may be asking why the radius is not 6 feet. Each bubble will have a radius of 3 feet, so when one bubble touches another, those two locations (at the center of each bubble) will be exactly 6 feet apart. When those circles intersect, the distance will be less than 6 feet. So, the 3 foot radius allows us to visually see those intersections.

Circle X
// X coordinate for the circle.
[Seat X]+([Radius]*COS(RADIANS([Angle])))

Circle Y
// Y coordinate for the circle.
[Seat Y]+([Radius]*SIN(RADIANS([Angle])))

Now drag Circle X to the columns shelf and Circle Y to the rows shelf and make them both continuous dimensions (no aggregation).


Now change the mark type to “Polygon”. Then drag ID to the detail card (so that we get a separate mark for each ID), and drag Densification Point to the path card, making it a continuous dimension. This will define the order that we connect the points.


Don’t worry if your circles look more like ovals at this point—that’s because our scale is a bit off, but we’ll fix it shortly.

Next, use the Map menu to specify a background image. Select your floor plan image. Use Circle X for your X Field and Circle Y for your Y Field. Set “Right” to the width of the image and “Top” to the height of the image.


On the options tab, ensure that “Lock Aspect Ratio” and “Always Show Entire Image” are both selected.

You should now have six-foot diameter circles around each chair location.


Finally, you can color the locations based on whether or not they intersect with another. To do this, manually select those locations, create a group or set, and use that group/set on the color card. I considered doing the math to find all such intersections, but that ultimately felt unnecessary because you can easily see those intersections visually. Furthermore, that math wouldn’t be able to account for the existence of physical walls which would make distance largely irrelevant.

And that’s the process. There are definitely a fair number of manual steps required, but unless your office space is very large, it doesn’t take too long to create. If needed, feel free to download my Tableau Public workbook.

If you decide to create your own social distancing floor plan, please remember everything we discussed earlier. This is just a starting point for understanding how to maintain proper social distancing within our offices. It is absolutely critical that this be just one of numerous factors that you consider regarding your workplace’s readiness for employees’ return.

Thanks for reading! And a huge thanks to Bridget and Amanda for your feedback and words of wisdom.

Ken Flerlage, Bridget Cogley, Amanda Makulec May 13, 2020

Update May 28, 2020: Sarah Battersby, a cartography-focused research scientist at Tableau, has taken this basic concept and developed an alternative method using maps. What's great about her approach is that, with maps, we are able to leverage buffer calculations to draw the circles, using simple calculations, instead of complicated trigonometry. Additionally, we are able to use Tableau's intersection functionality (built into joins) to automatically find where the bubbles intersect with each other. So, please be sure to check out her method as well. 


1. Creative Commons Office Floor Plan by Marco Garbelini, used under CC BY; Modifications by Ken Flerlage in order to make the floor plan more generic and to fill in unused spaces.

23 comments:

  1. Would the buffer calculation not work in this instance in place of the radius?

    ReplyDelete
    Replies
    1. Yes! You'd need to convert the cartesian coordinates (x,y) into some sort of geographical coordinate system since the buffer calculations only work with geographic data. But, if you could do that, then buffer calculations would work. In fact, they'd make it a lot easier. Keep an eye out--I believe someone is working on exactly this, so if that comes to fruition, I'll be sure to include a link to it on this blog.

      Delete
  2. Can we make it a live employee tracking instead of just fixing the chair positions ?

    ReplyDelete
    Replies
    1. I think that would require some additional technology. There would have to be some way to track people's movements throughout the office. And I think that would introduce some ethical dilemmas.

      Delete
  3. I am calculating pixel per foot using formula PPI = diagonal_per_pixel/diagonal_per_inch. diagonal_per_pixel = square_root(width*width + height*height) and then converting diagonal_per_pixel to diagonal_per_inch using online converter. Finally I am coverting PPI to PPF using online converter to get Pixel_Per_Foot. Is this the correct way of doing this because I am not getting same output when adding CircleX and CircleY to view. I am taking care of changing them in view to non-aggregate and dimension. All I am getting is big round circle which is made up of many small circles.

    ReplyDelete
  4. After the step where we add CircleX and CircleY and make them non-aggregated dimension all I am getting is a big round circle made-up of very small circles. PPI = diagonal_in_pixel/diagonal_in_inch , diagonal_in_pixel = square_root(width*width + height*height). Converting diagonal_in_pixel to diagonal_in_inch using online converter. Then using online converter I am converting PPI to PPF to get pixel Pixel_Per_Foot.

    ReplyDelete
    Replies
    1. Could you email me at flerlagekr@gmail.com?

      Delete
    2. Yeah Ken. I have sent you mail please check.

      Delete
  5. Hi Ken,

    I want to calculate if circles are intersecting with each other to find out the safe and unsafe seats, as i am taking social distancing requirement in the input. Not able to figure out how to dynamically calculate the intersecting circles, can u please help?

    ReplyDelete
    Replies
    1. I didn't build that into this because the math is tricky. However, if you Sarah Battersby's approach (https://community.tableau.com/s/news/a0A4T000002NGVbUAO/tableau-distance-and-buffer-calculations-to-build-dynamically-update-a-socia), you'll see that using maps allows you to do this in a more automated way.

      Delete
    2. Hi Ken-this is truly amazing and an eye-opener. Many thanks for sharing. I am definitely interested in the “tricky” dynamic calculations for the intersecting circles, especially on custom images when maps/long/lag are not possible.
      Would you be able to provide the calculations for detecting intersecting circles?

      Delete
    3. I haven't actually looked into this, so I don't have a good answer for you at this point in time.

      Delete
  6. Hi Ken, Thanks for replying on mail. The only question I have now is what are the values of CircleX and CircleY you set while adding Background image. Circles are getting little bit shifted from exact chair location in my workbook.

    ReplyDelete
    Replies
    1. It's hard to troubleshoot in this comments section. Any chance you could send me an email? flerlagekr@gmail.com

      Delete
  7. Thanks for posting this, its very helpful. I have a question. We have a bunch of chairs on the discussion table. Instead of showing all doesnt meet social distancing norms, can we show a few chairs which meets social distancing and is free to use? here it is recognizing only individual seats as safe to use. Rather even though there are 10 seats nearby, one or two seats in that cluster meets social distancing requirements. Is there a way to highlight it

    ReplyDelete
    Replies
    1. That's a great point. In my workbook, I've just manually colored them. You could do something like that, choosing which to color blue and which red.

      Delete
  8. Hi Ken,

    This post is very relevant and useful in this scenario. Using your dashboard as a base and enhanced it to next level. Now in the same dashboard users can select the seats and depending on the distance between the seats and selection the color coding will change dynamically. Thanks for providing the base.

    ReplyDelete
    Replies
    1. That's great. How did you do the dynamic coloring?

      Delete
    2. How did you do the dynamic coloring? Or calculate the number of intersections automatically?

      Delete
  9. Hello, new to this forum.
    I'm involved with helping interior Designers study for and obtain the NCIDQ Certicifation through the NCIDQ Exam: We are an independant organization not directly affiliated with the Exam developers and providers so always seek out eamples of knowledge topics, especially visual ones, on which the test tests.
    And the example you've uesed here on social distancing is a perfect example of "data visualization," one of the newer topics that the test addresses.
    We'd like a reach-out to discuss being able to use it in our program.
    If you can contact me so we can take this off-line, I'd appreciate it very much.
    Thank you, david.

    ReplyDelete
  10. Hi. I am working on a similar project and have a question. I have a floor plan of a retail store with various product categories. I am overlaying revenue and quantity measures by product category per map location. This working well. The issue I am having is as soon as I had a date filter (my data is broken out by 4 days), the viz triggers a "aggregate measures". are there general guidelines in terms of the level of detail I should work within or are there data preparation approaches to the data to avoid auto aggregation yet still have the ability to add filters? My last question is, i'd like to also use a physical relationship connection to another data set. The secondary data set is at the customer level -- I;d like to show if certain product categories are purchased by a certain type of customer (say age). As soon as I create the relationship and drop in the customer type attribute, things aggregate automatically and I lose the map. Sorry for the long post... I can't find answers anywhere..

    ReplyDelete
    Replies
    1. This is difficult to answer via this comment section. Would you be able to send me an email? flerlagekr@gmail.com

      Delete

Powered by Blogger.