9 Replies Latest reply on Mar 8, 2017 2:47 PM by Clint Larson

    Center of grativy (based on distribution data)

    Lukas Gächter

      Hello :-),

       

      We'd like to create a center of gravity model (geographical center) based on our distribution data, which should "look like" the picture below.

       

      center of gravity - example.png

       

      All the information like FROM / TO (country, ZIP, city, quantity, cost, weight) are available.

       

      To the question...

      Is it possible, to create this analysis in tableau?

      If Yes:

      1. How do we create it?
      2. Is it possible, to calculate the geographical center based on the road map (based on the distance a truck would need to drive on the road from A to B)?

       

       

      We hope we have provided all information, for you expertes to help us :-).

       

       

      Best regards

      Lukas

        • 1. Re: Center of grativy (based on distribution data)
          Jonathan Drummey

          Hi Lukas,

           

          If you have latitude and longitude then it's possible to do this as a general point on the earth via a set of calculations (I don't know them offhand, if you can't find them yourself then we'd need the help of someone like Noah Salvaterra ). If you don't have latitude & longitude you'll have to get that, while there are ways to extract lat & long from Tableau's internal geocoding you're generally better off setting up a process using an external geocoding source.

           

          Tableau doesn't have roadway data built in nor the functions to do that kind of calculation. You'd need a mapping tool such as QGIS or Alteryx and a database of roads, the latter can be the most expensive part to procure.

           

          Jonathan

          • 2. Re: Center of grativy (based on distribution data)
            noahsalvaterra.0

            That list is by no means comprehensive. A skilled R user may be able to pull this off as well. I agree with Jonathan, though, if you're looking to use road data then pre-processing is likely a necessity. I hesitate to declare anything an impossibility, but it would be fun to see someone prove me wrong, so here it is: Tableau can't do that (yet)!

             

            Ignoring #2 there are a couple questions that come to mind. I think I know the answer to the first one, and I don't think it complicates things terribly either way, but it is good to clarify anyway:

            1. Do the points have different weights? The bubbles are different sizes... so I'm assuming yes, and I don't think it complicates things terribly.

            2. Does it need to be Correct? How correct does it need to be? I'm not just being cheeky here. Lat-Lon coordinates are a way of indexing points on a sphere(ish) thing and corresponding them to points on a rectangular thing. Computing the center of mass should really be done in 3 dimensions, which means transforming the points onto a sphere and back again. I've done this in Tableau: The End of the World – by Noah Salvaterra | Drawing with Numbers, so it could be done... but I don't particularly recommend the all Tableau approach (unless it is necessary). Once in 3d center of mass should just be the (weighted) average in x, y and z coordinates, which is likely underground, but could be projected back to the surface to show on your flat map. If it is sufficient for this point to be roughly correct, then you could just take the (weighted) average of lat and lon coordinates individually. I suppose that is the center of mass in your flatland model of the earth, so is meaningful from a certain point of view.

            • 3. Re: Center of grativy (based on distribution data)
              Lukas Gächter

              Hey both of you :-)!

               

              Thanks for your response. I just looked into Tableau to test some ideas you gave me, though I have to face I'm no expert yet.

               

              Okay, to the questions:

               

              #2 - Road map .... Yes, let's forget about this, it's more "luxuary" then it's worth the effort.

               

              Jonathan Drummey

              No, I don't have latitude and longitude, all I have is the ZIP-Code and Country - but won't tableau create the latitude and longitude based on the ZIP-Code and Country?

               

              Below you can see all the Information I have (5000 rows at all, on excel):

                         

              Nr.DateCountry originZIP origin Country recipientZIP recipientQuantityPackagingweight (kg)Cost
              303.01.2014Schweiz9469Deutschland692142Palette621 € 110
              403.01.2014Schweiz9469Deutschland933331Palette150 €    39
              503.01.2014Schweiz9469Deutschland743214Palette760 € 114
              603.01.2014Schweiz9469Deutschland972595Palette1451 € 177
              703.01.2014Deutschland96052Italien291211Palette150 €    50
              803.01.2014Deutschland96052Italien100341Palette151 €    56
              903.01.2014Deutschland96052Italien200901Palette150 €    45

               

               

              noahsalvaterra.0

              1. The picture from the first post is from the Internet, sorry for that.

              We'd like to create the center based on the number of paletts (if it's the weight or the number of paletts should be no diffrence for the analysis).

               

              I took a screenshot from what I have already created in Tableau:

              (Colours are destination and the size is the number of paletts)

              center of grativy.png

              Now I just need another point which shows the center based on the sum, then it's done :-)

               

               

              2. What is "correct" ... if it shows the center in Mexico, then we know it's for sure wrong :-) ...the more accurate the better. At least I should know how inaccurate it is.

               

              noahsalvaterra.0 schrieb:

              Once in 3d center of mass should just be the (weighted) average in x, y and z coordinates, which is likely underground, but could be projected back to the surface to show on your flat map.

              X and Y axis should be enough, what is the third ("Z") axis for? (isn't tableau just a "flat map"?)

               

               

              noahsalvaterra.0 schrieb:

               

              then you could just take the (weighted) average of lat and lon coordinates individually.

              I think this sounds like a way that could be correct for my case? Or am I wrong?

               

              How do I create an averaged geographical centered point based on a sum?

               

               

              If my thinking is wrong, please tell me :-).

               

              best,

              Lukas

              • 4. Re: Center of grativy (based on distribution data)
                Jonathan Drummey

                Hi Lukas,

                 

                I’ll just respond to the bit you addressed to me around zip codes & lat/long:

                 

                What Tableau does when we put zip code into a view as a dimension to draw a map is 1) run the queries & aggregations & calculations on the data, then 2) look up the lat&long associated with the zip codes. In order to do the calculations you want the lat&long needs to be available for step #1, and Tableau doesn’t provide that in an automated way. As I’d noted we can manually extract them from Tableau to create a data source that has those but it’s a pain, if you really want instructions I can dig  them up (they changed in v10.0 and I haven’t tried it lately).

                 

                Jonathan

                • 5. Re: Center of grativy (based on distribution data)
                  Lukas Gächter

                  Hey Jonathan Drummey,

                   

                  Sorry for my late respond, I was on a business trip the entire week.

                   

                  Okay, hmm... actually, I don't wanna bother you with this, but it seems there is no way around?

                  If you can tell me what I could search for, I do maybe find something from someone who already explained it?

                   

                   

                  Lukas

                  • 6. Re: Center of grativy (based on distribution data)
                    Glenn Kuly

                    Hi Jonathon, if i understand your needs correctly, this is a hub-and-spoke problem that that is typically solved using combinatorial mathematics. Are you looking to actually find a solution with Tableau, or to visualize a solution that you have already found?

                     

                    Several months ago, one of my profs directed me to a couple open source operations research software platforms (i was working on a problem involving optimal employee scheduling at the time). I can try to dig up the platform names if you think it will be useful.

                     

                    -- Glenn

                    • 7. Re: Center of grativy (based on distribution data)
                      Jonathan Drummey

                      Hi Lukas,

                       

                      I took the 7 rows of data that you posted, assumed that you wanted the weighted center of the recipients, and built this:

                       

                      Screen Shot 2016-12-10 at 3.04.34 PM.png

                       

                      Here's an overview of how I built it:

                       

                       

                      High level order of steps

                       

                      1) (if we're going to use Tableau's geocoding, which I did): set up a datasource that we can use for Tableau's geocoding (see Plotting all points... below)

                      2) use Tableau's geocoding (see Using Tableau as a geocoder below)

                      3) Build out a data source and calculations to plot the desired weighted centroid (see Computing a weighted center... below)

                       

                       

                      Plotting all points when a data source has origin/destination pairs

                      1) union the source to itself

                      2) for each geographic field create a calculation with a formula like:

                      CASE [Table Name] //a unique field for each copy of the source

                        WHEN 'Table1' THEN [Origin Country]

                        WHEN 'Table2' THEN [Destination Country]

                      END

                       

                      Repeat this for each geographic field that you need to plot.


                      3) for each of the fields created in step 2 assign the appropriate geopgraphic role by right-clicking on the field and choosing Geographic Role->[correct geography]

                       

                      [You can see this in the getting lat & long data source]

                       

                       

                      Using Tableau as a geocoder:

                      (see http://drawingwithnumbers.artisart.org/wiki/tableau/mapping/ for some non-Tableau options for geocoding)

                      1) connect to your data source.

                      2) bring out your geographic dimension(s) so you have all your points.

                      3) right-click in an empty space of the view and View Data.

                      4) copy all the rows.

                      5) paste them into a new file (or another worksheet in the existing file)

                      6) join that back to the data source using either a cross data source join or a regular join.

                       

                      [this was done using the getting lat & long source, the data was pasted into sheet 2 and then a new Shee1 data source was created]

                       

                      Computing a weighted center & showing lines from original to the computed weighted center

                      This is based on the instructions from the A. Geographic Midpoint calc in http://www.geomidpoint.com/calculation.html

                      Pre-requisites: You *must* have the lat & long in the data source and must have the ability to union the source to itself. Also you'll need to have the table/source name in the view as a dimension in order to have enough points to draw lines from the origin to the centroid

                       

                      1) Union the source to itself.

                      2) Convert decimal lat/long into radians (the Step 1 fields)

                      3) Convert the lat/long into X/Y/Z coordinates (the Step 2 fields)

                      4) Weight the X/Y/Z coordinates

                      5) Convert the weighted X/Y/Z back to lat/long for the centroid points for one source while keeping the original lat/longs from the other source.

                      6) Build your map

                       

                      [You can see this in the Sheet1 data source and the raw data, workout, and view worksheets].

                       

                      In summary there are roughly three parts to this: 1) geocoding your data, 2) doing the weighted center calculation, and 3) drawing the map w/lines. Because Tableau only draws lines on maps when there are origin & destination pairs as separate rows in the data it takes some extra work to make sure the data is in the right shape and in this case that affects how we do things in parts 1 and 2.

                       

                      v10.0 workbook is attached.

                       

                      Jonathan

                      1 of 1 people found this helpful
                      • 8. Re: Center of grativy (based on distribution data)
                        Lukas Gächter

                        Hi Jonathan,

                         

                        This looks pretty much what I'm looking for!

                         

                        Let me work threw all the steps, then I'll come back to you. It will take some time for me to figure everything out but I'll not forget you :-).

                         

                         

                        Thanks a lot so far,

                        Lukas

                        • 9. Re: Center of grativy (based on distribution data)
                          Clint Larson

                          I'm trying to replicate this with US data, but it doesn't seem to be working. The only thing I can think of is around the negative numbers for Longitude. Has anyone had success with replicating this on US data?