Tuesday 29 May 2018

Labelling polygons that go across the prime meridian and its antimeridian


Consider a dataset describing polygons like the one below:

lat lon path polygons
1 1 1 1
-1 1 2 1
-1 -1 3 1
1 -1 4 1
1 1 5 1
1 179 1 2
-1 179 2 2
-1 -179 3 2
1 -179 4 2
1 179 5 2

Tableau doesn't allow you to label polygons directly, therefore the work around is to do dual axis on either latitude or longitude, and create a second layer using the average latitude and longitude of all the vertices of the polygon (key detail, remove the path from the level of detail, while you need it there for the polygon layer) to place the label. 

This works fine apart from any polygons that include points on either side of the antimeridian at 180 degrees longitude, using the convention of 0 to 180 for longitude east, and 0 to -180 for longitude west. The trick here is to detect if the maximum and minimum longitude have different signs and if difference between the maximum and the minimum longitude defines an angle smaller or greater than 180 degrees. In the latter case, the longitude convention needs to be changed to 0 to 360 for the averaging to give sensible results. To achieve that we create a calculated field as shown below and use it in place of the longitude.

if max([Lon])*min([Lon])<0 then
if max([Lon])-min([Lon])>180 then avg(if [Lon]<0 then 360+[Lon] else [Lon] end)
else avg([Lon]) end
else avg([Lon])
end

Sunday 27 May 2018

Spatial filtering by distance in km from a known point


 Tableau has supported the circular select tool for a few versions now. So the lazy way out is to use this select option, click at the known point on the map and then drag watching the radius of the circle until it reaches the desired value (tip: change the workbook locale to English Ireland for it to be in kilometres rather than miles). This only works for small distances though, what if you want to be a zoom level further out and look at hundreds and thousands of kilometres, or even use this distance to do something else such as filtering?

Let's port the haversine formula into Tableau

Assuming our fixed point is at 52N 0E (consider using parameters with lists of values if you have several points of interest), we create a calculated field step_1:

sin(radians(52-[Latitude])/2) * sin(radians(52-[Latitude])/2) +
        cos(radians([Latitude])) * cos(radians(52)) *
        sin(radians(0-[Longitude])/2) * sin(radians(0-[Longitude])/2)

Then the distance in km from the point defined in step 1 is given by:

2*[R] * atan2(sqrt([step_1]), sqrt(1-[step_1]))

Where R has been defined as the radius of the earth in km: 6371.


Sunday 20 May 2018

Using 24 colours in Tableau

Yet again Tableau tells me it's running out of colours
I've found a solution that works, it also adds a retro touch as it brings back some of the classic template colours us Tableau old timers grew to know and love. 
I click on edit colours, and I select the Classic 20 palette, and then I only change the colours corresponding to 20-23 where it's reusing colours for 0-3, making sure I select the colours in the old palette that no longer exist in the new. 

Friday 4 May 2018

BBC News website Carto election map epic fail

Meanwhile, in the land where elections happen on Thursdays, this is what happens to the public broadcaster website on the day after!