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.
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.
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.
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. Date Country origin ZIP origin Country recipient ZIP recipient Quantity Packaging weight (kg) Cost 3 03.01.2014 Schweiz 9469 Deutschland 69214 2 Palette 621 € 110 4 03.01.2014 Schweiz 9469 Deutschland 93333 1 Palette 150 € 39 5 03.01.2014 Schweiz 9469 Deutschland 74321 4 Palette 760 € 114 6 03.01.2014 Schweiz 9469 Deutschland 97259 5 Palette 1451 € 177 7 03.01.2014 Deutschland 96052 Italien 29121 1 Palette 150 € 50 8 03.01.2014 Deutschland 96052 Italien 10034 1 Palette 151 € 56 9 03.01.2014 Deutschland 96052 Italien 20090 1 Palette 150 € 45
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)
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.
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"?)
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 :-).
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).
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?
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.
I took the 7 rows of data that you posted, assumed that you wanted the weighted center of the recipients, and built this:
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]
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.
geocoding and weighted centroid.twbx 692.7 KB
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,
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?
Hi Jonathan Drummey,
I've been trying to replicate what you showed above with my own data, however I seem to run into a problem when I want to proceed with making the calculations with my new long/lat-data that I joined with the source.
These long/lats are namely created as a dimension string and of course I want to change these to a measure in order to perform the calculations.
However when I try to change them to a number, either decimal or whole, almost all of them become 'null' values.
I tried to prevent this by creating a calculated field in the data source (INT(....)), but this also does not seem to solve my issue.
The weird thing is that a few of the lat/longs do convert just fine, but the majority does not (see attached picture).
Do you know what causes this or how I could prevent this from happening?
Left column: this is what happens when I try to change to number (decimal).
Center column: this is what happens when I use the INT(...) formula.
Right column: this is what the lat/longs look like at the beginning, seems to me like nothing is wrong with that format.
Thanks in advance!
Hi Lukas, a small thought to add to this this thread, but if you are able to use the Google geolocation API (or are willing to learn how) you can obtain actual route distances to the the locations on your map.
In fact--though not sure this is relevant to your application--this could be done dynamically for optimal routes based on current road and traffic conditions. Powerful stuff.
I can see from the screenshot that the Latitude (generated) field has a number data type, not a string data type as you’d indicated. In Tableau the data type change occurs before any calculated fields are computed so the conversion of values to Null is happening in the data type change, and therefore the Calculation1 isn’t doing anything that the data type change already did.
The way data type conversion/casting works in Tableau is like that of other databases - if the data type conversion fails the result is Null. The reasons for this with numbers include:
- the value contains spaces
- the value contains alphabetic or other special characters
- the value contains expected characters (like . or ,) but in an order that doesn’t make sense. One way this can happen is when the data was made in one locale like a European with a value like 48,54 and opened in a USA locale that expects 48.54.
I’m suspecting something to do with the latter, however without seeing the raw data I can’t tell exactly which of these might be the issue. If this isn’t enough to help you solve the issue then I suggest you post a sample of the raw data you are working with and we can check it out.
Hey there -- Easiest way to do this is:
1. Geocode the zipcodes in your source file with Latitude and Longitude as individual columns of data. These will be the measures you'll use in Tableau to plot your points. There are free geocoders available online, or you could use Google's API.
2. While conversion to Radians and X/Y/Z coordinates may be "proper", that really becomes important as you get closer to earth's poles. For 80% of the center of gravity analyses I've needed to do, you can get an accurate enough answer by simply averaging the Latitude column and then averaging the Longitude column within your source file. If you want to weight it by revenue or importance (for example), then do so in your source file calculations as a weighted average [e.g. in excel it's Sumproduct()/Sum()].
You may want to add another column in your source file for "Map Reference" and label your datapoints as "Locations" and the average of lat/longs as "Center of Gravity" ... this way you can bring this in as a dimension that can toggle a filter or color.
That's how i do it at least! Hope this helps.