Manufacturing a polygon from a single lat/lon coordinate

A little while ago I wrote a blog post on how to use pivot and densification to create great arc lines on a map without having to read the source data multiple times. In that example, each record had two fields I could pivot to create the two records – the from/to airport codes. But what if I don’t have a field that I can pivot? What if my data looks like this?

2016-06-17_13-00-18

Can I create this? (I think you can probably guess the answer…)

2016-06-17_13-03-28

The universal rule is that in order to draw a densified line/polygon we need two records – one marking the start point and the other marking the end point. We then use densification techniques to fabricate the intervening points and calculate their location.

You might think that this data set doesn’t have fields to pivot, but of course it does – lat and lon! In order to convert my single record into two records I pivot on these fields:

2016-06-17_13-09-06

We then use the pivot field name to generate out Point ID:

Point ID:
if [Lat&Lon] = "Latitude" then 1 else [NumPoints] end

And we unpivot our lat/lon values:

Unpivot Lat:
if [Lat&Lon] = "Latitude" then [Lat&Lon Value] end

Unpivot Lon:
if [Lat&Lon] = "Longitude" then [Lat&Lon Value] end

2016-06-17_13-14-11

We then replicate the lat/lon values to fill in the NULLs via LOD calculations:

Lat:
{fixed [Record ID] : max([Unpivot Lon])}

Lon:
{fixed [Record ID] : max([Unpivot Lon])}

And that’s the hard work done! We now have two records with a Point ID that ranges from 1 to a parameter-controlled value. We now create a bin on this Point ID field of width 1 that will drive our densification when used with table calculations like this:

Angle:
(6.28318 / [NumPoints]) * (index()-1)

Circle Lat:
window_avg(min([Lat])) + [SectorLength]*cos([Angle (circle)])

Circle Lon: 
window_avg(min([Lon])) + [SectorLength]*sin([Angle (circle)])

Now we can assemble our viz:

2016-06-17_13-28-47

What started me down this path was revisiting my old mobile towers viz to get it off the previous custom SQL UNION approach. It now runs faster as it only does a single pass over the data. The logic is the same as the example above but the calculations are (obviously) different as we are plotting a sector, not a full circle:

2016-06-17_13-34-25And finally (because I sometimes go a little nuts) I used the technique discussed in this post to eliminate the projection distortion from the circles in my original example, and allow the user to set the radius in real-world measurements.

All the working is available in the workbook, which you can download here.

Enjoy!

About Alan Eldridge

Hi. I'm Alan. By day I manage the sales consulting team for Tableau Software in Australia/New Zealand. By night, I'm a caped crusader. Or sleeping. Most often it's sleeping.
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to Manufacturing a polygon from a single lat/lon coordinate

  1. Pingback: Use Tableau to Create Census Tract Map – Data & Web

  2. ericksondata says:

    Alan, Great post. Even if I didn’t have a Lat and Long, I could just make two extra columns and pivot on those and then use the binning method to generate as many points as needed between them. Wow great stuff as always.

  3. Pingback: Custom Map in Tableau – Data & Web

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s