Plotting hexagons in Power BI Icon Map visual: EP6

In the last Episode EP5 we created lists of hexagon coordinates in several ways. Next up, in this Episode 6, we will be finally converting these lists to Well-Known-Text geometry representations (#wkt) and show it in the Icon Map Visual. Also, we are then ready to show the hexagon in the icon map visual!

What is a well known text representation?

Well Known Text (WKT) is a standard language for representing geometric shapes on a map. It's a text-based notation that translates the map's spatial features into a string of text which can be easily interpreted and used in databases and geographic information systems (GIS). This language is used to encode various types of geometric shapes, including points, lines, and polygons, which can represent real-world objects like cities, rivers, and regions.

Examples include:

• "POINT (30 10)" which specifies a single coordinate pair in two-dimensional space.
• "LINESTRING (30 10, 10 30, 40 40)" represents a series of points connected by straight lines, forming a continuous path.
• "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" defines a closed shape with linear edges that connect back to the starting point. For instance our hexagons

You can put latitude longitude in the WKT representations, and with the right interpreter it can be shown on a map. For instance with the Icon Map visual in PowerBI

Generating some hexagon coordinates

We will start with 7 hexagons. This means the gridsize is 1: the offset to all directions from the center. We assume we look at H3 system level 8, so the average edge size is 461.35 meters. For simplicity, we put the center_lat and center_lon of the middle hexagon to (0, 0). In EP1 the basics on cube coordinates are shown, and it looks like this in short.

Now, instead of generating this grid I made a simple list in powerquery containing the cube coordinates of this table.

We need to have them as records for our functions to pick up on them later, so the table is converted to a list of records. After that (some pseudo code here) we convert the cubecoordinates to actual grid coordinates xy based on the edge size using a function hex2xy, followed by an optional rotatation function xy_rotate to get xyrot, and then a mapping to latitude/longitude coordinates latlon with function xy2latlon. Will be shown in a later episode when we generate the grids, hope you are still with me now

cuberecords = Table.ToRecords(yourcubetable)
xy = hex2xy(cuberecords, hex_size)
xyrot = xy_rotate(xy, precalc_gridtrigonometrics)
latlon = xy2latlon(center_longitude, center_latitude, xyrot)
hexcorners = getHexCorners(latlon, initinfo))        

We now have this (as the above was done in an addcolumns fashion)

And each list item in the gethexcorners column contains 6 records each with 2 hexagon coordinate fields, needed for the hexagon, in latitude and longitude. Summarizing: We now have a solid basis of 7 rows, depicting 7 hexagons, where the corners are shown in one column, each with a list of 6 coordinate sets.

Now lets generate some WKT polygons

We now need to find a way to efficiently convert {[Lat1 Lon1] ... [Lat6 Lon6]} to a polygonal text representation. The first important notice is that a polygon needs to be closed. So the endpoint should be the same as the start point. This means point 7 = point 1.

The below solutions/trials are all fairly close to each other. Perhaps when seeing this you think: well, that can be done better/easier. By all means let me know! But in the end: although their performance is definitively different, for the use case we have (up to about 10-100k of hexagons) it does not really matter all that much vs the loading of the real data.

In DAX however, when doing this with measures, speed is definitively much more important: each visual refresh triggers a regeneration of these wkt strings, which should be done as efficiently as possible. DAX is also mentioned below, but its not further explored to get it really as performant as possible for our usecase.

Approach

