7 Replies Latest reply on Mar 30, 2018 7:52 AM by John O'Malley

    Dual Axis custom polygon multiple data source public health access map

    John O'Malley

      In the attached workbook "Confused as ****" I have three data sources:

      • Custom polygons for all counties within Illinois and all ZIP codes within one county. Created from ArcMap & ET GeoWizards. [Datasource named Custom]
      • Provider IDs with long/lat for each practice location in the state (so each provide IDs is found on one or multiple rows) [Datasource named Master]
      • Provider IDs with patient service details, including totals for age ranges, location category, and geographic service area (linkable to polygons data source). The way it's disaggregated, each provider ID is duplicated along many rows. [Datasource named Maximus]

       

       

      I want to produce a dual-axis map with provider locations as points and counties or ZIPs as polygons. When the user clicks on a point, two things happen:

      1. All the other points with the same provider ID are highlighted (showing all their practice locations in the state)
      2. All the polygons that are served by the provider are highlighted (and color ramped by the total patients served).

       

      My problem:

      The forum posts I've seen tell me everything has to be unioned into one data source in order to make the dual-axis map work. Is that true? I don't know how to achieve that given my three data sources. I'm hoping I can blend.

       

      As close as I can get:

      I've been able to achieve some of what I want I want with a single and far less detailed data source (see attached workbook Success with smaller scope...) But this example was easier because:

      • Each provider had only one location
      • Each provider had simpler patient data (no age/service/place category differentiation)
      • There were only county geographies. There were no custom polygons combining ZIP and County. Tableau could intuit counties automatically.
      • No joins were necessary

       

      This is for a public health data project showing access to care for Medicaid populations; your help with greatly assist us with our project. Note, none of the data is sensitive or governed by HIPAA. It was provided by a government agency that aggregated to levels that protect patient confidentiality.

        • 1. Re: Dual Axis custom polygon multiple data source public health access map
          Sarah Battersby

          Hi John,

           

          I can try to help you with this.  I think (but will not promise that) most, if not all, of this is possible and I can write up how to do the joining / unioning...but could you send me the shapefile(s) for your combined geographies? Using the filled polygon from vertices is going to make your project way more painful than it needs to be.  You could just use a shapefile and the geometries will be better optimized for working in Tableau.

           

          Could you send me the raw data as a big zip file so that I could play with the files tomorrow?

           

          -Sarah

          Tableau Research

          • 2. Re: Dual Axis custom polygon multiple data source public health access map
            Sarah Battersby

            Hi John,

             

            I re-constructed your point data and converted to polygons using QGIS this morning to make things a little simpler and I have a rough example of how you can get part of what you want in your viz (dual axis is working, polygons are shaded based on an attribute that I made up to fake 'patient count', and selecting any point on the map highlights the other locations with the same provider NPI).  But...I don't think you'll be able to get BOTH highlight points based on NPI AND county/zip based on the points that fall inside the counties with this dataset.    Here is an example of what I mocked up.  The workbook is attached.  Take a look and see if it's getting close and if so then I'll detail what I did to make it work like this (sort of complicated to describe so I'll wait to see if I'm even on target first ).

            The short story is that Tableau doesn't do selections separately by axis on a dual axis - so if you select all of the providers based on NPI we may have several counties that return...and if we select by county name that the providers fall into we would get all of the providers in that county.

             

            It might work better to do some clever sets of maps linked together in a dashboard, but how that works out depends on what the analytical questions are that you're targeting with this.

             

            -Sarah

            Tableau Research

            1 of 1 people found this helpful
            • 3. Re: Dual Axis custom polygon multiple data source public health access map
              John O'Malley

              Thanks Sarah! You're so quick. I was still working on making the shapefiles for you.

               

              I'm excited to examine the workbook you shared, and I'll get back to you by tonight

              • 4. Re: Dual Axis custom polygon multiple data source public health access map
                Sarah Battersby

                Hi John -

                 

                I have another attempt to share with you - I put it up on Public so that you could try it out.  It involves a parameter where you can select the NPI to do the search of the map, but once you pick an NPI I have it set up so that it will highlight all locations with that provider and the geometries that contain offices for that NPI. 


                I did a bit of pre-processing of the data in QGIS - joined the geometry IDs to the table with provider locations so that I would have a common attribute to use (group) and then blended the polygons and the csv datasets together to make the visualization. 

                 

                 

                I added in a table to show how many unique geographies are covered by any NPI so that it's easier to find interesting NPIs to select with the parameter (it's more fun when multiple places highlight).  I've also set it up so that you can just click on an NPI in the bar chart and have the map filter (but you don't get the highlighting on selected vs. not selected because it filters...I just wanted to put in both options for you to check out).  Here is what it looks like... and here is the link on Tableau Public

                 

                 

                Getting closer?

                 

                -Sarah

                Tableau Research

                1 of 1 people found this helpful
                • 5. Re: Dual Axis custom polygon multiple data source public health access map
                  John O'Malley

                  You're a rockstar Sarah.

                   

                  This is working really well. Wish I could open the workbook on 10.1 at work but I'm excited to play with the file at home tonight and see how you made everything purrr in the back end. I think I need to see the shapefile structure to understand what you did with QGIS (so I can replicate it with ArcMap). I also now have a cleaner shapefile so counties and ZIPs don't overlap.

                   

                  Nice touch with counting the NPI geographies so I could sample the providers with multiple locations

                  • 6. Re: Dual Axis custom polygon multiple data source public health access map
                    Sarah Battersby

                    Hi John,

                     

                    I think the main modification I made was just to the Maximus dataset (other than converting the point file that you were using to draw the geometries over so that I had polygons to work with).  I dropped the Maximus points into QGIS with the polygon dataset (counties / zip codes) and did a spatial join to add the polygon ID to each point.  It gives a light weight way to then blend the data back together (connect every point to the right geometry).  I've attached the modified Maximus_counties dataset so you can check it out.

                     

                    As for replicating in ArcMap, it's just a spatial join so easy-peasy to do in ArcMap. 

                     

                    When you have a chance to take a look through, if you have questions about how this is working let me know.

                     

                    -Sarah.

                    • 7. Re: Dual Axis custom polygon multiple data source public health access map
                      John O'Malley

                      Hi Sarah,

                       

                      Your solution worked well. I'm going with your bar graph selector idea.

                       

                      Need to redo the shapefiles still and add in the patient data to make it really shine -- can't wait to have this ready for public use.

                       

                      Thanks for all your help!