Spider Graph in Tableau

Hi Jenny,

I made a quick sample after doing some processing in QGIS.  I wasn't sure about the exact structure of your data, so I took a guess and started with two datasets that I made up:

• 'red dots' - a shapefile of 150 randomly generated points
• 'blue stars' - a shapefile of 10 randomly generated points

The hard part is keeping track of all of the field names (I think).  The short story on this is -

• make a distance matrix with the three closest blue stars for each red dot
• convert that new dataset from multipoint (each row in the table has the original red dot + the blue dot it's close to) into single point
• take the singlepoint dataset and convert points to lines using a unique ID for each of the multipoint pairs in the step above
• Join the attribute back into the new line dataset
• map in Tableau

If you want to try this out, I used QGIS 3.x and have attached my starter shapefiles and the result so you can play with the data.

I'm guessing you could also do this with Python if you wanted, and definitely with Postgres.  It feels like it may be easiest in Postgres, but if your data isn't already in the database it might be more work than it's worth.

Here are the steps I took to make the 'spiders' connecting each red dot to the three closest blue stars:

1. Use the Vector -> Analysis -> Distance Matrix tool to calculate the three closest blue star neighbors for each red dot.  There is an option to pare down and only return the N closest points, so I picked 3

2. The return from the distance matrix is a new dataset that has three rows for each point.

To make it easy to pull out individual 'lines' in the final dataset, I added a new column to the table that had an ID for each 'line' (each row in the table is going to turn into a line, so it's just an index giving the row number)  The new field is shown below, and I added another arrow to point out the 'field calculator' tool to add a new field.  The calculation is just $id

3. Each of those rows is a MULTIPOINT with two points in it (the red dot location and the blue star location that it 'connects' to).  We can't make lines from the Multipoints, so I then used the 'Convert multipoints to points' tool (which I can never find in a QGIS menu, so I just search in the processing toolbox to find it).  The convert multipoints to points tool will return a table with TWO rows for each Line...one for the red dot and one for the blue star that it connects to.

The resulting table:

4. QGIS has a nice tool for Convert Points to Line(s) (another tool I just find with the search toolbar in the Processing Toolbox), so I opened that and set the LineID to be my 'separate by...' attribute (this defines the grouping of the unique pairs of red dots and blue stars...it's the ID we created in step 2).  'Order by...' doesn't mean much here since there are only two points.

5. The result gives us a nice set of lines, but doesn't have the original attributes.

So, I just joined those back in from the matrix that I created in step 1

6. And then I exported the final file to a shapefile for mapping in Tableau (right click on the layer name and select export-> save features as...)

7. And mapped in Tableau:

Let me know if this works, or if it's totally  mystifying.  I just made this example sort of quickly so didn't do a lot of quality control

-Sarah

Tableau Research