7 Replies Latest reply on Jun 18, 2019 11:00 AM by Tim Dines

    Grouping Data by distance

    Jared Hess

      Hello!

       

      New Tableau user here so bare with me if I am using wrong terms. I am trying to set up a dashboard that allows the users to see pieces of equipment with in a certain radius of the selected town. I need the user to be able to select what state they want to see, and then select the city they wish to have the radius around. I want the cities with more pieces of equipment to be larger.

       

      For example, if I select Indiana, and then select Indianapolis, I want to be able to see all the pieces of equipment for all the cities within a certain radius. Currently I am using a 50 mile radius. I have attached my workbook to this forum post.

       

      I have set a parameter to automatically fill in the select city. With that parameter, I have created a filter using the haversine formula to give me the distances I want.

       

      My only issue right now, is I just want to display the data on the right side of the dashboard, but what I running into is even after I have the filter in place, my table on the right still only shows me the equipment on that selected city instead of the selected city and all the cities within 50 mile radius.

       

      I am still new to Tableau so I know my dashboard/worksheets might look messy so I apologize for that.

       

      Any help would be greatly appreciated! Thank you!

        • 1. Re: Grouping Data by distance
          Tim Dines

          I have an example on Tableau Public that should help you to achieve this.

           

          Tableau Public

          • 2. Re: Grouping Data by distance
            Vikram Gokhale

            Not sure what am I missing here.. I don't see any table on right side... this is what I see in the dashboard.

             

            • 3. Re: Grouping Data by distance
              Jared Hess

              I have updated the workbook! Thanks for noticing this!

              • 4. Re: Grouping Data by distance
                Jared Hess

                Still need to group them into a table. Basically if I select Indianapolis, I need it also select all cities within 50 miles and add them as a group.

                • 5. Re: Grouping Data by distance
                  Tim Dines

                  This is the distance calculation:

                  3959 * ACOS

                  (

                  SIN( RADIANS([Latitude]) ) * SIN( RADIANS([Latitude]) ) +

                  COS( RADIANS([Latitude]) ) * COS( RADIANS([Latitude]) ) *

                  COS( RADIANS([Longitude])-RADIANS([Longitude]))

                  )

                   

                  Circle Longitude:

                  DEGREES(RADIANS( [Longitude] ) +  ATAN2(COS(([Radius])/3959)-SIN(RADIANS( [Latitude] ))*SIN(RADIANS( [Circle Latitude] )), SIN(RADIANS([Degree]))*SIN(([Radius])/3959)*COS(RADIANS( [Latitude] ))) )-90

                   

                  Circle Latitude:

                  DEGREES( ASIN(SIN(RADIANS([Latitude]))*COS(([Radius])/3959) + COS(RADIANS([Latitude]))*SIN(([Radius])/3959)*COS(RADIANS([Degree]))) )

                   

                  The above calculations define the circle.  The radius is a parameter that allows the user to choose any number of distances in miles from the center of the circle.  The Degree field is just a field with values 1 to 360.  I use this to draw a circle over the map, but you can use this same information to select the names or other dimensions of equipment that fall inside the circle.

                  • 6. Re: Grouping Data by distance
                    Jared Hess

                    I think my biggest confusion is, what would I replace the Degree field with?

                     

                    I have my distance formula calculated with:

                    3959 * ACOS(

                    SIN(RADIANS(LOOKUP(AVG([Lat & Long].[Latitude]), First()))) * SIN(RADIANS(AVG([Lat & Long].[Latitude]))) +

                    COS(RADIANS(LOOKUP(AVG([Lat & Long].[Latitude]), First()))) * COS(RADIANS(AVG([Lat & Long].[Latitude]))) * COS(RADIANS(AVG([Lat & Long].[Longitude])) - RADIANS(LOOKUP(AVG([Lat & Long].[Longitude]), First())))

                    )

                     

                    So when I click on Indianapolis, I want the circle to cover the range of distance for 25,50,75,100 miles. I have the parameter set up when I click on Indianapolis, IN it will automatically change the parameter to Indianapolis, IN. So I am able to plug in the parameter that way. Am I supposed to get the longitude/latitude of the selected city and replace it into the Degree field that you have?

                    • 7. Re: Grouping Data by distance
                      Tim Dines

                      The degree field is the 360 degrees around a circle.  I am looking at this as well.  It wasn't as straight forward as I made it sound.  Sorry to frustrate.  If you look at this information from Richard Leeke it will help.  Richard Leeke's Super-Charged ZIP Code Radius-Finder