Hexgrid Shape Map

I had a requirement to represent arbitrary regions on a map of the UK. The regions didn’t have natural borders like counties or nations would, and after several attempts at ‘making up’ the borders I wasn’t seeing the effect I was after.

One of the issues was that smaller regions became too tiny to make visual sense when data was loaded into the map. I also found that the regions didn’t make much sense when compared to where the actual locations were (as in, they weren’t indicative of actual location density).

My solution was to use a ‘hexmap’, which looks like the below:

This can easily be achieved (I’m using QGIS here) with some default tools and plugins. After laying the hexagon grid over the UK, I trimmed off individual polygons to create an identifiable outline of the country.

The first challenge is to get the latitude and longitude values for each hexagon. This can be done in QGIS by converting hexagons to centroids (essentially a centre point inside each shape) and then using a calculated field, like so:

x(transform($geometry, layer_property(@layer, 'crs'),'EPSG:4326'))

This uses the ‘x’ property of the polygon and the map reference system to return a location value. By adding these, we can inspect the lat/lon for any polygon. I also added an ID field at this stage, as we will need that for linking to data points in Power BI.

After exporting this and converting to TopoJSON using the always-useful mapshaper we have a file that is ready to be used in Power BI.

Before we start building any visuals, we have some extra setup to do. First, we need a table to map the ID to the lat/lon values we have in the shapemap. This means we can use a single field (ID) to link a polygon to a datapoint, but use the related lat/lon values in DAX calculations. We can just build this lookup table directly in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddtbcis3DEXRufg7V0XwCY4llflPI2iTqkjYJ59x1pVb3eQBTTT//vunmf/89TPqq841l9mK//pj5VX3nrWt/fPPXw/aD7LX3rb7HFOiUR/UX9Vt7L6e//qzX9vK7ObzmnZMMR+9+JRmPKa9uq9eZjVp9rnqXrrvbkOZWY6xVkqt/2PsfDEfrffmW5p6zFjDxqhLmX2/l41S4z62Y0a1OXb8v2P6+V7us/kcktyvPmtx22tLM4+JezmWTZfmPlS3Xqy+b2Ey9xaObnPbrML0Ujg4YIxjA+bewmV9WJvq9vTSjunxxaY3eT1tqOFjLa7Pr7h3Z43m8eCrEEsNry/hR1TbzYb6JffW7b7jWnun6PfGxQ+eYSIuo5sawV+iqvH7JZoavV+iq7H7KdYduTFoSz8jzl8r7uOeZV0yOLhhJgY3yOLghnEObph9TPE+22rSeOEEgDFOAJjKCQDTOAFgOidAMvHzx4xXiZ/M9r6eNnr8I+vX9PMoPJ5EuRMA5j6u0Wvc5anNZFbDLE42GOd0g9mccNnUgikHYpx0MJXTDqZx4sF0Tj2YwcmXzbiP1HzbOnPkz3rtOVarca3H3EcaX3y3fW4PzMAkBblPNL513KDu0ixOZBjHRAbZnMjZzMKJDGOcyDCVExmmcSLDdE5kmMGJnI3/mvUaMRXjuu/vGvGhcU/3NeuYMmetXrY0v7d5vrxabVaaMmfBMF7Nlq3W5O86K4bv0ICZDA2YxdCAcYYGzGZoJDNLYWjAGEMDpiI0QBpDA6YzNGAGQwNmMjSyOeHjL4uPqfHgjzFfK5Y885o7xGKcrjLen5PMPKa3sqxZk+YOQ3OPMba6Mm2ogEpmqoBKZomASsRVQCWzVUB9m15EQCViKqCSqSqgkmkqoJLpKqCSGSqgkpkqoL7NCTF/TI2p8/vd52ttX/5c6zHjmBITy+yUJZg7elZcTOlmyiw7ATVWn09GSbMYPjDO8IHZDJ9szoruO3xgjOEDUxk+MI3hA9MRPiCD4QMzGT4wi+GTzCp3aDSPtV2p93N62V5i9B0ymD2ZOGMlk81UScTu0Jk7iKvfYzd2QsSg6JIsJlMmzmDKZCOXkqiFsZSJMZUyqQilLBozKZPOSMpkMJEymQykTBbzKBOxLZRIvyPhcyk0XyW+X63j/E0Tle+YVvY071sa+8FyCab+YLkE01RiJdOPiT+AY8S8kyaZcUxk2jibNiTzDE6vo4yzZKbZKhy/zSwqHJMxFY7JiK03GLGtBrNUYn0bvxmxzaz8XtyfmMgtnm6Zd2T45gomm10YNTDGrIG5I2P3tuOjXJqGQAK5A6OPEhH3ex9o7sCoxebcZzDD3JGxh5Vg+nMW0w/Gj+m9x0ja6mt5MYQbSGW6ZXNitETVbF77VE/CTxx8rzzGq/bq8ddOvWZz5ZFNK1x5wBhzBaYyV2AacwWmM1dgBnMFZjJXYBZyBcSZKzA3V6JE7Da6/JwhNgZh9nnsNmIO1SEf6ckDf03bXs/I6M+H+pp319+nc+EBs7nyyGYVxAqIMVZgKmMFpjFWYDpjBWYgVkAmYwVmMVZgnLECsxkr2ZwV8veqCuZmxueyCoa7xiBi1zibLbZOYcTWKcyNsBi5z1CVI2Pf8d6KBevqd+1yB2pc8oyLu7+rz2fdfVs92wqjEMYYhTCVUQjTGIUwnVEIMxiFMJNRCLMYhTDOKITZiMJMzrr8OwphjFEIU4/53ESDETvvMGLnHUbsqmfTuNMNIna6YcQONYzYoYbxY6L492amr+fOnFVjIeLanF2Z8qxXnj9vtNmimpRY3li9Db49iqomyZiqJslUUU0SaaqaJNNVNUlmqGqSzFTVJJklqkkirqpJMltVk28zi6omyZiqJsncufW5SIURLRAY0QLJZolmAYxoFsCIZgGMaBbAiGYBzFSlK5k7AW3EU516+Kw7AWMFtOswec1eVAlMxlQJ/DbbWALba8YaLmbaEZUFMAnRJElismglIZooSYgWShKb5epLxPAvrFaZGIpVFpW1KpPGUpWJaPdkIro9iRibIlmInkgmoiWSieiIZCIaIpkslqhM7gT5fIUhkbNW+C5imRhrWCaVJSyT9oO/mRJphiIXDyOmcN/F36ayyhGJNhCR6AMRiUYQkbNEEW3UKJheWKSIjFWKqLJMETXWKaLOQkUkemFEohlGxG4YjWiHEYl+GNAQnSwi0coiEr0sItHMIhLdLKLFEkrkrKFE9/3Ez3fHgGZhpSUyllqgtVjfIk+Lx3C38UbOEke0WeWAvLDQEYl+HVFltSPqrGZEg/WMaLKiEYkmIpHoIhKJNiLQFn1EItFIJBKdRCLRSiRiL5FGNBOJRDeRSLQTiZy1m2izemdkpbB+E92XSGtk/dCD194vkX5WeaLGOg9ki6U1As3in4z7ok8g0QslEt1QoFpYWonYNKXpLIhEordKNFkQiUQLlkg0YYnYhoVpohFLJFqxRGzG0oh2LJFoyBKJliyRaMoSibYskWjMEon39YF6YWUlMlZWosrKStRYWYGG6Ck/IfT8AXXfoYqZL5rKRKKrTCT6uERLlbqMXJW6jERLGGiJnjCRaAoTia4wkTiRQdRVqctINJiJpih12YguNJGrUpeROCYC9H5X/LvUZSQOihCJkyJETZS6bLoqdRkNVeq+US2ijxxTvtf4i/i+VhZoM+iBrDDoidiSphE9aSJxoIaIJ2poxJEaInGmhmgx6InEyRsicfQGqIqzN0Ti8A2ROH1DJI7fEHUGPdFg0AO9D4t9ppw997+0fd9pC9SYckSdKUckDg4RibNDROL4EBFPENGIQ0RA7zNsnylHJI4SEYnTRETivQEicaaIaCDlaCZTjmgx5YB2Z6SU1569PBf5RqIhTcRzTDTiIBOROMlEJI4yZdTeh/A+I4VIHGYiEqeZiERPnkg05YkGI4VoMlKIFiMF6P2e+mcQlGeUlvUs7S4Sh5qIxKkmInGsCaiz20sj2r1E4mQTkTjaRCQ6x0SidUw0GQREC0FAI5rQQEscXiqxZJllt/uaWiCeXqIRx5eAXJxfIhI9SSLRlCQSXUki0ZYkEn1JItGYJFpq+mYk2pdEon+ZUX+/Qv3fVCmvWDK4Py8fXsLOFQ1bVzTsXdGweUXD7hUN21c0O88RkMYWFw17XDCTW/wx07b5elpk13CHn4Yb/DTc36fh9j4Nd/dhFjf3abi3n80o3GaNQuPFR72HOsNwl5WGm6w03GOl4RYrjGHzlIR7pzDvE0gfQyMWWnN7VLH5NtyiouEOFQ03qGAGd5VgnLsAMZbix/acRLyGmwCf5p9/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, latitude = _t, longitude = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"latitude", type number}, {"longitude", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"latitude", "Latitude"}, {"longitude", "Longitude"}, {"id", "ID"}})
in
    #"Renamed Columns"

