11 Replies Latest reply on Oct 26, 2015 9:42 AM by Connor Martin

    Merging custom zip code boundaries into a single shape for analysis

    malena.rubino

      Hi there,

       

      I'm new to Tableau and a novice GIS user. I'm trying to find a solution to my mapping problem and there seems to be many resources available on these forums, but I am not sure which one is best for me to attempt. I'm looking for someone to point me to the right direction or help me out.

       

      Basically, I want to create my own custom map boundaries. At this point I have zip code data and can set the boundaries I need using these data, but I want to group certain zip codes into a single shape to view results at an aggregated level instead of by an individual zip code level.  Ideally I wish I could merge zip code boundaries and assign club names to them.

       

      Attached is a workbook including unique sales areas defined at the zip code level. These sales areas are considered clubs and are often made up of random areas across different states. For example if you filter on the Allied club, only the orange area appears as a group made up of Kansas, West Virginia, Kentucky, Connecticut. What's missing: I want users to see Allied results by clicking on the map, so if they click any area that makes up Allied, I want the aggregate results to show of all those zip codes  and I want the entire club to highlight on the map.  Currently, when users click on the map they get zip code level data instead of club level data.  I don't want users to view results at the zip code level but only at the designated club level.

       

      I think I need to use custom geocoding and or create my own poligons. I'm just not sure what solution I should use to achieve this. Please let me know if you need any more clarification, this is all new to me.

       

      I have access to Quantum GIS.

        • 1. Re: Merging custom zip code boundaries into a single shape for analysis
          Richard Leeke

          You can achieve what you want when you click on the map very, very easily without having to worry about custom shapes by using actions.

           

          In the attached I have simply defined a highlight action which highlights all zip codes for the selected club(s) and a filter action which filters the display to just the zips for the selected club(s).

           

          I turned off display of the shape borders, but you do still get a grey line at the zip code boundaries. Also display is quite sluggish with that many zips.

           

          But that may well be good enough for your purposes. Going the next step of merging the zips into custom shapes is a lot of work and really quite difficult - and would either mean you drawing your own polygons or stepping out into the unsupported "hack" territory that I've been playing in recently.

           

          See if the attached gives you what you need - or plan on a few weeks of intensive effort and learning all sorts of things about GIS that you didn't really want to have to know.  ;-)

          • 2. Re: Merging custom zip code boundaries into a single shape for analysis
            Shawn Wallwork

            Malena, Richard is being a bit coy (actually a bit humble for that matter). He has created a great way to create custom filled maps using a utility (he calls it a 'hack') of his own invention. It works great! Here's the link:

             

            http://community.tableau.com/thread/116369?start=0&tstart=0

             

            For your purposes, the only 'catch' is, you'll need to use your GIS skills to generate the shape file of the outside boundary of your ZIP code groups. Do that, and not only will you have the custom geocoding you're looking for, but it will load infinitely faster than Tableau's standard geocoding (you don't have to load the whole world, just your zips).

             

            So sure, simple and quick is almost always better, but if you're looking for something a bit more adventurous try out Richard's utility.

             

            --Shawn

            2 of 2 people found this helpful
            • 3. Re: Merging custom zip code boundaries into a single shape for analysis
              malena.rubino

              Thanks, Richard.

               

              This is really helpful and does work for my purposes but you're right, it's a bit slow with all these zip codes. Tableau crashed a few times in the short span I've been playing with this solution. Unfortunately, I have to include all these codes and will be adding more in the future. I think it may be best to try your utility that Shawn mentions below. I have an in house GIS analyst I can get help from.  If we have any questions, I'm sure you'll be the first to know.

               

              Cheers,

              Malena

              • 4. Re: Merging custom zip code boundaries into a single shape for analysis
                malena.rubino

                Thanks, Shawn. I think my team will try this route!

                • 5. Re: Merging custom zip code boundaries into a single shape for analysis
                  Richard Leeke

                  The reason I was being coy is that the 'catch' of merging shapes is actually hard. I've been looking at a few approaches to that recently, including building it into my hack utility as a standard option (which would need me to write my own logic to eliminate the common boundary lines, which is non-trivial), using GIS tools to do it or using a spatial database to do it.

                   

                  The spatial database approach looked the easiest to me. "All" you need to do is load the zip boundaries along with the zip to club mapping table and then use the standard ST_UNION() spatial function to merge the shapes. With a little bit of tweaking of my hack utility it ought then to be possible to use the spatial database as the data source used by the hack and directly generate the merged shapes straight into Tableau. I've tried that with three spatially enabled databases and run into roadblocks with each:

                   

                  MS SQL Server: The GDAL libraries for SQL Server failed to load the shape files with very unhelpful error messages. I spent a while on it and gave up.

                   

                  MySQL: The current released version of MySQL has a very brain-dead subset of spatial functionality working and does things like using a rectangle bounding box as an approximation to the real shape for most of the nominally supported functions. Useless. The beta of the next version adds processing of the proper boundaries for some functions (though others are still not there). But it's spatial indexing is completely broken, so it's unusable on any volume of data (this was still the state of play in the latest beta I downloaded last week). So I'd say spatial support isn't out of kindergarten yet in MySQL.

                   

                  PostGIS/Postgress: GDAL loads the data fine, all the spatial functions are there and spatial indexes work correctly so it processes large files fast. But the result of an ST_UNION() call gives shapes which break GDAL on attempting to export the shapes as WKT. I haven't given up on this path - but haven't touched it for a few days because work is full on at the moment. I hate it when that happens.  ;-)

                   

                  Anyone who knows their way around GIS tools and knows how to do this more easily, please chime in here.

                   

                  Edit:

                  I just saw your comment about having an in-house GIS expert - so maybe there's no need to be daunted by all of this.

                  Good luck and do post updates on how you're getting on.

                  • 6. Re: Merging custom zip code boundaries into a single shape for analysis
                    Richard Leeke

                    I have managed to get past the issue I was having with merging shapes using PostGIS. If anyone is interested, let me know and I'll explain how to do it.

                    • 7. Re: Merging custom zip code boundaries into a single shape for analysis
                      Richard Leeke

                      I've been having a bit more of a look at the question of merging shapes - it comes up quite a bit on the forum and I've hit it several times on things I'm working on, so I've been having a bit of a think about whether I can generalise the approach I've been using to do it as part of my filled maps hack that Shawn mentioned above.

                       

                      I haven't quite taken it to the extent of automating it yet, but I thought merging the zip codes for clubs would be a good test case, so I had a bit more of a play with the sample workbook from this thread and merged the zips for the clubs.

                       

                      The approach I'm using (merging shapes using spatial functions in the PostGIS spatial database) and then using my hack to load the resulting shapes seems to have worked really well, and the workbook certainly performs much better than the original..

                       

                      There were a few zip codes with multiple clubs associated with them. I just left those like that, which means that there are a few places where the shapes for the different clubs overlap (which seems like the best way to represent the data if that reflects reality). The darker areas in the blue below are where the orange shows through.

                      Overlapping Clubs.png

                      There were also a few null points which I haven't even bothered to investigate - I just filtered them out.

                      1 of 1 people found this helpful
                      • 8. Re: Merging custom zip code boundaries into a single shape for analysis
                        Shawn Wallwork

                        This is great Richard. Everything you want in a filled map:

                        Richard-1.PNG

                        Cut-outs, mulitiple non-contiguous shapes, and fast load! Great stuff.

                         

                        --Shawn

                        • 9. Re: Merging custom zip code boundaries into a single shape for analysis
                          malena.rubino

                          This is exactly what I was looking for—merged shapes and fast performance! This is awesome! How would I go about adding additional clubs to this file?  Would I need to go through PostGIS and then your hack?

                           

                          Thanks!

                          Malena

                          • 10. Re: Merging custom zip code boundaries into a single shape for analysis
                            Richard Leeke

                            Yes, that's exactly right I'm afraid. In fact it's slightly worse than that in that there was also another bit of a hack to get the zip code boundaries into PostGIS - and that's not something I can easily share at the moment - it was a bit convoluted.

                             

                            I'm hoping to add the ability to merge shapes like that into my hack at some point (though no idea when - I'm flat-out at work at the moment). If and when I do I have a couple of options about how to. The easier way for me would require PostGIS installed - but I think that may be getting just too complex. The other way should just fall out of something I'm looking at doing to improve the simplification of complex boundaries - but that is hard and will need a bit of thinking time.

                             

                            For now you would need to source your own zip code boundaries and merge them for yourself - either using a GIS tool or a GIS enabled database like PostGIS. You would also almost certainly need to simplify the resulting merged boundaries. And then use my hack.

                            • 11. Re: Merging custom zip code boundaries into a single shape for analysis
                              Connor Martin

                              "The Hack" was a bit out of my league in more ways than one, but was the desired outcome as well. Thanks for throwing in the simpler example, works just fine.