7 Replies Latest reply on Jul 6, 2017 10:46 AM by Erik Lask

    Map Radius Member by Site

    Erik Lask

      Grow your own Filled Maps

      Richard Leeke's Super-Charged ZIP Code Radius-Finder

       

      Referencing the two above posts, are showing really superb ways to visualize maps.  Top class!  I am looking to create something more like the second example however with multiple sites.  I haven't come across any examples of this kind so think this would be a first to see something like this.


      I have (for examples sake) 46 Doctor Sites spread out over 22 zip codes.  Within the same zip codes 2200 members.  I want to visualize 10 miles of each doctor site to see how many members are within this coverage area and how many remain outside of this coverage area.

       

      I want to ask Shawn Wallwork and Richard Leeke for your recommendation as you guys are the Data Monarchs leading the pack.

       

      I did a sample of doing a heatmap of members with how many doctor sites are in each zip code, but I want to step it up a notch.

       

      For starters I did a 10 mile coverage area with each of the 46 doctor sites

       

      I want to overlay it with member data to visualize the results.

       

       

      The fruit of what I'm looking to create would be a combination type of what was posted on Grow your own Filled Maps

      but to be able to identify what is actually happening like what was posted on Richard Leeke's Super-Charged ZIP Code Radius-Finder

       

       

      I have attached some sample data found from online housing data source.  Roughly 700 addresses with lat and long.  Maybe 20-30 addresses could be used as "doctor site" locations and the rest could be dubbed as "members".

      I don't know if anyone is up to a good challenge.  I have not found anything of the sort in the tableau community so know it would be a good learning exercise for people looking for similar results.

      I am attaching the data samples in a few different formats.

       

      Or if someone know has found a link describing a similar type post of what I am requesting please attach that.

       

      Any and all help would be much appreciated.

       

      All the best!!!!

        • 1. Re: Map Radius Member by Site
          Richard Leeke

          Gosh, I haven't thought about any of this stuff for years, I'll have to get my head back around how that all worked!

           

          A couple of quick comments off the top of my head, then I'll try to find time to think a bit more about it in the next day or two.

           

          One observation is that from what I understand of what you are wanting the approach is likely to be very different from what I did with Shawn on the "Richard's Boards" example. In that case we were doing a (very crude) estimate of the population inside the circle for each zip code, by apportioning the total population for the zip code according to the land area inside or outside the circle. I explained that in some detail in the comments on Shawn's post about Richard's Boards. In your case, from what I understand, you actually want to know which member sites are within X miles of a doctor site - based on the actual location of each.

           

          I think you can work that out using the distance formula used for drawing circles in countless posts - though it may be that you need to do that inside a Custom SQL connection. I have a sneaking suspicion that you won't be able to do it with table calculations (due to what I have always viewed as a missing addressing function in table calculations). I'll think more about this and see if I can dig out the history to explain what I mean.

           

          When it comes to actually visualising the results, the nicest approach might be to generate shapes from the overlap of all the individual X mile radius circles around the doctor sites, rather than drawing lots of overlapping circles. Doing that would mean creating custom shape files via a GIS system, though (much as I did for the zip/circle overlaps in Richard's Boards. That takes a lot of effort and may be overkill for what you are trying to do, though.

           

          If you do go that way, you will probably then be able to do the visualisation by connecting to your custom shape file directly from Tableau, now that Tableau supports shape files - i.e. no need for my tabgeohack utility any more.

           

          As I say, I'll try to find time to think this through in more detail and maybe put an example together for you.

          • 2. Re: Map Radius Member by Site
            Erik Lask

            Really appreciate your input, I will start researching your feedback and start seeing what I can come up with.

             

            To put it another way, I work in the healthcare industry and am looking to visually map out our coverage area.

             

            We have several Physician offices all over.  In this example I narrowed our coverage to a specific region.

            So within this region, for example lets say we have 46 PCP offices.  In that same region we have our members spread out, lets say 2200.  So a member is supposed to be within 10 miles to a PCP office.

            Realistically this is not always the case, which will provide feedback that we need more PCP offices.

            Visually I would like to show our coverage area, specifically members that fall out of the coverage area.  Live more than 10 miles to a Physician office.

             

            With possibility to breakdown percentage of members lie within coverage area.  If there is an overlap of 4 PCP sites and a member lives within 4, not sure visually how to depict that or if it is even necessary.  Might get to confusing with too many scenarios going on at the same time.  Main idea is to show our coverage area, where is it strong and where is it weak.

             

            Been banging my head on the concept of what is a way to depict visually.

             

            Thank you for your feedback, greatly appreciated.

            • 3. Re: Map Radius Member by Site
              Richard Leeke

              That's helpful to understand what you are trying to achieve. I can envisage various ways of presenting it - and possibly making it interactive to allow you to explore the distribution of members with multiple coverage. Maybe the ability to filter or highlight based on number of overlaps.

               

              Anyway - first thing is to wok out how best to structure the data and do the calculation of being inside the coverage area. If you get the data and calculation right the visualisation should be the easy bit (he says bravely).

              1 of 1 people found this helpful
              • 4. Re: Map Radius Member by Site
                Erik Lask

                Will give it some good thought today and see what I can come up with.

                My first crack at this project looked like this.

                A dual axis map where the blue dots are members and the orange are doctor sites.  Looks pretty frickin cool in my opinion.

                Want to somehow make it more interactive though with some kind of breakdown.  This is more or less a hodgepodge full of dots.

                Was really impressed with the super charged zip code finder.  Still thinking

                • 5. Re: Map Radius Member by Site
                  Mihai Constantinescu

                  Perhaps this can help Tinkering With LOD Expressions & Distance « Tableau Tinkering had similar issues recently (unsolved unfortunately)

                  • 6. Re: Map Radius Member by Site
                    Richard Leeke

                    I still haven't had a chance to have a good think about this - but I did remember a couple of old threads that might give you some more ideas and that also go into detail about some of the techniques you might need to use. In particular, these threads discuss how to calculate the distance from all instances of one set of locations to all instances of another set - which is essentially the same as your doctor and member locations.

                     

                    This thread about buffer zones surrounding oil wells, to allow for conflicts between planned work on different wells went into lots of detail and ran for ages. I've linked to my first posting on the thread which had links to several other useful historical threads.

                     

                    This post (on a thread which started out about something completely different but morphed over time) shows an example workbook I did showing population density and calculating "optimum location" - i.e. the location with the largest population within a given radius. This view shows the population within 25 km of every postcode in Germany (with Essen being the "optimum"):

                     

                     

                    Those two threads both sound to be right in the space you are thinking about.

                     

                    Unfortunately I see that a lot of the Tableau Public workbook links in those old threads no longer work - presumably workbooks have got broken by more recent Tableau updates. Let me know if there are any of mine that you think would be particularly helpful - I almost certainly still have the original workbooks - I'm an inveterate hoarder.

                    • 7. Re: Map Radius Member by Site
                      Erik Lask

                      Thanks again for taking the time to put some thought into my post and sharing the links.  I have been away on this one for a bit, (mentally and physically).  However I will be revisiting it shortly as maps are reoccurring deliverables for me.  The maps will be seen by our executive team via presentations.

                      The theme that I hear is trying to "tell a story".

                       

                      I will play around a bit with the above graphic.  It is really good work.  The main point for my task will be overlaying two sets of data.  Members, in relation to provider locations.  While the one above is great to depict members, would need to find a way to relate it providers "to tell the story".  Is the task I feel myself daunted with.