13 Replies Latest reply on Oct 4, 2012 4:55 PM by Shawn Wallwork

    Custom Geocoding - Can I add a role aggregating counties without lat/lon info?

    Leigh Fonseca

      I have identified some marketing regions that are a collection of counties and would like to visualize them as filled maps.  To do this, I think I need to create a new role.  For example, the new role would be called "Bay Area" and it would include the counties: San Francisco, Alameda, Marin, San Mateo and San Jose.

       

      I'm looking to leverage the fact that Tableau already knows the coordinates for these counties and I'm just grouping them together.  Something like this:

       

      import-example.png

       

      Presently, I do not have the poly coordinates for the counties or the marketing regions but I'm hoping there's a way to say, all of these counties belong to this new role.  I've spent some time this morning reading articles on the forum (http://community.tableau.com/thread/103926) and reading through the manual and knowledge base articles but I haven't found anything that helps me define the import file without lat/lon coordinates.  The manual entry (http://onlinehelp.tableausoftware.com/v7.0/pro/online/en-us/maps_customgeocode_importfile_newrole.html) seems to say that I need them but it also seems to be speaking to adding a level of detail rather than aggregating existing fields.  And while I could go to TabGeoHack approach (http://www.clearlyandsimply.com/clearly_and_simply/2012/03/create-your-own-filled-maps-in-tableau.html) I'm hoping there's simplier route.  Any ideas?

       

      Thanks,
      Leigh

        • 1. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
          Shawn Wallwork

          Leigh, I'll give you a short answer, until Richard Leeke, signs in and sees your question. He's got a lot more depth of knowledge in this area. So...

           

          1. Easiest/simplest way to visualize a group of counties as belonging to a group is to make them the same color:

          Map1.png

          Obviously this doesn't require importing geographic roles, just assigning colors.

           

          2. You can do the same thing without individually assigning each county, by adding an extra column to your data, say "Area" and defining "Bay Area" and "Other" there.

           

          3. No, you can't import geography without at least lat/long fields. Tableau can easily generate this list for you (view data\copy) but this will only give you points for the counties, not shapes.

           

          Without a shape file that includes custom polygon outlines of your group of counties, there's really no advantage of going the custom geocode route. You can get the shape files for the counties you want here: http://geocommons.com/search?model=&query=CA+counties But to group them into a single shape is going to take some work.

           

          Hope this helped a little.

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
            Leigh Fonseca

            Thanks, Shawn.  Since I'm looking at national data I'm afraid I may have to generate the custom polygon's.  I was really hoping there was a trick to group a collection of known polygons to form a new entity.  Given custom sales region, Nielsen DMAs, etc., this sounds like a good suggestion for the Ideas section.  Maybe Richard will have suggestion for me in the meantime. 

             

            And thanks for the GeoCommon's link.  They're new to me.

             

            Cheers,
            Leigh

            • 3. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
              Shawn Wallwork

              Leigh, if you search GeoCommons you'll find DMA shape files. I don't remember which one I used but I'm attaching a filled DMA map I created using one of the files I found on GeoCommons, and used Richard Leeke's:       ShapeToTab utility to create it.

               

              Even if you find TabGeoHack overwhelming, ShapeToTab is the opposite: easy to use, and more importantly 'legal' (not a hack). I can not recommend this wonderful utility more highly!

               

              If you find an Arbitron shape file please let me know! I've struck out so far. Thanks,

               

              --Shawn

               

              EDIT: Oh, I forgot to mention: Urban Mapping, the 3rd party map provider of Tableau's maps, does offer the services you're interested in (i.e. custom sales areas) for an additional fee. I have no idea what that fee would be, but the service is available. (I suspect Brooke or Ian will jump in here soon.)

               

              Message was edited by: Shawn Wallwork

              1 of 1 people found this helpful
              • 4. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                Richard Leeke

                Well Shawn has pretty much covered all the bases already.

                 

                My recommendation would be to try the approach that Shawn mentions of adding a column to your data to define the marketing regions (if it isn't already there). Here's a really simple example using the SuperstoreSales example data that demonstrates grouping States into regions. Note that turning off borders on the shapes helps make it look more like a continuous area. I've attached the workbook so you can see how easy that is.

                 

                Grouping Shapes.png

                 

                Depending how your data is structured there might be a bit of work to do to get that to work properly (you would need to arrange to have rows in your data corresponding to all counties - even if your data doesn't really include those). Post a sample of your data if you want some pointers.

                 

                If that doesn't work for you then I would probably suggest taking a look at the Urban Mapping offering as the next point of call. I don't know much about it (including cost), so I don't know if that will meet your need or not, but that certainly seems to be the future direction for Tableau.

                 

                Or as Shawn says you could take a look at either of the utilities I did. Shapetotab is simpler and doesn't take you into unsupported territory. Tabgeohack is more functional, but quite hard to understand (not to mention all the caveats from the article).

                1 of 1 people found this helpful
                • 5. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                  Leigh Fonseca

                  Thanks Richard & Shawn,

                   

                  Lot's of great suggestions and the Nielsen DMA packaged workbook was a big help.

                   

                  Last night I decided to make a go of TabGeoHack.  I read all the the Grow your Own Filled Maps start to finish, the TabGeoHack doc and the Simply & Clearly instructions.  I'm using a Neilsen DMA shapefile downloaded from GeoCommons and I cannot get either TabGeoHack nor ShapeToTab to recognize it.  Apparently it is missing the coordinates system.  The download link says the files are ESRI Shapefiles. (I've attached the zip).


                  For TabGeoHack, I've tried adding

                   

                  source_crs: ESRI Shapefile

                  or

                  source_crs: WGS84

                  or

                  omitting source_crs

                   

                  each time I get the error:

                  C:\TabGeoHack>tabgeohack --roles NielsenDMA2.yml

                  Generating custom geocoding files...

                  FAILURE:

                  Unable to open datasource `c:\temp\NielsenDMA2\DMA_temp.shp' with the following drivers.

                    -> ESRI Shapefile

                    -> MapInfo File

                  ....

                   

                  I then switched to ShapeToTab hoping I could get it working there but encountered the same error and had difficulty setting the s_srs option and accessing the help menu:

                   

                  C:\ShapeToTab>shapetotab nielsen_dma.shp

                  Can't transform coordinates, source layer has no

                  coordinate system.  Use -s_srs to set one.

                   

                  at shapetotab.pl line 157

                   

                  C:\ShapeToTab>shapetotab -s_srs ESRI Shapefile nielsen_dma.shp

                  Unknown option: s_srs

                  Usage: shapetotab [options] <shape_file>

                  Try: shapetotab --help at shapetotab.pl line 570.

                   

                  C:\ShapeToTab>shapetotab -s_srs WGS84 nielsen_dma.shp

                  Unknown option: s_srs

                  Usage: shapetotab [options] <shape_file>

                  Try: shapetotab --help at shapetotab.pl line 570.

                   

                  C:\ShapeToTab>shapetotab --help

                  Usage: shapetotab [options] <shape_file>

                  Try: shapetotab --help at shapetotab.pl line 584.

                   

                   

                  After seeing the great work done with your tool I'd really like to leverage it.  I realize these are unsupported tools so any assistance you can offer is greatly appreciated.  I've attached my yml and log files.

                   

                  Thanks much,

                  Leigh

                  • 6. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                    Ian White

                    Thanks for the reference Shawn.

                     

                    Hi Leigh-- we're working with a few Tableau customers who have custom aggregations that define sales regions or other kinds of custom territories. It's clear users want to manipulate/control all the data they can and you've stumbled across some of the limitations doing everything in the client. We can to aggregate any boundary feature (parcel, census tract, city, postcode, county, state, etc...) and represent it as a custom layer (server side) for you. We then create custom geometry for you in Mapfluence so you can associate different values over time (# reps, # deals, etc...) . The screenshot below is similar to Richard's above except we've done it using Mapfluence.

                     

                    If your data is updated frequently (or you have a lot of it), I suspect there could be a bit of frustration in doing this activity. It takes us about 10 minutes! Richard's tool is great to fill the current gap, and we offer another way.

                    Slide4.png

                    • 7. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                      Richard Leeke

                      Hi Leigh

                       

                      Sorry it's proving difficult - but I did warn you that it's a bit tricky. The good news is that you a re very close and I've just successfully loaded your shape file after a few tweaks to your YAML. I'll explain all the issue and how to get it going with both tabgeohack and shapetotab.

                       

                      DMASample.png

                       

                      The only real problem is that the shape file doesn't contain a definition of it's coordinate references system, so as you saw you either need to tell tabgeohack and shapetotab what the source CRS is, or tell it not to bother transforming it (because it is already the one you want). The thing you were missing was the obscure naming of coordinate reference systems required by the GIS libraries I'm using. If you want to tell either tabgeohack or shapetotab to use WGS84 you have to refer to it as 'EPSG:4326' - that is just the standard name for that. Or just don't transform. So either this:

                       

                              source_crs: EPSG:4326

                       

                      or this:

                       

                              no_transform_crs: true

                       

                      would have done. (Note the 'true' rather than '1'.)

                       

                      One other issue that confused me for a bit is that you actually have two sections defining the same shape file in that YAML - so even after telling it how to cope with the first one I was still getting the same error. I've commented out the second one in the attached file.

                       

                      The next thing was that you had tried to include DMA into the definition of the hierarchies in the YAML. But because the DMA file doesn't actually define where in Tableau's hierarchy each DMA lives, that resulted in tabgeohack purging all of the DMAs again after it had added them (because it didn't know they were in the USA). So I just took that out of the hierarchy.

                       

                      One more thing which didn't hurt because the line was commented out, but can sometimes cause problems is that you had used tabs instead of spaces at one point in the file. The easiest way to see that is to use an editor that knows about YAML. I use scite which colours lines with tabs in red:

                       

                      YAML errors.png

                       

                      I've attached a fixed up YAML file for you.

                       

                      To get it going with shapetotab you should just need to specify one of these two options on the command lines.

                       

                      --from_crs EPSG:4326

                       

                      --no_transform

                       

                      Note that the -s_srs option is actually part of the error from the underlying GIS libraries, which is a bit confusing - that isn't an option for shapetotab directly, though it is the option I'm using under the covers when you use the --from_crs option.

                       

                      Good luck. Let me know how it goes.

                      1 of 1 people found this helpful
                      • 8. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                        Leigh Fonseca

                        Thanks for your very quick  and thorough reply Richard.  Your edits made the program run and I now better understand how to construct the yaml file.  Sadly, I'm still struggling to get my maps filled by DMA. 

                         

                        The program runs and completes quickly:

                        c:\TabGeoHack>tabgeohack --roles NielsenDMA-117147.yml

                        Generating custom geocoding files...

                            DMA... (206)

                        Done in 1 seconds

                         

                        And the output folder and files are created.  I didn't have any errors with the "Import Custom Geocoding" in Tableau or assigning the dimensions to the Custom Geocod fields.  In fact, all the DMAs appear correctly on the map but Tableau keeps giving me an error that Filled maps are not available for my new geographic role--DMA.  I'm wondering if there's something different about my setup than yours since you were able to render filled maps with the same data set.

                         

                        no-filled-maps.png

                         

                        To debug this, I started over and freshly download the CSV and Shapefile from Geocommons (GeoCommons-117147.zip).  I created new directories in TabGeoHack and a new yml and ran it to generate a new file for "Import Custom Geocoding".  Same error.   I'm sure like earlier it must be something quite simple that I'm misunderstanding and I really appreciate your time looking at this.

                         

                        I created a packaged workbook with the data and also zipped my TabGeoHack files.  Thanks in advance, Richard!

                         

                        Also, would you mind posting the workbook you were able to get the geocoding to work with?  I'm wondering if I can see something there that I'm missing. 

                         

                        • The GeoCommons-11147.zip is the complete set of source files. 
                        • NielsenDMA-117147.zip contains the shapefiles and the output directories and files
                        • NielsenDMA Example.twbx uses the CSV and Custom Geocoding (the image above is from that)
                        • NielsenDMA-117147-log-yml.zip contains my log and yml files.

                         

                        Thanks again!

                        Leigh

                        • 9. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                          Richard Leeke

                          Will have a proper look later - but it looks as if you may just have forgotten to do the last step. You need to:

                           

                          tabgeohack --roles (done)

                          import custom geocoding (done)

                          tabgeohack --shapes (I can't see any sign of this)

                           

                          That would certainly explain the symptoms - that final step is the one that adds the filled map data.

                          • 10. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                            Leigh Fonseca

                            Richard,

                             

                            You are a prince.  That was the step I was missing.  Thank you again for your generosity producing this great tool and your time helping me with it.  I'll share my vis shortly.

                             

                            Cheers,
                            Leigh

                            • 11. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                              Richard Leeke

                              Glad it works for you now, and look forward to seeing the results. I put a lot of work into getting that going so it's always nice to see the end product.

                              • 12. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                                Leigh Fonseca

                                Richard,

                                 

                                I really cannot say Thank You enough.  I read those directions so many times and kept missing the --shapes.  I knew it must be something simple given all the success stories on the forums.  Funny how sometimes we get caught in our own little loop.  I'm working on some election spending data from OpenSecrets and mapping it to DMAs.  I'm also really excited to get to leverage the DMA work for my clients.  Stay tuned.  And Thank you again for all that you contribute.  I love this Tableau community

                                 

                                Best,
                                Leigh

                                • 13. Re: Custom Geocoding - Can I add a role aggregating counties without lat/lon info?
                                  Shawn Wallwork

                                  Leigh, did you ever finish that DMA map? You want to share?

                                   

                                  --Shawn