I cannot make it much more pretty than the image below. (maybe I could, but don't feel like it). The list with records (now shown as a table) needs to become a text string in the following way. The following functions show how to do this

WKT Trial 1:

• It grabs the first list item
• It makes a combined FirstCoord representation with a space in between
• It does the same for the full list again, also appanding a comma between each coordinate set
• It appends the first coordinate to the set as last coordinate
• It completes the text

All and all, quite a bit of & operations, which might be getting relatively slow on the long term

let
    getWKTFromHexCorners = (CornersList as any) as text =>
        let
            FirstCoord = List.First(CornersList),
            FirstCoordTxt = Text.From(FirstCoord[hexcorner_lon]) & " " & Text.From(FirstCoord[hexcorner_lat]),
            TransformList = List.Transform(CornersList, each Text.From([hexcorner_lon]) & " " & Text.From([hexcorner_lat]) & ", "),
            CombineText = Text.Combine(TransformList) & FirstCoordTxt,
            WKT = "POLYGON((" & CombineText & "))"
        in
            WKT
in
    getWKTFromHexCorners        

WKT Trial 2:

This version is already a lot more compact.

• It does not do the initial coordinate separate, but instead does a list.transform over the complete thing
• Only after that it appends the first text representation again to the end

let
    getWKTFromHexCorners = (CornersList as any) as text =>
        let
            CoordTextList = List.Transform(CornersList, each Text.From([hexcorner_lon]) & " " & Text.From([hexcorner_lat])),
            CombinedCoords = Text.Combine(CoordTextList, ", ") & ", " & CoordTextList{0},
            WKT = "POLYGON((" & CombinedCoords & "))"
        in
            WKT
in
    getWKTFromHexCorners        

WKT Trial 3:

• More use of Text.Combine instead of manual combining with &

let
    getWKTFromHexCorners3 = (CornersList as any) as text =>
        let
            CoordTextList = List.Transform(CornersList, each Text.Combine({Text.From([hexcorner_lon]), Text.From([hexcorner_lat])}, " ")),
            CombinedCoords = Text.Combine(CoordTextList & {CoordTextList{0}}, ", "),
            WKT = "POLYGON((" & CombinedCoords & "))"
        in
            WKT
in
    getWKTFromHexCorners3        

Comparing the above

I asked ADA to give me a rundown on the above and here is what it came up with. I did redact the answers a bit as I did not fully agree, but in general I think its a pretty accurate comparison. Melissa de Korte ??what are your thoughts? Again, in the end it does not matter all that much, but for the sake of argument....

Showing the mini grid in the Icon Map visual

Now that we arrived here, we run the function as another addcolumn and obtain the WKT in the previous example with 7 rows

Next, in Power BI download from the appsource the Icon Map Visual.

Select it, and as category (for now) add the WKT code. Also as size add the WKT code. This is all just for the sake of example: normally a unique identifier should be added as category, and as size a certain measure. Or a measure stating size = 1 if you always want to show all hexagons.

Unfortunately the Icon map will then still show this. The reason is, we didnt give it anything to plot yet. Only some categories and size.

To plot the WKT shapes, go to the format visual pane > section objects > and click the conditional function, where you set it to 'first WKT' for instance.

Immediately after that you will see that the hexagons got plotted! Hooray! Their location is in the middle of the sea to the west of Africa at lon lat (0,0) but their size is exactly as we wanted it, plus we can see they are aligned to eachother (which is not per se something for granted when i started on this)

DAX?

You could also add a measure to the f(x) button at the wkt options. So no precalculated WKT polygon, but a dax measure based on some properties. The below comes from a many-versions-back trial, but the idea is roughly the same: you have a certain lon/lat position of a hexagon, and then you can calculate its corners and make a WKT out of it. All in one measure. There is a bit of noise here in the dax measure related to H3, but if you skim through it the idea should be clear.

Why would you want this in dax? Well, first of all, because we can. But other than that: maybe you want something fancy and use a slider to change the latitude, longitude or rotation of the grid. Or maybe the number of hexagons to plot. In that case you need to generate the whole grid on the fly, so you also need to be able to plot the hexagons on the fly - with a measure. It passed my mind for a minute to make something like this but I let it go. Practical use in this case I find null. Or at least for now.

WKT Rotated measure = 
VAR _level = MAXX(FILTER(InitializeVariables,InitializeVariables[Name]="H3Level"),InitializeVariables[Value])
VAR center_lat = MAXX(FILTER(InitializeVariables,InitializeVariables[Name]="center_lat"),InitializeVariables[Value])
VAR center_lon = MAXX(FILTER(InitializeVariables,InitializeVariables[Name]="center_lon"),InitializeVariables[Value])
VAR edge_length = MAXX(FILTER(InitializeVariables,InitializeVariables[Name]="edge_length"),InitializeVariables[Value])
VAR edge_length_lat = edge_length / 1000 / 111.32
VAR edge_length_lon = edge_length / 1000 / (111.32 * COS(RADIANS(center_lat))) 
VAR rotation = MAXX(FILTER(InitializeVariables,InitializeVariables[Name]="angle"),InitializeVariables[Value])
VAR __rotated_lon = MAX(CalculateHexagonCenters[rotated_lon])
VAR __rotated_lat = MAX(CalculateHexagonCenters[rotated_lat])
RETURN
  
    "POLYGON((" & 
        CONCATENATEX(
            GENERATESERIES(0, 6, 1),
            FORMAT(__rotated_lon + edge_length_lon * SIN(RADIANS([Value] * 60 + 120 + rotation)), "0.000000") & " " &
            FORMAT(__rotated_lat + edge_length_lat * COS(RADIANS([Value] * 60 + 120 + rotation)), "0.000000") & 
            IF([Value] < 6, ",", "")
        ) &
        "))"        

Closing word

This completes the code to find plot the hexagon using WKT in this EP6! Once again, when you have read it its pretty straightforward, but I wanted to give you the versions I came through in my journey. I hope you found it entertaining.

In any case, let me know if you have some comments/remarks