2 Replies Latest reply on Jan 11, 2018 4:51 AM by Michael Hesser

    Building a Contour Graph ("Doppler Map") on a Shoestring Budget

    Michael Hesser

      For the longest time I've been working on solving a problem, but I didn't know how to explain my issue, much less begin to solve it.

      In a nutshell, I wanted to be able to display what happens when you "add" two or more three dimensional objects. For example:

      What happens when I combine THIS with THIS?

      I knew, from brute-force trial-and-error, that it wasn't as simple as stacking them:

      What I was after was a simple contour graph. Contour graphs show points (via line or color) that are on the same level. Doppler Maps are type of contour graph:

            Doppler map courtesy of NWS Enhanced Radar Mosaic: Pacific Southwest Sector

       

      But how on earth would I create this graph? I knew I had several problems:

      #1 I was going to need data points-- the more the better. Think of these points, together, as a pliable bedsheet.

      #2 I wasn't sure how I would record my Peaks, The Peaks might represent house cats who insist upon darting under the covers when you're making the bed.

      #3 I needed to know how to color those data points, or rather compute the elevation of any point on the bedsheet, especially when I had more than one cat under there.

       

      #1 Generating the Data Points

      I knew I needed a TON of data points, but I also knew I wanted Tableau to do most of the heavy lifting. I remembered a fantastic trick I learned from kettan about using a CROSS JOIN to generate cells. You can find details here: Re: CROSS JOIN with Tableau's join dialog . Please check this out-- you won't be sorry!

       

      I created two files that were practically identical, an X File [cue spooky music] and a Y File. Both included numbers 1 through 50 and a "Linker"

      By cross-joining these two relatively tiny tables, I could immediately generate 2,500 (50x50) unique X/Y values! My "bedsheet" problem was solved.

      I could have used more values if I wanted to increase my resolution, but a 50x50 grid was sufficient for my purposes.

       

      #2 Recording the Peaks

      Once I knew I would be working with a 50x50 grid, the next step was fairly easy:

      I created a third file with 4 columns:

      X LocationY LocationHeightLinker
      11135Link
      352110Link

       

      Here the [X Location] and [Y Location] mark the X/Y coordinates of the peak.

      [Height] represents the elevation of the peak, but it could just as easily represent temperature, output, or any other key measurement.

      The [Linker] field allows us to cross-join each peak with every generated X/Y coordinate.

      Important: this turned out to be a very tiny file! I'm just recording unique "peaks"-- nothing more.

       

      #3 Generating the Contours/Gradients

      The most mind-numbing part of this project was trying to figure out how to generate the contours/gradients. This was really a two-parter:

      #3A: How do I determine the elevation of a single point on the grid?

      The circular nature of the contours gave me my answer: it had to be based on DISTANCE, or rather the inverse distance. The further it was from the peak, the lower it would be.

      ...and the contour would be

       

      Distance

      sqrt(([X Location]-([X]))^2 + ([Y Location]-([Y]))^2)

       

      Contour

      if [Distance]=0 then 5

      else sqrt(([Height])/[Distance])

      end

       

      We check if the Distance is 0 (meaning the grid point is located atop one of the peaks) and assign it a large number. We do this because otherwise, a null will be generated.

      We're also multiplying the inverse distance by the height. This, in short, means

       

      #3B. How do I combine the contours?

      This was easy: when I used SUM(Contour),

       

      And here's how it worked: I added these two elements:

       

      ...and got this:

      Fancy, huh?

       

      Real World Use Scenario:

      I wanted to chart the output of ice cubes in our work space. We have refrigerators (which produce hardly any ice) as well as ice makers located throughout the building.

      Using a map of our building as a background, I was able to plot the location of each ice maker, as well as give a representation of how much "ice cube coverage" different locations experience.

      Additionally, I was able to include a proposed location for a new ice maker to visually demonstrate how much more efficiently a new or relocated ice maker would service our employees. 

       

      Thanks for making it this far! There is admittedly a lot of back-of-the-napkin work that went into this, so please let me know if you have suggestions for more appropriate calculations.