1 2 Previous Next 16 Replies Latest reply on Jul 18, 2017 2:17 PM by Sarah Battersby

    Beginner mapping questions - census visualizations and data overlay

    Parv Aggarwal

      Hi all,

       

      Tableau newbie here. I am trying to look at financial services market capture of latino populations (as per 2010 census) in NC via census data visualization. I have followed Mark Evans' guide for importing census variable data and joining it to shapefiles via his WDC  (Tableau, Census Data, and Web Data Connectors – I Like Big Bytes  and http://ilikebigbytes.com/index.php/2016/09/07/mapping-census-tracts-in-tableau/ ) and successfully formed a color coded map of Hispanic or Latino populations in NC - see attached workbook. I would next like to  put our branch locations as points on the same map and use the radius selector tool to count/add up the latino populations in circles of any given radii from those branch points based on census tract centroids.

       

      However, I am stuck on/struggling with:

      - Making circles (e.g. 5-10 miles) around any arbitrary point shows a very inaccurate sum(hispanic or latino population), off by at least a factor of 1000 (in many cases exceeding the population of our state!) and I have no idea why this is... any insight / how to fix it?

       

      - I made an excel of (branch, zip, lat, long) and am trying to display the branch locations to the same map but it says I must first create a relationship mapping the branch latitudes / longitudes to the "generated" ones in the census shapefile... I believe the generated ones are just endpoints of the tract polygons, so the equivalency wouldn't work... what should I create instead to get it to show? I'd like to show branches as labeled dots.

       

      - Alternatively, I could add the branch locations by (center points of) zip codes, but then I need to figure out how to map zip codes to tract-level FIPS codes... any simple way to do that? I would also like to add our membership data to this map too but cannot geocode it en masse due to privacy restrictions; hence will probably have to aggregate by zip codes - I know by itself on a new workbook zip codes aggregate data without any issues but I am not sure what the right way is to create a relationship so I can plot zip code level data along with the existing tract data I have.

       

      Mr. Mark is understandably unable to offer help and advised me to ask here. I would really appreciate any advice from the community!

      Tableau 10.2 - 32 bit.

       

      Sincerely

      Parv

        • 1. Re: Beginner mapping questions - census visualizations and data overlay
          Courtney Drake

          For your first question, it looks like you are getting the population for every PointOrder:

          You might be able to do this with an LoD calc, you should be able to work around this. They allow you to control the level of aggregation.

          More on LoD calcs: Top 15 LOD Expressions | Tableau Software

           

          Or something less elegant like:

          if [PointOrder (Census Tract Shapefile Data)] = 1 then [Hispanic or Latino Tract Population] END

          1 of 1 people found this helpful
          • 2. Re: Beginner mapping questions - census visualizations and data overlay
            Parv Aggarwal

            Hi Courtney,

             

            Appreciate your insight and suggestion!  I created a new calculated field of [if [PointOrder (Census Tract Shapefile Data)] = 1 then [Hispanic or Latino Tract Population] END] and called it Hispanic_Tract_Sum. I want to get the correct summation to display in the hovering caption (it'd be nice to let that stay longer but that can wait until later) when I select an area on the map (usually a circle) but as of now it still displays only the old flawed sum (see screenshot). How can I change that?

             

            Thanks

            Parv

             

            PS can anyone guide me with qs 2 and 3?

            • 3. Re: Beginner mapping questions - census visualizations and data overlay
              Sarah Battersby

              Hi Parv Aggarwal -

               

              From your question I think you were asking how to:

              • Make a map with Census tracts as filled polygons and point locations on top of the map
              • Select tracts on the map using a radial select and get a summary of the values for the tracts

               

              If that is right, I have a workbook for you that might help with doing this.  It is a little complicated, but I've tried to put sufficient detail in the worksheet captions in the twbx so that you can follow.  The short version:

              1. Union the the two files (csv with vertices defining the polygons & the csv with the point locations you want on top of the tracts).  You can do this on the data source tab
              2. Make a new latitude and longitude calculated field that has a value for every point (if there is a value from the polygon csv, use that; if not, use the one from the point csv) - this just makes sure that we have a latitude and longitude value for every row, regardless of which table it came from.
              3. Make the filled polygon map using the new latitude and longitude calculated field.  HIDE the points that aren't used for drawing the polygons (the ones that represent the points you want on top of your map).  You don't want to exclude them, just hide them.
              4. Add another copy of the new latitude calculated field that you made
              5. On the second marks card, you'll make your point map
              6. Right click on the second latitude pill and select dual axis.

               

              On the dashboard that I added to the workbook, you can use radial select to select Census tracts around one of the points and the table next to it will update with population information and a sum of all selected tracts.

               

              6-13-2017 8-40-13 PM.png     6-13-2017 8-40-41 PM.png

               

              Let me know if this works for you.

               

              -Sarah.

               

              ps - It sounded like you were working with 10.2, so instead of using vertices to draw polygons, you could just use a shapefile from the Census and it simplifies the workflow a bit.  Let me know if you want some help with the shapefile version of this type of dual axis map.

              1 of 1 people found this helpful
              • 4. Re: Beginner mapping questions - census visualizations and data overlay
                Courtney Drake

                For the tool tip, I think a Table Calculation would work. We basically need to make sure all the PointOrders that != 1 have the correct value for [Hispanic or Latino Tract Population], instead of the 0 we were using to ensure the total wasn't inflated.

                 

                For that, I basically wrapped our last equation in a Window_Sum, like this:

                WINDOW_SUM(SUM(IF [PointOrder (Census Tract Shapefile Data)] = 1 THEN [Hispanic or Latino Tract Population] ELSE 0 END)

                 

                In the attached workbook, if you right click on the Window_Sum on the marks card > Edit table calculation, you can see how it is computed.

                1 of 1 people found this helpful
                • 5. Re: Beginner mapping questions - census visualizations and data overlay
                  Parv Aggarwal

                  Thank you so much Sarah and Courtney for your helpful tips and demonstrated workbooks; they really are very helpful! I will try to process this to the best of my ability first and get back if I have further questions! Really appreciate both of your help and support!

                  • 6. Re: Beginner mapping questions - census visualizations and data overlay
                    Parv Aggarwal

                    Hi Courtney Drake, or Sarah if you may know this too (esp #3) -

                     

                    I am trying to understand and replicate step by step what you did. I named the single measure of the tract population 'unique_hispanic_pop' instead of 'pointorder=1' to save confusion. I called window_sum 'total_hispanic_pop' instead. See attached.

                    1) However, while putting agg (unique_hispanic_pop) to color shows the correct map, it displays 0 for all unique_hispanic_pop values. Any idea why?

                    2) The window_sum works properly and I really appreciate your guidance, but I am trying to better understand how this function works - it's supposedly summing the selected values for the pointorder=1? Is there any alternate to this function for the correct population display perchance?

                    3) This way tends to select and add up all the extra tracts that are barely contained within a circular selection (often seeming to be out of the radial circle)... I'm not sure that it's only including it at the first polygon point (that's what pointoder=1 was supposed to do right?) I would like to instead only select and include the tract population if the centroid is contained within the selection... I have a spreadsheet of track centroids (attached)... is there a way to place the unique_hispanic_pop at the centroid points instead of pointorder = 1 for this purpose?

                     

                    Sincerely

                    Parv

                    • 7. Re: Beginner mapping questions - census visualizations and data overlay
                      Parv Aggarwal

                      Hi Sarah Battersby

                       

                      I am trying to replicate and understand your steps above as well -

                       

                      1) I'm still stuck on this, as every time it's somehow preventing me from seeing both tables/data sources at the same time to add to the union... the data source cylinder at the top left only lets me toggle one or the other source view and hence I can't drag the other to the union argh! see attached.

                      2)  I see how in calculated fields, makes sense.

                      3) Explained in captions well.

                      4) Can you explain why this is necessary?

                      5) Maybe I should take a look at the basic dual axis idea page to understand the logic /mechanism better, as currently I don't quite follow.

                       

                      So yeah, basically how do I get the two files to show up to do the union properly?

                      Also, are your last 2 tabs necessary functionally, or just visual artifacts?

                       

                      Thanks so much

                      Parv

                      • 8. Re: Beginner mapping questions - census visualizations and data overlay
                        Sarah Battersby

                        Hi Parv,

                         

                        You should be able to union two data sources.  My workbook used two csv files for the input - I saved the data in your TWBX as csv files so that I could work with it directly.  I'm not sure if your working from a datasource accessed through a Web Data Connector would act differently or not.  Even with data from different locations, you should be able to just have both data sources added, and then switch between them to select the second file to union.

                         

                        This might be a good place to start with figuring out the unioning problem: Combine your data with Union in Tableau 9.3 | Tableau Software

                         

                        As for the last two tabs - they aren't necessary for the overlay; I just created them so that I could demonstrate how to get the sum of the population based on the radial selection. 

                         

                        -Sarah.

                        • 9. Re: Beginner mapping questions - census visualizations and data overlay
                          Parv Aggarwal

                          HiSarah Battersby Courtney Drake  - I unfortunately still am stuck on this basic union thing and need that to proceed forward as deadlines are approaching fast . I've attached my packaged workbook with all relevant data. As you can see when I try to create a union it only allows me to view a singular data source/table via the toggle cylinder at the top and hence I cannot select two tables simultaneously which I need to make a union. Are you using 10.2? The union forum you pointed to was for an older version and the 10.2 union how to link Union Your Data  assumes all your sheets can be viewed simultaneously which is not the case. Can you try to successfully create a union using my workbook in 10.2? I need to create unions between

                          - the branch lat/longs and census shapefile lat/longs as suggested earlier

                          - the census tract ids from the shapefiles and their respective centroid data as indicated earlier (need to modify courtney's pointorder rule so it's based on centroids instead... any suggestions for how to do that?)

                          - our membership zip code data and tract shapefile data, or any alternative way to get an overlay... it is apparent from the file mapping zip to tract_fips that the relationship is not one to one either way, so that union would be meaningless... I want to view members by zipcodes on top of the census tract population data (or worst case have to toggle between the views), but I am not sure which union is need to be able to view these simultaneouly, following sarah's dual axis instructions...

                           

                          Sorry I'm still stuck on these basics; once I get this union thing figured out it's probably not that complicated...(well other than the centroid-only selection). Perhaps I should contact tech support with the union help if y'all are too busy?

                           

                          Sincerely

                          Parv

                          • 10. Re: Beginner mapping questions - census visualizations and data overlay
                            Sarah Battersby

                            Hi Parv,

                             

                            Since your polygons seem to be coming via a web data connector, you may not be able to union directly with this data source (or maybe it's possible, but I don't know how...).  If you just use a csv with the data for the polygons (attached - I exported it from your TWBX), you should be able to union the two csv files together to solve problem #1 (branch locations with tract polygons).  I couldn't find a way to union the data from the web data connector.

                             

                            With problem #2 is this a map with tract polygons and points for their centroids?  If so, you should be able to use the same method as in problem #1 - use the csv of the shapefile data (instead of the web data connector), and then union with the file with the centroid lat/lon locations.


                            Problem #3 - is this points for zip codes on top of tracts?  Or polygons for zip codes on top of tracts (you'll really only be able to see the data on top, since they are both polygons for the same geographic area).  I'm not sure what exactly you're trying to get in the final map...

                             

                            -Sarah.

                            • 11. Re: Beginner mapping questions - census visualizations and data overlay
                              Parv Aggarwal

                              Hi Sarah,

                               

                              Thank you so much for your reply; will shortly attempt to do the basic union with CSV for #1 (only have tableau at office and I left for the day).

                              For #2, I just have a list of tract_id and their centroid lat/long points. Assuming I'm able to do the union, do you have a suggestion for how to modify the window_sum / pointorder selection code earlier to only select/add values for tracts whose centroid lat/longs fall within the selected area, as right now it's including a lot of extra tracts?

                              For #3, yes I just want to be able to see a cluster of membership points distributed by zipcode (presumably at the built-in zip centroid point) on top of the color-coded census tracts populations. I don't think it will be helpful going through trouble with zipcode shapefiles. Once I'm able to get a cluster of points, I will add / color code additional variables like account size or type. But just getting to that point will be helpful right now.

                               

                              Really am grateful for your help.

                               

                              Sincerely

                              Parv

                              • 12. Re: Beginner mapping questions - census visualizations and data overlay
                                Sarah Battersby

                                Hi Parv,


                                I'm still a bit fuzzy on what you need to do in problem #2 - Tableau will return any polygon that touches the selection, regardless of whether the centroid is included.  You could possible set up a second viz that would update based on the centroids selected (to show sum of population for selected points).  I can't think of a way - off the top of my head - to have this be something that would show up in a tooltip.

                                 

                                 

                                For problem #3 - Your zip codes will be using a Tableau generated latitude and longitude value for the centroid and the Census tracts that you are using are from a csv.  You can't easily combine the Tableau generated latitude and longitude with non-generated latitude and longitude values.   There are some workarounds that I explain here: Dual Axis Mapping - Many Ways , but I think that working with centroids for the geocoding data instead of polygons might present a challenge. If you have the Census tracts as a shapefile, it would be easy to create the dual axis map since both would then be using the Tableau generated latitude and longitude.  You can download a shapefile with the tract boundaries from the US Census web site, and then just join in whatever attribute you want (e.g., the Hispanic population values).  That would make it easy to create a dual axis map with Census polygons + Tableau geocoding points for zip codes. 

                                 

                                My recommendation is to simplify the whole workflow and just use shapefiles instead of the data from the WDC, since the data you're using is so easy to get from the Census (and would make it more straightforward to make your maps).

                                 

                                -Sarah.

                                • 13. Re: Beginner mapping questions - census visualizations and data overlay
                                  Parv Aggarwal

                                  Hi Sarah Battersby

                                  I re-built the workbook using the csv data you packaged and no WDC as per your advice and have attached it here. I noticed that apparently I need to add new excel sheets via  the "add" button under "connections" rather than "new data source" in order to see them simultaneously, and furthermore I have to come up with rules to "join" the sheets via variables as soon as I add them.... the union pop up *still* won't let me drag tables into the "drag tables here" area even though I can view them all now (0).. I have added the data again and am trying to figure out how to proceed further. Stuck points:

                                  0) as mentioned above

                                  1) I understand your first point above about the tooltip "being what it is" and not worth changing the selection rules over. As suggested in Courtney Drake's reply #4 I edited the tooltip box to display the "unique" census tract population that's tied to PointOrder=1 in order to obtain the correct summation when multiple tracts are selected. But it's showing 0 for all tracts upon hovering (which makes sense since it is tied to PointOrder=1) even though it gives the correct summation when multiple tracts are selected. Any way I can modify this formula to display the correct value (which the original measure "Hispanic or Latino Tract Population" gave), or alternatively can I have the tooltip display the original measure but still have it sum based on the modified "unique" measure since only that gives the correct result?

                                  2) The variables from most sheets are not showing up under measures in the visualization tab.

                                  3) I get a "type mismatch" error when I try to join GeoID in the the shapefile data file to the GeoID in the "Zip to Tract_FIPS" file, even after converting the latter from string to number in excel... any idea why this is occurring?

                                  4) I am trying to work on "problem #3" to plot data based on zip codes on top of FIPS codes data. I am trying to make sense of what you wrote above. I haven't used my centroid data so far, and have given up on trying to modify the selection rule as indicated earlier. I want to figure out the best way to plot zip code based data on top of existing tract data, using your suggestion, via dual axis.  The only reason I am even trying to join zip to fips codes is so that I can plot data based on both on the same map, as suggested in your reply #3, step #1 for dual axis plotting earlier, since file unions aren't working for me and tableau wants me to join everything to view it on the viusalization tab it seems. I know that there isn't a one to one relationship between zip and tract_fips (one of each can map to multiple of the other as the areas intersect) and hence this join doesn't serve any logical purpose. It is possible to still simultaneously plot data based on zip codes and census tract without having to create an (illogical) union between them?

                                   

                                  I want to resolve these simpler things first to proceed forward to other features I'd like to add.

                                   

                                  Sincerely

                                  Parv

                                  • 14. Re: Beginner mapping questions - census visualizations and data overlay
                                    Parv Aggarwal

                                    I am taking a detailed look at your blogpost now and willl try my best to implement it using your tips there and in the earlier reply. Will get back if I am truly stuck.

                                    1 2 Previous Next