## 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?

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

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:

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```

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:

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:

And 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!

Hi. I'm Alan. By day I manage the APAC sales engineering team for Snowflake Computing. By night, I'm a caped crusader. Or sleeping. Most often it's sleeping.
This entry was posted in Uncategorized. Bookmark the permalink.

### 8 Responses to Manufacturing a polygon from a single lat/lon coordinate

1. 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.

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

3. Brian says:

Hello,
Just ran across this post. Great stuff. I wondered if you think it possible to draw multiple poly of differing length per azimuth?

• Hi Brian – glad you like the post. It’s a very flexible technique and you certainly could draw sector polygons of different lengths for each azimuth. You’d just have to make the SectorLength driven by a data value for each azimuth, rather than a constant parameter. Hope this helps!

4. Alec says:

Hi,

geat instruction, but how to get the last sector view If i open it in Tableau, i don’t see the last view?

Tnaks

Alec

• I just checked the workbook downloaded from the last paragraph. The sector view is shown on step 6.

• Alec says:

Brilliant! I was blank due to differet coordinate system, but i works now. Great Thanks!

Have you maybe work on showing the neighbours lines between source and target cell ?
BR

Alec