5 Replies Latest reply on Oct 9, 2016 1:06 PM by vincelackner

    Richard Leeke's Super-Charged ZIP Code Radius-Finder

    Shawn Wallwork

      Richard Leeke is up to his map-circles wizardry again, and this time he has really outdone himself! First a bit of history…

       

      I first contacted Richard late last year with a question about converting his Concentric Circles technique from kilometers to miles. This lead to discussions of different uses for this technique, and ways to add more elements into it. We worked together, pushing these ideas as far as we could; resulting in a good serviceable radius-finder, but it was unfortunately very slow to load, and wouldn’t allow labels to be displayed at the same time.


      So Richard went to work developing an idea he had for speeding things up. This resulted in TabGeoHack, (as well as ShapeToTab) which he asked me to help beta test. I gladly agreed! TabGeoHack can be used to substantially trim the native database size resulting in maps that load lightning fast.


      When Richard recently started working on Merged Shapes, I remembered the concentric circles project and wondered if importing the circles as shapes, rather than having Tableau draw them from calculations might not give us more flexibility as to what elements/functionality could be added on top. Richard agreed to ‘give it a bit of a play’. The results of his ‘play’ are truly amazing! (See attached.)


      Why is this so useful? The advertising industry uses ZIP code-based geo-targeting extensively. For instance, on-line banner ads are often bought based on a particular set of ZIP codes. Of course, direct mail campaigns usually include a ZIP code element. The success of these campaigns partly hinges on coming up with the optimum set of ZIP codes, based on target demographics, populations, and in the case of a retail store, the distance from the store.


      This is where a good ZIP code radius-finder becomes essential. However most ZIP code radius-finders out there determine whether to include a ZIP based on a single latitude/longitude, usually the centroid of the ZIP:

       

      Richard-1.png

       

       

       

      As you can see this can prematurely eliminate ZIPs that should be at least part of the initial consideration. These single-point radius-finders end up producing sets of ZIPs like this:

       

      Richard-2.png

       

       

       

       

      The ‘missing’ ZIPs aren’t as easy to find as you might think. Especially since Tableau’s underlying ZIP code map doesn’t match up with the filled ZIP codes on top (they seem to be using two different data sources).


      Whenever I produce a ZIP code map for my ad agency client, they inevitable ask about the ‘missing’ ZIPs. They want to know which ZIPs they are, what are the demographics and population, and how much bigger are they. And might they be worth including? This is the problem I presented to Richard, and the solution he came up with went far beyond what I ever imagined would be possible in Tableau!

       

      SuperZIP.PNG

       

       

       

      This viz shows any ZIPs that are even partially within a selected number of miles of the store. (In this case, our fictitious Richard’s Boards, a windsurfing shop we’re opening in Colorado Springs!)


      Even cooler is that Richard came up with a way to split the ZIPs along the circle boundary so the inside/outside calculations could be done. I was a bit skeptical whether this was even possible and how much value it would add. Boy was I wrong; the client loves this feature! In fact we’ve gotten nothing but rave reviews from the group of Media Planners this viz is designed to serve. They can easily include/exclude individual ZIPs, and instantly see the results reflected in the various metrics they base their decisions on. When done, they simply export the ZIP code list and give it to their vendors.


      This turned out to be an instantaneous what-if machine with the incredible direct-response interactivity Tableau is so famous for! (I love DataViz!)


      Richard has posted a smaller, more limited Public version as part of his Equinox IT Blog. I suspect he’ll also comment here, once he solves his real-work problems. He’s mentioned wanting to give a nuts & bolts post so others might be able to put something like this together.


      Final Note: If you don’t care for the layout or graphics of the viz, don’t blame Richard, that’s all on me. He did all the under-the-covers stuff; I took care of the user interface design.


      We both had a blast working on this (even when I accidentally broke it for a while)! We hope you enjoy playing around with it. And fan or foe, let us know what you think, really we can take it! (After all this is VizTalk, right?)


      --Shawn 

        • 2. Re: Super-Charged ZIP Code Radius-Finder
          Richard Leeke

          Well as Shawn said, this was a really fun exercise. We both learnt lots in the course of putting this together - not least about how to collaborate effectively on a joint workbook. We made good use of the time-zone difference, bouncing the latest version to and fro via Dropbox during our respective nights. We were careful to be clear about handing control to and fro, so only one of us was ever making changes at one time, so we didn’t go losing work.

           

          We also made a point of explaining to each other in some detail what we had changed each time. But despite that we each managed to break things the other person had done from time to time.

           

          That has got me thinking about possible extra features in the product which could help with collaboration – mainly around finding out where something is used and what the impact of changes would be. About the only way I know of at the moment for finding out where a field is used is to try deleting it. That either gives you a list of places where it’s used (at which point you can cancel) or it goes ahead and deletes it (at which point you can use the undo button to get it back). A less destructive way of doing that (and ideally getting more information) would be nice. I’ll post some more thoughts on this when I’ve organised them a bit.

           

          In case anyone is interested, here’s a brief summary of how the mapping of the ZIPs and circles was achieved. Most of the work in this part of the viz was actually in the data preparation, which was done outside Tableau, using PostGIS (an open source spatial database) and some other open source spatial libraries. Here are the steps involved in getting the shapes as we wanted them:

           

          • Load ZIP code boundaries into PostGIS
          • Generate a shape file with a set of concentric circles centred at the point of interest and load that into PostGIS
          • Use PostGIS spatial query functions to work out which zips were inside, outside and overlapping each circle. For the ones that intersect each of the circles, generate separate shapes for the inner and outer segments. Also calculate the total area of each ZIP and the area of the inner and outer segments for those that intersect the circles
          • Export the resulting shapes, along with the area details and save in shape file format
          • Load the shapes into Tableau with tabgeohack, purging all built-in shapes other than the ZIPs in the area of interest (to minimise the size of the packaged workbook so that it doesn’t take too much space on Tableau Public, and to make the viz as fast as possible)

           

          The resulting shapefile contains multiple different definitions of the ZIPs in the area, broken down according to each radius of interest – in this case 2, 4, 6, 8, 10 miles, as shown below.

           

          All CS Shapes Horizontal.png

           

          The viz also uses the original whole Tableau ZIPs, overlaid on top of the view of the segments using a dual axis, but with transparency set to zero (i.e. completely see-through) for the whole ZIPs. This allows selection of the whole ZIP, whilst clicking on either the inner or outer segment. That allows ZIPS to be excluded and the ZIP filter to be updated correctly (i.e. excluding the ZIP simply unchecks the row in the ZIP filter), rather than the exclude action generating a new filter which just excludes ZIP segments.

           

          Note that the basic display of the shapes and colouring to show the most relevant ZIPs can be achieved without the need for the unsupported hack – just using polygons derived from the same shapefile exported from PostGIS. The view of all the different circle sizes above was actually generated off the polygon version, for example. But there are several limitations doing it that way (no labels on polygons for example) and when it comes to adding some of the richer interactivity, the polygon version seems to hit the wall a lot sooner than the geocoding version. Also, the polygon version is a lot slower,

           

          Other than that it’s just regular Tableau functionality – with a fair amount of attention to the user interface from Shawn.

           

          An example of Shawn’s influence was that I carefully constructed the tooltips for the map and bar chart view, laden down with far too much information. The next day when I got the workbook back from Shawn he had simply removed the tooltips completely – correctly pointing out that they obscured the far more useful and more interactive highlighting and labelling we had put in on the hover actions. This actually ties in with a recent thread on the forums discussing moving menu actions entirely into tooltips. For cases like this where you don’t want tooltips I think the menu actions need to be in both places.

           

          Shawn’s graphic designer’s eye also detected that the font we were using did not work well on Public, as discussed in this thread. I’d just thought that either my eyesight was suffering from too many hours glued to a laptop or that my glasses needed a clean.

           

           

          The main issue I see with this whole approach (aside from the fact that it relies on my unsupported hack) is that the preparation of the shapes needs to be done statically outside of Tableau. Whilst that actually works OK in this case, it does mean that the analysis is locked in to a specific centre (or set of centres). If Tableau were to support direct visualisation of geometries from a spatial data source as a new mark type, the whole process could be made dynamic, with the location of the centre, scaling of the radius and generation of the overlapping segments done on the fly with spatial functions. One day, perhaps.

          • 3. Re: Super-Charged ZIP Code Radius-Finder
            Shawn Wallwork

            If Tableau were to support direct visualisation of geometries from a spatial data source as a new mark type, the whole process could be made dynamic, with the location of the centre, scaling of the radius and generation of the overlapping segments done on the fly with spatial functions. One day, perhaps.

             

            Hear, hear! I'm certainly hoping this 'One day' comes sooner rather than later. One of our prime motivating factors in all of this was to demonstrate how useful this sort of functionality could be, and not just for the advertising industry. The off-forum feedback we're getting is excitement over how this could be applied/adapted for other industries and situations. I realize Tableau has lots of different chart types to support and continue to evolve; I'm just hoping that when it comes to filled maps they (you) move in this direction. (Please)

             

            --Shawn

             

            PS: Jonathan, glad you liked it, and thanks for your comment.

            • 4. Re: Super-Charged ZIP Code Radius-Finder
              Richard Leeke

              > I'm certainly hoping this 'One day' comes sooner rather than later.

               

              I have no idea what the product roadmap looks like - but I wouldn't go getting your hopes up too much Shawn. I think there is a significant amount of development work needed to get to what I'm talking about here - and realistically it would only be a relatively small proportion of customers that would have access to a spatial database. I expect high effort for the benefit of a small number of customers puts it fairly well down the priority list.


              • 5. Re: Richard Leeke's Super-Charged ZIP Code Radius-Finder
                vincelackner

                Shawn:

                 

                Though you might find this link interesting:

                 

                Re: Solution to Geocoding Privacy Concerns

                 

                Best,

                Vince Lackner