6 Replies Latest reply on Aug 2, 2018 1:10 PM by Leigh Michael

    How to create dual axis map using census data and custom data?

    Leigh Michael

      Hi all!

       

      I'm trying to create a map that reflects the following:

       

      1. First file: I pulled Census Block shapefiles from ACS. I used the shapefiles to create a geographic layer for my map.
      2. Second file: I pulled ACS data on the median household income of each Census block. This was in the form of an Excel file.

      So far, so good. I connected the data sources successfully, and the map looks like the one you see below. When you hover over a census block, it gives you that block's median household income.

       

      Here's where things get complicated (at least for me). I want to add another layer to this map that uses data from another file source (King County Metro) to pinpoint bus stops across the county. This file uses lat/long coordinates to determine stop locations. I'd like these stops to be layered on top of the existing map and show up as dots.

       

      Every time I try to add the third file - the King County Metro stop data - it doesn't work. I think I'm making this more complicated than it needs to be, but I'm at a loss.

       

      Can I connect a third file - the Metro lat/long stop data - to the existing files (the ACS shapefiles and the ACS household income data)? If so, how should I configure the formatting of the Metro data to ensure it actually works?

       

      Thank you for your help!

        • 1. Re: How to create dual axis map using census data and custom data?
          Don Wise

          Hello Leigh,

           

          This is an excellent resource for what you're trying to do.  Many methods will work with your two data sets: Dual Axis Mapping - Many Ways  Best way to learn is by doing! If you get stuck though. Feel free to reach out (with a sample workbook). 

           

          Happy to help! Thx, Don

          • 2. Re: How to create dual axis map using census data and custom data?
            Leigh Michael

            Hi Don,

             

            Thanks for your help! Unfortunately, I'm still struggling with this. I do, however, think I have a better idea of what I do know and what I need to know. For some more context:

             

            • I have three files:
              1. A shapefiles map of King County, divided by Census block.
              2. An excel sheet that gives me the median household income of each census block.
              3. An excel sheet that has the lat/long coordinates of certain bus stops in King County.

             

            Files 1, 2, and 3 all have one "like" field: the Census block ID. I'm assuming that this is needed to join the data.

             

            I have no problem making a map from files 1 and 2. It's when I add file 3 to the mix that things get complicated.

             

            As you can see from the packaged workbook, my dual axis map merely highlights the census block that each stop is - it does not portray the stop as a point/dot. Basically, I want the map to:

            • Display a King County map divided up by Census block [DONE!]
            • Be able to hover over each Census block on the map to see the median income of that block [DONE!]
            • Be able to see each bus stop on the map as a dot [NOT done]
            • Be able to hover over the bus stop point to see the bus stop's line ID [NOT done]
            • Customize the color of the bus stop point so it stands out on the map [NOT done]

             

            I'm not even sure if I'm connecting the three files correctly - perhaps this is why I'm running into errors again and again?

             

            I would be so grateful for any advice on how to do this. I'm using Tableau 10.4 Desktop.

             

            Thank you!

            • 3. Re: How to create dual axis map using census data and custom data?
              Don Wise

              Hi Leigh,

              See if the following two screenshots play out for you?  Change Data Source join to ID and Lines 36_40 Stops to Geo ID Final. Change JOIN to FULL OUTER.  As shown in Sheet 2, the majority of your map will go away due to the NULLS (1K).  However, this is a small sample data set to join on, so that number will go down and more detail should display when you add in the additional lines. But I think it will only display underlying data (polygon layers) if there's related data.

              Screen Shot 2018-08-01 at 4.16.57 PM.png

              Meaning, unless there's a bus line that crosses over each and every census tract, you might end up with some blank spots in the map. Give it a go and see how it turns out.  Also notice I needed to change the Latitude and Longitude to use GPS Lat/Lon for both Columns/Rows and for Dual-Axis to get to the resulting view.  I think this gets you closer than where you were before, but you were getting there!

              Screen Shot 2018-08-01 at 4.14.33 PM.png

              Lastly, this is definitely GPS data...meaning it's tracing the bus line route and although there appears to be large gaps between stops, there are generally two (2) points for the line/route/trace.  Which would lead me to believe that it was a stop up the route and then another stop on the return route back the reverse direction.  Just an observation...

               

              Screen Shot 2018-08-01 at 4.28.16 PM.png

               

              K, hope that helps!  Thx, Don

              1 of 1 people found this helpful
              • 4. Re: How to create dual axis map using census data and custom data?
                Leigh Michael

                Don,

                 

                This is incredibly helpful - and it worked like a charm! Thank you so much for your help.

                 

                Last question (and I can also start this as a new thread if you think this diverges too much from my original question)

                 

                Now, I'd like to make a 1/4-mile radius circle around each bus stop point on the map. Basically, my goal is to see the census blocks that each bus stop servces using the industry standard that a stop serves households located within 1/4 mile of the stop.

                 

                I looked at this thread but haven't been able to crack how to do it yet - I don't have any experience with SQL, so it's new territory for me.

                 

                Any advice? Again, I really appreciate your help!

                 

                Updated workbook is attached for reference.

                • 5. Re: How to create dual axis map using census data and custom data?
                  Don Wise

                  Hi Leigh,

                  That's great! I don't have much experience with doing buffers around the points.  Which is more due to my lack of time to attempt, than anything else! I would recommend closing this thread out and then posting a new request on the forums.  You can close this thread by marking my response as correct.

                   

                  There are at least 3-4 other folks actively monitoring the forums that should be able to help with your latest need.  Hopefully you won't need to do any SQL to make that work. 

                   

                  Try looking at this solution (Step 3 onwards)?  Make sure you use 3959 (for miles) instead of 6371 (for KM).  I'm assuming you'd use .25 for the parameter control...  hope it helps!

                   

                  Thanks!  Don