3 Replies Latest reply on Sep 7, 2012 2:29 PM by Wilson Po

    Calculating distance using Zip Codes

    Richard Khuu

      I have a data set that lists the name of a college/school and their zip code and I have another that has the zip codes of the student attending each college. I would like to determine the distance between the school zip code and the student zip code.

       

      Is there a way to do this in Tableau? I don't have the lat or longs for each school and researching each is not scalable.

       

      Thanks,

       

      Richard

        • 1. Re: Calculating distance using Zip Codes
          Wilson Po

          Hi Richard,

           

          This is possible in Tableau, but unfortunately generated latitude and longitude (the fields that Tableau generate to locate the zip code locations) cannot be used in a calculated field.  For that, you would need to have the zip code latitude and longitude in the data set to even start this. 

           

          A clever approach is that you can technically export the latitude and longitudes out and try to fit them in your data set. To do this: 

          (1) Create a simple sheet in Tableau that includes the Zip Code dimension somewhere on the sheet and the generated latitude and longitude measures in the Level of Detail shelf.

          (2) When you select "View Data", the summary level should provide you with a table of the lat/long for each zip code. 

          (3) You can copy this data out, and either join this in as a separate table in the data set or try to simply add the corresponding lat/long to the data as a new column.

           

          If you do this each time for the schools data set and the students data set, the new data sets should not contain their own latitude and longitude for you to run the calculations on.  Depending on the overall distance of these locations (i.e. whether they are within the same town vs across the nation) you can decide whether to use a calculation based of of Pythagorean Theorem (which is a good estimate for smaller distances) or one that accounts for the curvature of the Earth (More complex but more accurate especially for longer distances).  This is a little bit of a project, but absolutely possible and have been done many times before.

           

          Good luck!

           

          Wilson Po

          1 of 1 people found this helpful
          • 2. Re: Calculating distance using Zip Codes
            Shawn Wallwork

            Wilson Po wrote:

             

            This is possible in Tableau, but unfortunately generated latitude and longitude ... cannot be used in a calculated field.

             

            And the thinking behind this is? Any chance of changing this?

             

            --Shawn

             

            PS: I've been following you for awhile now; I was beginning to think I conjured you up! Good to know you actually exist!

            • 3. Re: Calculating distance using Zip Codes
              Wilson Po

              Good to know you've been watching.  I've been relatively busy for a while and have not had a chance to get back to the forums until today. 

               

              I actually don't have too much insight to the development of this area of product and so I can't really speak the thinking or development in this area.  I would encourage you to make the feature request in the correct forum area if you are finding that you need those fields in calculations a lot.  That said, my experience in the field suggests that the practical application of this would be limited - mapping is used by a portion of the user base and an even smaller portion needs to do any distance analysis. That said, this is only my 2-cents on the matter.