Next we need to calculate the closest hexagon lat/lon for each of our locations. These locations have their own latitude and longitude but that might not always exactly match a hexagon. Firstly, we calculate latitude as so:

CALCULATE(
    MIN(Map[Latitude]),
    FILTER(
        Map,
        Map[Latitude] > Store[Latitude]
    )
)

This gets the minimum latitude from our map lookup table where latitude is greater than that of the location.

Next, we use this DAX column to calculate longitude:

CALCULATE(
    MIN(Map[Longitude]),
    FILTER(
        Map,
        Map[Longitude] > Store[Longitude] &&
        Map[Latitude] = Store[Lat Calc]
    )
)

This is done in much the same way, but we look for rows in our lookup that have our pre-calculated latitude, before looking for the nearest longitude.

Once we have both values, we can create a combined key (in both our lookup and location tables) that can form a relationship:

Store[Lat Calc] & "_" & Store[Lon Calc]

Now we can apply the ID column from our lookup table to the shapemap visual, select our TopoJSON file and apply a measure for values. This gives us something like this:

The shapemap is using colour saturation to visualise our data – darker colours show either a high density of locations, a higher value in our measure, or both. They can be clicked to drill other visuals down to provide more detail.

A further build I did was to merge the polygons to simulate the regions the company was divided into. Some regions were just cities, with a high density of locations but actually a tiny area on the map. Using the hexagons meant they still had some good space on the map for users to interact with. I also split out London entirely, for obvious reasons:

This was a very satisfying project to work on. I think this has a wide range of applications, with a particular highlight being election mapping.