We recently spoke at SQLBits about how geospatial analytics is transforming thanks to modern cloud-native formats. Our talk, "Geospatial Meets Big Data: Transforming Location Intelligence," provided insights into emerging technologies reshaping location analytics.

During our presentation, we demonstrated how DuckDB could be embedded within Power BI to unlock advanced geospatial capabilities. This blog expands on our SQLBits talk, offering a more detailed exploration and practical examples.

Introducing DuckDB: A Lightweight Powerhouse​

DuckDB is an open-source, lightweight, SQL database designed specifically for analytical workloads. Unlike traditional databases, DuckDB runs completely in-memory, requiring no physical database setup. Its simplicity, speed, and minimal resource usage make it ideal for cloud-based geospatial tasks, particularly within tools like Power BI.

This lightweight capability prompted us to explore its potential in enhancing Power Query's geospatial processing capabilities.

Integrating DuckDB with Power Query​

Our first step involved setting up the necessary drivers. We downloaded the DuckDB ODBC driver directly from their official site DuckDB ODBC Driver and installed it locally. While exploring connectivity options, we discovered MotherDuck's custom DuckDB connector for Power BI. Initially promising, we soon realised its limitations, it provided basic table access without essential capabilities such as installing geospatial libraries or executing custom SQL. Thus, we decided to handle connectivity manually using Power Query M scripts.

After a few minutes of tinkering we realised something remarkable: we could spin‑up a fully fledged SQL database that lives only in RAM, meaning all the power of DuckDB with zero files, zero server process and zero install footprint. We instantiated the in‑memory DuckDB database with the following one‑line connection string:

ConnStr  = "Driver=DuckDB Driver;" &
           "Database=:memory:;" &
           "access_mode=read_write;" &
           "custom_user_agent=PowerBI;",

Direct Access to Overture Data with DuckDB​

Our initial practical use case was to dive straight into Overture Maps, a community-driven, open-source basemap project created collaboratively by Amazon, Microsoft, Meta, and TomTom, and hosted under the governance of the Linux Foundation. Overture provides comprehensive global map data, including detailed administrative boundaries, building footprints, transportation networks, points of interest, and more. This data is made available through regular monthly releases, which users can access directly from cloud storage platforms such as AWS S3 and Azure Blob. The diagram below provides an overview of Overture Maps' datasets and structure.

Using DuckDB within Power Query, we developed a reusable function to seamlessly integrate DuckDB SQL execution and the necessary geospatial libraries (spatial and httpfs) for accessing data directly from AWS S3 storage (Note: There is also a version of Overture Maps in Azure but we found S3 worked straight away so stuck with that).

Here's our reusable Power Query function:

//////////////////////////////////////////////////////////////////////
// DuckDBQuery  – execute arbitrary DuckDB SQL in an in-memory DB
// ---------------------------------------------------------------
// Parameters:
//   sqlText  (text, required)   – your SELECT / DDL / DML script
//   s3Region (text, optional)   – defaults to "us-west-2"
//
//////////////////////////////////////////////////////////////////////
let
    DuckDBQuery =
        (sqlText as text, optional s3Region as text) as table =>
        let
            // ── 1. choose region (default us-west-2) ──────────────────────
            region   = if s3Region = null then "us-west-2" else s3Region,

            // ── 2. one ODBC connection string to an in-memory DB ─────────
            ConnStr  = "Driver=DuckDB Driver;" &
                       "Database=:memory:;" &
                       "access_mode=read_write;" &
                       "custom_user_agent=PowerBI;",

            // ── 3. build a single SQL batch: install, load, set, run ─────
            BatchSQL =
                Text.Combine(
                    {
                        "INSTALL spatial;",
                        "INSTALL httpfs;",
                        "LOAD spatial;",
                        "LOAD httpfs;",
                        "SET autoinstall_known_extensions=1;",
                        "SET autoload_known_extensions=1;",
                        "SET s3_region = '" & region & "';",
                        sqlText
                    },
                    "#(lf)"                // line-feed separator
                ),

            // ── 4. execute and return the result table ───────────────────
            Result   = Odbc.Query(ConnStr, BatchSQL)
        in
            Result
in
    DuckDBQuery

Example: Extracting US County Boundaries​

Using our custom function, we started by directly accessing the area boundaries from the Overture Divisions theme. Overture data is stored as GeoParquet files in cloud storage, an enhanced version of the widely-used Apache Parquet format, which powers big data frameworks like Apache Spark and Trino. GeoParquet seamlessly blends geospatial processing capabilities with the high-performance analytics advantages of columnar data storage.

DuckDB is specifically optimised for this format and natively interacts with GeoParquet files directly in cloud environments using its built-in read_parquet function, thus enabling efficient queries against massive datasets without additional data transformations or transfers.

Specifically, we extracted county boundaries within Pennsylvania, USA.  We also leveraged DuckDB's ST_AsText function to convert binary geometries (WKB) into readable WKT formats that are fully compatible with Power BI and Icon Map Pro.

let
    Source = #"DuckDB Overture SQL Script"("
        SELECT id,
               division_id,
               names.primary,
               ST_AsText(geometry) AS geometry_wkt
        FROM   read_parquet(
                   's3://overturemaps-us-west-2/release/2025-01-22.0/theme=divisions/type=division_area/*',
                   hive_partitioning = 1
               )
        WHERE  subtype = 'county'
          AND  country = 'US'
          AND  region  = 'US-PA';", null)
in
    Source

Here was the data returned:

The query executed in around 25 seconds, retrieving all 67 counties. This is impressive given the dataset contains over 5.5 million divisions within a 6.4GB GeoParquet file, accessed in-place from AWS cloud storage.

Addressing Column Truncation Issues​

Initially, our geometry data was truncated to 200 characters due to metadata handling limitations between Power Query and the DuckDB ODBC driver. When the ODBC driver does not expose column length information, Power BI defaults to a maximum of 200 characters for string columns. To resolve this, we explicitly cast our geometry data to a wider column type.

CAST(ST_AsText(geometry) AS VARCHAR(30000))

This ensured the most geometry polygons were retrieved successfully but a few were still over the 30000 character limit.

Simplifying the Polygons​

To simplify the polygons, DuckDB has another handy function ST_SimplifyPreserveTopology.

Also to simplify usability, especially for analysts unfamiliar with SQL, we extended our PowerQuery function, allowing users to query Overture themes, specify column selections, conditions, and simplify polygons directly via function parameters.

//////////////////////////////////////////////////////////////////////
//  OvertureQuery – default boundary simplification                 //
//////////////////////////////////////////////////////////////////////
let
    OvertureQuery =
        (
            theme               as text,    //Overture maps theme 
            ctype               as text,    //Overture Maps Dataset
            optional columnsTxt       as nullable text,  //list of columns, defaults to all 
            optional includeGeomWkt   as nullable logical, //include the geometry as WKT  
            optional whereTxt         as nullable text,  //the where clause to filter the data
            optional s3Region         as nullable text,  //options s3 region, defaults to us-west-2
            optional simplifyTolDeg   as nullable number   // degrees tolerance, default 0.0005, 0=none 
        ) as table =>
        let
            // ── defaults ────────────────────────────────────────────────
            region         = if s3Region        = null then "us-west-2" else s3Region,
            colsRaw        = if columnsTxt      = null or columnsTxt = "" then "*" else columnsTxt,
            addWkt         = if includeGeomWkt  = null then true else includeGeomWkt,
            tolDegDefault  = 0.0005,                    // ≈ 55 m at the equator
            tolDeg         = if simplifyTolDeg = null then tolDegDefault else simplifyTolDeg,

            // ── geometry expression ────────────────────────────────────
            geomExpr       = if tolDeg > 0
                             then "ST_SimplifyPreserveTopology(geometry, " &
                                  Number.ToText(tolDeg, "0.############") & ")"
                             else "geometry",

            // ── SELECT list ────────────────────────────────────────────
            selectList     = if addWkt
                             then Text.Combine(
                                      { colsRaw,
                                        "CAST(ST_AsText(" & geomExpr & ") as VARCHAR(30000)) AS geometry_wkt" },
                                      ", ")
                             else colsRaw,

            // ── WHERE clause (optional) ────────────────────────────────
            whereClause    = if whereTxt = null or Text.Trim(whereTxt) = ""
                             then ""
                             else "WHERE " & whereTxt,

            // ── parquet URL ─────────────────────────────────────────────
            parquetUrl     =
                "s3://overturemaps-" & region & "/" &
                "release/2025-01-22.0/" &
                "theme=" & theme & "/type=" & ctype & "/*",

            // ── ODBC connection string (in-memory DuckDB) ──────────────
            ConnStr =
                "Driver=DuckDB Driver;" &
                "Database=:memory:;" &
                "access_mode=read_write;" &
                "custom_user_agent=PowerBI;",

            // ── SQL batch ───────────────────────────────────────────────
            SqlBatch = Text.Combine(
                {
                    "INSTALL spatial;",
                    "INSTALL httpfs;",
                    "LOAD spatial;",
                    "LOAD httpfs;",
                    "SET autoinstall_known_extensions = 1;",
                    "SET autoload_known_extensions   = 1;",
                    "SET s3_region = '" & region & "';",
                    "",
                    "SELECT " & selectList,
                    "FROM   read_parquet('" & parquetUrl & "', hive_partitioning = 1)",
                    whereClause & ";"
                },
                "#(lf)"
            ),

            // ── run and return ──────────────────────────────────────────
            OutTable = Odbc.Query(ConnStr, SqlBatch)
        in
            OutTable
in
    OvertureQuery

Now we can easily invoke queries without writing any SQL and also simplify the polygons:

Source = #"DuckDB Overture Query with Simplify"(
    "divisions",
    "division_area",
    "id, division_id, names.primary",
    true,
    "subtype = 'county' AND country = 'US' AND region = 'US-PA'",
    null,
    null)

We now have all polgons with a WKT string under the 30K limits, so we can easily visualise it in Icon Map Pro

Pushing the Limits: Querying 1.65 Million Buildings in NYC​

Encouraged by initial success, we challenged DuckDB further using the significantly larger buildings dataset. Previously, querying these datasets using Apache Spark in Databricks resulted in poor performance. DuckDB, however, excelled even on our local laptop.

Now, we wanted to efficiently query buildings within a specific area, and the most effective way to do this is by using a bounding box. A bounding box defines a rectangular region with minimum and maximum coordinates, providing a quick way to filter geographic data. Ideally, we wanted to avoid manually calculating these coordinates. We noticed during our earlier exploration of the Overture divisions theme that each area already included a bbox column containing these coordinates. This gave us the idea to leverage these existing bounding boxes to easily geocode a city or area name. Once we had the bounding box, we could efficiently retrieve all buildings within that region by simply filtering based on these numeric values, eliminating the need for complex spatial queries.

The following is a Power Query function we created to easily retrieve a bounding box based on a city or area name and its standard 2-character ISO country code:

//////////////////////////////////////////////////////////////////////
//  GetCityBBox – returns "lat1,lon1,lat2,lon2" for a settlement    //
//////////////////////////////////////////////////////////////////////
let
    GetCityBBox =
        (
            cityName   as text,          // e.g. "London"
            isoCountry as text,          // ISO-3166 alpha-2, e.g. "GB"
            optional s3Region as nullable text
        ) as text =>
        let
            // ── query the locality polygon -----------------------------
            rows =
                OvertureQuery(
                    "divisions",
                    "division_area",
                    "bbox.xmin, bbox.ymin, bbox.xmax, bbox.ymax",
                    false,                                            // no geometry_wkt
                    "subtype = 'locality' and " &
                    "country = '" & isoCountry & "' and " &
                    "names.primary ILIKE '" & cityName & "%'",
                    null,                                             // no extra bounding box
                    s3Region,                                         // defaults to us-west-2
                    0                                                 // no simplification
                ),

            // ── pick the first match (refine filter if ambiguous) -----
            firstRow =
                if Table.IsEmpty(rows)
                then error "City not found in divisions data"
                else Table.First(rows),

            // ── assemble lat/lon string -------------------------------
            bboxTxt =
                Number.ToText(firstRow[bbox.ymin]) & "," &
                Number.ToText(firstRow[bbox.xmin]) & "," &
                Number.ToText(firstRow[bbox.ymax]) & "," &
                Number.ToText(firstRow[bbox.xmax])
        in
            bboxTxt
in
    GetCityBBox

Filtering with Bounding Boxes​

We then modified our primary query function to include bounding box filtering capability:

//////////////////////////////////////////////////////////////////////
//  OvertureQuery – with optional bounding box                   //
//////////////////////////////////////////////////////////////////////
let
    OvertureQuery =
        (
            theme               as text,
            ctype               as text,
            optional columnsTxt       as nullable text,
            optional includeGeomWkt   as nullable logical,
            optional whereTxt         as nullable text,
            optional boundingBoxTxt   as nullable text,   // lat1,lon1,lat2,lon2
            optional s3Region         as nullable text,
            optional simplifyTolDeg   as nullable number  // tolerance in degrees, 0 ⇒ none
        ) as table =>
        let
            // ── defaults ────────────────────────────────────────────────
            region         = if s3Region        = null then "us-west-2" else s3Region,
            colsRaw        = if columnsTxt      = null or columnsTxt = "" then "*" else columnsTxt,
            addWkt         = if includeGeomWkt  = null then true else includeGeomWkt,
            tolDegDefault  = 0.0005,                    // ≈ 55 m at the equator
            tolDeg         = if simplifyTolDeg = null then tolDegDefault else simplifyTolDeg,

            // ── geometry expression ────────────────────────────────────
            geomExpr       = if tolDeg > 0
                             then "ST_SimplifyPreserveTopology(geometry, " &
                                  Number.ToText(tolDeg, "0.############") & ")"
                             else "geometry",

            // ── SELECT list ────────────────────────────────────────────
            selectList     = if addWkt
                             then Text.Combine(
                                      { colsRaw,
                                        "CAST(ST_AsText(" & geomExpr & ") AS VARCHAR(30000)) AS geometry_wkt" },
                                      ", ")
                             else colsRaw,

            // ── optional bounding-box condition ───────────────────────
            bboxCondRaw    =
                if boundingBoxTxt = null or Text.Trim(boundingBoxTxt) = "" then ""
                else
                    let
                        nums      = List.Transform(
                                        Text.Split(boundingBoxTxt, ","),
                                        each Number.From(Text.Trim(_))),
                        _check    = if List.Count(nums) <> 4
                                    then error "Bounding box must have four numeric values"
                                    else null,
                        lat1      = nums{0},
                        lon1      = nums{1},
                        lat2      = nums{2},
                        lon2      = nums{3},
                        minLat    = if lat1 < lat2 then lat1 else lat2,
                        maxLat    = if lat1 > lat2 then lat1 else lat2,
                        minLon    = if lon1 < lon2 then lon1 else lon2,
                        maxLon    = if lon1 > lon2 then lon1 else lon2
                    in
                        "bbox.xmin >= " & Number.ToText(minLon, "0.######") &
                        " AND bbox.xmax <= " & Number.ToText(maxLon, "0.######") &
                        " AND bbox.ymin >= " & Number.ToText(minLat, "0.######") &
                        " AND bbox.ymax <= " & Number.ToText(maxLat, "0.######"),

            // ── combine WHERE pieces ───────────────────────────────────
            whereTxtTrim   = if whereTxt = null then "" else Text.Trim(whereTxt),
            wherePieces    = List.Select({ whereTxtTrim, bboxCondRaw }, each _ <> ""),
            whereClause    = if List.Count(wherePieces) = 0
                             then ""
                             else "WHERE " & Text.Combine(wherePieces, " AND "),

            // ── parquet URL ─────────────────────────────────────────────
            parquetUrl     =
                "s3://overturemaps-" & region & "/" &
                "release/2025-01-22.0/" &
                "theme=" & theme & "/type=" & ctype & "/*",

            // ── ODBC connection string (in-memory DuckDB) ──────────────
            ConnStr =
                "Driver=DuckDB Driver;" &
                "Database=:memory:;" &
                "access_mode=read_write;" &
                "custom_user_agent=PowerBI;",

            // ── SQL batch ───────────────────────────────────────────────
            SqlBatch = Text.Combine(
                {
                    "INSTALL spatial;",
                    "INSTALL httpfs;",
                    "LOAD spatial;",
                    "LOAD httpfs;",
                    "SET autoinstall_known_extensions = 1;",
                    "SET autoload_known_extensions   = 1;",
                    "SET s3_region = '" & region & "';",
                    "",
                    "SELECT " & selectList,
                    "FROM   read_parquet('" & parquetUrl & "', hive_partitioning = 1)",
                    whereClause & ";"
                },
                "#(lf)"
            ),

            // ── run and return ──────────────────────────────────────────
            OutTable = Odbc.Query(ConnStr, SqlBatch)
        in
            OutTable
in
    OvertureQuery

Now we were ready to query the building outlines, along with a bunch of additional attributes that might be useful for further analysis. So let’s pull it all together and really put it to the test by attempting to extract all the buildings in New York City. We used our bounding box function to locate the spatial extent of the city, then passed this to our main query function.

let
    NewYorkBBox      = GetCityBBox("City Of New York", "US"),
    NewYorkBuildings =
        OvertureQuery(
            "buildings",
            "building",
            "id, names.primary, class, height, subtype, class, num_floors, is_underground, facade_color, facade_material, roof_material, roof_shape, roof_color, roof_height",
            true,
            null,
            NewYorkBBox,
            null,
            null
        )
in
    NewYorkBuildings

Amazingly, within around 5 minutes, we had extracted all 1.65 million buildings in New York City, complete with building outlines and a wealth of rich attributes. Even more impressive was that Power BI's resource usage remained minimal.

Spatial Joins: Assigning Buildings to Neighbourhoods​

That is pretty cool, but even though Icon Map Pro can display up to half a million polygons on a map, 1.65 million buildings is just too many to visualise effectively. To manage this, we needed a way to filter buildings by area. Unfortunately, the Overture data does not provide a direct foreign key between buildings and divisions such as neighbourhoods, so we needed to perform a spatial join. No problem, however, because we can do exactly that using DuckDB's spatial functions.

This required switching back to SQL, as the query is more complex. We first selected the New York neighbourhoods from the division_area layer, then retrieved all the buildings within the city’s bounding box, and finally performed a spatial join using ST_Intersects to associate each building with its corresponding area.

let
    Source = #"DuckDB Overture SQL Script"("
    -- 1.  Pull the New York City divisions you care about
WITH areas AS (
    SELECT
        id                    AS area_id,          
        names.primary         AS area_name,
        subtype,
        geometry              AS geom
    FROM read_parquet(
             's3://overturemaps-us-west-2/release/2025-01-22.0/theme=divisions/type=division_area/*',
             hive_partitioning = 1
         )
    WHERE country = 'US'
      AND region  = 'US-NY'
      AND subtype IN ('neighborhood')
),

-- 2.  Load buildings inside the city’s overall bounding box first
bldg AS (
    SELECT
        id              AS building_id,
        names.primary   AS building_name, 
        class, 
        height, 
        subtype, 
        class, 
        num_floors, 
        is_underground, 
        facade_color, 
        facade_material, 
        roof_material, 
        roof_shape,
        roof_color, 
        roof_height,
        geometry AS geom
    FROM read_parquet(
             's3://overturemaps-us-west-2/release/2025-01-22.0/theme=buildings/type=building/*',
             hive_partitioning = 1
         )
    WHERE bbox.xmin > -74.3 AND bbox.xmax < -73.5
      AND bbox.ymin >  40.4 AND bbox.ymax <  41.0
)

-- 3.  Spatial join: which building sits in which area?
SELECT
    a.area_name,
    a.subtype,
    b.building_id,
    b.building_name, 
    b.class, 
    b.height, 
    b.subtype, 
    b.class, 
    b.num_floors, 
    b.is_underground, 
    b.facade_color, 
    b.facade_material, 
    b.roof_material, 
    b.roof_shape,
    b.roof_color, 
    b.roof_height,
    ST_AsText(b.geom) AS geometry_wkt
FROM bldg b
JOIN areas a
  ON ST_Intersects(a.geom, b.geom);
    ", null),
    #"Filtered Rows" = Table.SelectRows(Source, each true)
in
    #"Filtered Rows"

Incredibly, we managed to join all 1.65 million rows in memory within Power BI on a standard laptop. Even more impressive, when monitoring system performance, Power BI Desktop was only using about 10% of CPU and under 4GB of memory, barely 1GB more than its normal baseline usage. Definitely some voodoo magic going on somewhere!

Visualising Results in Icon Map Pro​

Finally, we visualised the results using Icon Map Pro within Power BI, colouring the buildings by type, and cross-filtering buildings by neighbourhood, type, height, and material properties.

So, What's the Catch?​

Currently, the only limitation is that DuckDB's ODBC driver isn't included by default in Power BI Service. Hence, similar to other third-party ODBC drivers, a Power BI Gateway server is required. Thankfully, setting this up is straightforward, simply spin up an Azure VM, install the DuckDB driver, and configure it as your gateway. You’ll then fully leverage DuckDB's capabilities in your Power BI cloud environment and be able to refresh on a schedule like any other data.

Explore the Possibilities​

We encourage you to explore and innovate with this approach, leveraging Overture Maps or your own spatial datasets. Experiment freely and share your exciting findings with us!

You can download a copy of the PBIX file here which includes all the powerquery functions and some other goodies like using DuckDB for generating H3 hexagons, another blog post coming soon on that one!

When explaining how to configure Icon Map Pro, for simplicity and ease of understanding, I usually show the data as a single flat table. This is after all how Icon Map Pro receives the data as the result of the DAX queries that Power BI generates on the fly as you slice and filter your report. However, it's generally regarded as best practice to model your data as facts and dimensions represented as a star schema.

Consider the following model representing crime data:

This model has a single fact table, 'Crime', with three dimensions, 'Crime Type', 'LAD' and 'LSOA'. The fact table has 2 measures, Crimes and Distance. The LAD (Local Authority District) and LSOA (Lower-level Super Output Area) dimensions represent different levels of geography and form a geographic hierarchy. Each of these dimensions have the longitude and latitude centroids which we might use for drawing circles on the map, and a code field that we might use for joining to a shape file to show the geographic boundaries on the map.

So lets start with drawing circles on the map, with the ability to drill down from LAD to LSOA level. To draw circles on the map, we need to provide Icon Map Pro with ID, Longitude, Latitude and Circle Size fields.

First of all we drag the 'pk_LAD ID' and 'pk_LSOA ID' fields from their respective dimension tables. This then generates a hierarchy in the visual, and the drill-down icons should appear in the corner:

We can drag our 'Crimes' field from the fact table into the 'Circle Size' box.

We now need to provide Longitude and Latitude values. However, we have these in two different tables depending on the drill-down level. For this case we're going to need to use DAX measures to return the appropriate fields.

First of all I'm going to create measure to return the current level in the hierarchy. I'm creating this as a separate measure so we can reuse it in multiple other measures.

Current Level =
SWITCH (
    TRUE (),
    ISINSCOPE ( LSOA[pk_LSOA ID] ) && HASONEVALUE ( LSOA[pk_LSOA ID] ), "LSOA",
    ISINSCOPE ( LAD[pk_LAD ID] ) && HASONEVALUE ( LAD[pk_LAD ID] ), "LAD",
    BLANK ()
)

It checks to see if the the keys we're using in the map are currently in scope and then returns either "LSOA" or "LAD" accordingly. The HASONEVALUE check is so that we only return a value where something is present in the data.

Now we know which level of the hierarchy we're at, we can create measures for longitude and latitude that return the values from the appropriate tables.

Latitude_Measure =
VAR _CurrentLevel = [Current Level]
RETURN
    SWITCH (
        TRUE (),
        _CurrentLevel = "LSOA",
            CALCULATE (
                AVERAGE ( LSOA[Latitude] ),
                TREATAS ( VALUES ( Crime[fk_LSOA ID] ), LSOA[pk_LSOA ID] )
            ),
        _CurrentLevel = "LAD",
            CALCULATE (
                AVERAGE ( LAD[Latitude] ),
                TREATAS ( VALUES ( Crime[fk_LAD ID] ), LAD[pk_LAD ID] )
            )
    )

and

Longitude_Measure =
VAR _CurrentLevel = [Current Level]
RETURN
    SWITCH (
        TRUE (),
        _CurrentLevel = "LSOA",
            CALCULATE (
                AVERAGE ( LSOA[Longitude] ),
                TREATAS ( VALUES ( Crime[fk_LSOA ID] ), LSOA[pk_LSOA ID] )
            ),
        _CurrentLevel = "LAD",
            CALCULATE (
                AVERAGE ( LAD[Longitude] ),
                TREATAS ( VALUES ( Crime[fk_LAD ID] ), LAD[pk_LAD ID] )
            )
    )

These measures return the longitude and latitude values from the appropriate tables according to the current level of the hierarchy. Whilst we're using the AVERAGE function on the values, at the time of evaluation, there should only be one value per LAD or LSOA, so the values aren't actually changed - the average of one value is that one value.

Because the relationship between the LAD and its child LSOAs is defined within the fact table, the filter direction on the relationship will mean that it's not able to return the longitude and latitudes for just the child LSOAs, so we use TREATAS to use the key from the fact table to ensure that the hierarchy relationship is represented.

With these measures, we can now configure our visual.

If we're creating a filled map using a shape file, then we can simply drag in the the appropriate fields in to the "Feature Reference" box from each dimension table:

or we can create a measure in the same way as we have for longitude and latitude and drag that in instead:

using this measure

Feature Reference =
SWITCH (
    [Current Level],
    "LSOA",
        CALCULATE (
            MAX ( LSOA[LSOA] ),
            TREATAS ( VALUES ( Crime[fk_LSOA ID] ), LSOA[pk_LSOA ID] )
        ),
    "LAD",
        CALCULATE (
            MAX ( LAD[LAD] ),
            TREATAS ( VALUES ( Crime[fk_LAD ID] ), LAD[pk_LAD ID] )
        )
)

Here's the resulting Power BI report

You can download the Power BI report file to see how it is all put together.

With SQL Server now in public preview inside of Microsoft Fabric, I was keen to investigate it's spatial data functions. Whilst the Event House has provided spatial data processing inside of Fabric since the outset with KQL database, I was excited when the SQL engine arrived as it's a more familiar environment to those working with spatial data. It also opens up the door for doing realtime processing of that data based on interactions in a Power BI report.

Fabric SQL has strong support for geospatial datatypes and functions opening up a wide range of operations such nearest neighbours, distance calculations, boundary intersections etc etc.

In this example I wanted to keep things simple and show properties within a selected distance from selected postboxes. The postboxes would be selected by a Power BI slicer, as would the distance required. Then on making these selections, SQL server would then find the properties within those search radiuses.

First of all I needed some data to use, so I extracted from OpenStreetMap the locations of all the postboxes in Buckinghamshire in the UK using the QuickOSM plugin for QGIS. I also did the same for all the buildings in Buckinghamshire. There's only partial data for building outlines in Buckinghamshire, a better datasource for these would have probably been Ordnance Survey data. I saved these two separate data extracts in CSV format with WKT geometry.

So with my data ready, I headed over to Microsoft Fabric, and was quickly and easily able to create SQL database in just 2 clicks. Importing the data was also straightforward using DataFlows Gen 2 to upload the CSV file into OneDrive and import into 2 tables within the database using the familiar PowerQuery interface. The only downside here is that PowerQuery can't create fields of type geography so the WKT data is added as a text field. But this is quickly remedied once the data is loaded.

I also added spatial indexes on the columns containing the spatial data to improve performance.

With my data loaded I was now ready to create a function to to perform the spatial searches when triggered from Power BI. I opted to use a table function as I can then parameterize the search criteria using PowerQuery parameters - I've not been successful in achieving this with stored procedures.

From Power BI, I pass in a list of comma separated postbox references and the distance in meters to search. The first thing we need to do is process that list of postboxes:

WITH RefList AS (
    SELECT TRIM(value) AS ref FROM STRING_SPLIT(@Ref, ',')
)

With that done we can use the STDistance spatial function to calculate the distance between the buildings and the selected postboxes, and return those within the specified distance:

-- Return buildings where the distance from the postboxes is less than @Distance
SELECT 
    b.full_id, 
    b.geom.STAsText() AS WKT, 
    b.building,
    'building' AS itemtype
FROM dbo.buildings AS b INNER JOIN dbo.postboxes AS p ON p.ref IN (select ref from RefList)
WHERE b.geom.STDistance(p.geom) <= @Distance

As well as showing the matched properties in the results, I also wanted to show the locations of the postboxes, so we use a UNION ALL to add the postbox locations into the results:

UNION ALL

-- Return a points for the postbox locations
SELECT 
    full_id,
    p2.geom.STAsText() AS WKT,
    '' AS building,
    'origin' AS itemtype
FROM dbo.postboxes AS p2
WHERE p2.ref IN (select ref from RefList)

And then finally I also wanted to return the search radius to show on the map:

UNION ALL

-- Return the perimeter of the search areas as polygons
SELECT
    p3.ref + 'perimeter' AS full_id,    
    p3.geom.STBuffer(@Distance).STAsText() AS WKT,
    '' AS building,
    'search_perimeter' AS itemtype
FROM dbo.postboxes AS p3
WHERE p3.ref IN (select ref from RefList)

Here's the complete function:

ALTER FUNCTION dbo.fn_GetBuildingsWithinDistance
(
    @Ref      nvarchar (4000),
    @Distance FLOAT
)
RETURNS TABLE
AS
    RETURN
    (

    WITH RefList AS (
        SELECT TRIM(value) AS ref FROM STRING_SPLIT(@Ref, ',')
    )

    -- Return buildings where the distance from the postboxes is less than @Distance
    SELECT 
        b.full_id, 
        b.geom.STAsText() AS WKT, 
        b.building,
        'building' AS itemtype
    FROM dbo.buildings AS b INNER JOIN dbo.postboxes AS p ON p.ref IN (select ref from RefList)
    WHERE b.geom.STDistance(p.geom) <= @Distance

    UNION ALL

   -- Return a points for the postbox locations
    SELECT 
        full_id,
        p2.geom.STAsText() AS WKT,
        '' AS building,
        'origin' AS itemtype
    FROM dbo.postboxes AS p2
    WHERE p2.ref IN (select ref from RefList)

    UNION ALL

    -- Return the perimeter of the search areas as polygons
    SELECT
        p3.ref + 'perimeter' AS full_id,    
        p3.geom.STBuffer(@Distance).STAsText() AS WKT,
        '' AS building,
        'search_perimeter' AS itemtype
    FROM dbo.postboxes AS p3
    WHERE p3.ref IN (select ref from RefList)
);

This is all the setup required within Fabric SQL, so now over to Power BI Desktop.

I was able to connect to Fabric SQL in the same way as I'd connect to any SQL DB, so that was easy, and I simply imported the postbox table as import mode into my Power BI model. I was't worried about any geometry here, just the reference numbers, although in real life, I'd have imported additional reference data for improved searching and selection.

Now to bring in the building search results. I next made to parameters in PowerQuery used to pass the selected postboxes and the search distance.

Now we're ready to create the connection to the table function. As we want the searching to be done interactively, we need to ensure the connection to the table function is made using Direct Query. This means that the SQL query is executed and new results returned every time we interact with the slicers (or other visuals) in the report.

After making the initial connection, I then edited the query in the Advanced Editor. This is because we need to add a little code to handle the multi-selection of postboxes. There's likely a better approach for this, but this seems to work well. First of all I handle a single value vs multiple, and then create a comma separated text string with the selected values:

Note we're using the parameters postbox_ref and range that I configured earlier.

Here's the M code:

let
    // Normalize postbox_ref to a list if it's a single string
    normalizedList = if Value.Is(postbox_ref, type list) then postbox_ref else {postbox_ref},
    
    // Combine into a single comma-separated string
    commaSeparated = Text.Combine(normalizedList, ","),

    Source = Sql.Database("yourdatabaseservergoeshere.database.fabric.microsoft.com", "spatialsql-314d84ad-21da-438c-9caf-76f51b7259d9"),
    dbo_fn_GetBuildingsWithinDistance = Source{[Schema="dbo",Item="fn_GetBuildingsWithinDistance"]}[Data],
    #"Invoked Functiondbo_fn_GetBuildingsWithinDistance1" = dbo_fn_GetBuildingsWithinDistance(commaSeparated, range)
in
    #"Invoked Functiondbo_fn_GetBuildingsWithinDistance1"

That's the data connections sorted. Now over to Power BI's report editor. The queries should load in all the postbox references into a table, and the table function should be called using the default values defined in the PowerQuery parameters.

We now need to set up the interactivity part of the report. So I added a slicer based on the post code reference.

But this won't currently change how the table function is called - we need to link up this to the Power Query function. We do this in the Power BI Model View.

Selecting the postcode reference field on the right, shows the properties panel. We need expand the Advanced section and in the "Bind to parameter" section, select our PowerQuery postbox_ref parameter. And then also enable Multi-Select.

This then binds this field to the PowerQuery parameter. When we select an item in the slicer now, that value is passed into the SQL query.

We also need to do something similar for the distance selection. For this I created a numeric range parameter:

This then provides us with a table of numbers, and a slicer, but we need to bind it to the PowerQuery parameter before we can use it. So back in the Model view, bind it to the range parameter a we did for the postbox_ref parameter, but this time don't enable Multi-select:

And now we're ready to display the results, which of course I used Icon Map Pro for.

The field configuration is straight forward, I add the full_id field into the ID field, and the geom field into the Image / WKT field:

Then in formatting settings for Icon Map Pro, in the Data Layers I just enabled WKT / GeoJson (from data):

And set up some conditional formatting for the buildings vs the postboxes and search radius:

eg:

Here's a video showing it in action:

Vector tiles unlock all sorts of possibilities in Icon Map Pro as they allow us to place complex lines and polygons on the map without having to load large files into the report. This means we're not as constrained by the amount of memory we have to play with as a Power BI custom visual - and therefore we can load more data into the visual as a result - we only need to send 2 columns of data to the visual, plus the formatting options.

For example this report contains the details of all 11 million properties in the Netherlands, and we can reference nearly half a million of them at a time on the map, setting the formatting options based on data in the Power BI dataset.

The challenge has been with vector tiles, is that they need to be generated and hosted somewhere - usually requiring a GIS server such as ArcGIS, GeoServer or a SaaS service such as Mapbox, which may charge by transaction or require setup and hosting. PMTiles however is a cloud-native geospatial format that enables the tiles to be held in a single file which can be hosted in simple cloud storage such as Azure blob storage or an AWS S3 bucket. These are generally cheap storage options, and have no compute cost associated with them.

So how do we go about creating PMTiles? In the coming months, at Tekantis, we'll launch an Azure and/or Microsoft Fabric service to generate them, but in the meantime there are open source tools we can use.

This guide goes through the process of setting up and using the tools for the first time.

The source data​

We're going to use the UK's local authority boundaries as our source data. These are obtained from the Office for National Statistics' Geoportal. You'll see me use these files a lot in demos and there are number of options for each file.

Often I'm loading them as Esri shape files into the visual, so I'll chose the "generalised clipped" version, which Icon Map Pro will go on to further simplify. The challenge with that is that by simplifying the boundaries we lose some of the detail. For example, where I live, the local authority boundary runs down the centre of the road, and depending on which side of the road you live, children are eligible for free school transport - or not - as the different local authorities have different eligibility criteria. Therefore for this example I'm going to download the full clipped version and download it in GeoJSON format - which will already be using Longitude and Latitude coordinates, rather than British National Grid coordinates. This file is over 190mb, so far too large to upload into the visual without using vector tiles, but it has the most amount of detail - ensuring that those precise boundaries are preserved.

Install Tippecanoe​

I've found the best tool for creating vector tiles is Tippecanoe - originally created by Mapbox. Recently Felt created a fork of Tippecanoe that natively supports PMTile generation.

For Power BI developers who will be typically using a PC, Tippecanoe an causes an issue as it doesn't run on Windows - it runs on OSX or Linux.

It is possible to run Linux alongside Windows using the Windows Subsystem for Linux (WSL). Microsoft have a comprehensive guide to setup, but here's the short version:

Run cmd or Powershell as administrator (Press the Windows key and R, type cmd and right click on Command Shell. Then select 'Run as Administrator').

Then within the command shell, type

wsl --install

This will install Linux using the Ubuntu distribution:

Once installed, start Linux and we're ready to install Tippecanoe.

First though we need to update the compiler:

sudo apt-get install gcc g++ make libsqlite3-dev zlib1g-dev

then

make

then

make install

With that done we can now install Tippecanoe:

$ git clone https://github.com/felt/tippecanoe.git
$ cd tippecanoe
$ make -j
$ make install

The good news is, you don't need to do these steps again!

Generate the PMTiles file​

Now we have all the prerequisites installed we can create our tiles. We can copy our geojson file from the downloads folder into the Linux file system. In windows explorer (Windows key and e) enter \\wsl$.

This will show the linux file system.

Navigate to the home directory, then the folder for your user name, and then tippecanoe.

Copy your file here.

Back in Linux, we're ready to run Tippecanoe. We're going to create a file called localauthorities.pmtiles in a layer named lad using our source file Local_Authority_Districts_December_2022_UK_BFC_V2.geojson

Run the following command:

tippecanoe -zg --projection=EPSG:4326 -o localauthorities.pmtiles -l lad Local_Authority_Districts_December_2022_UK_BFC_V2.geojson

Tippecanoe will now process the file and generate the PMTiles output file.

Back in windows explorer you should be able to see the newly generated file. You may need to press F5 to refresh Windows Explorer:

You'll notice that the generated file is around 15mb, 175mb smaller than the original.

Hosting the file​

Now we've generated the PMTiles file we need to host it somewhere. I'm going to use Azure Storage.

You can create a new Storage Account by clicking the new button in the Azure portal:

Provide a name for the storage account, and the remaining options should work as the defaults.

Once created we need to configure a couple of bits.

Firstly enable anonymous access to the blobs. (It is possible to configure secured access via SAS tokens)

And then configure CORS:

Create a row with Allowed origins set to * and the Allowed Methods set to "Get" and "Head".

Save these settings.

Now you can create a container to store the PMTiles file:

Click on the container to enter it.

Click the upload button to upload the PMTiles. Remember you can type \\wsl$ to navigate to the linux file system. Your file will be in home\username\tippecanoe

Once uploaded, click on it and copy the URL:

In my case the URL is:

https://iconmapproexamples.blob.core.windows.net/examples/localauthorities.pmtiles

Check the layer has worked​

We can use the PMTiles Viewer tool to check that it has worked.

Navigate to https://pmtiles.io/ in your browser

and paste in your URL and hit Load URL.

You should now see your layer displayed:

Enabling "show attributes" enables you to see the properties related to each shape as you mouse over them.

Use the PMTiles layer in Power BI​

Now in Power BI Desktop, add Icon Map Pro to your report canvas.

I have a dataset with the local authority codes and names in it - these match those from the original GeoJSON file.

I've dragged the code field into both the ID and Feature Reference fields in Icon Map Pro:

This will be used to create the join between the Power BI data and the properties in the vector tile layer.

Now in the settings, firstly enable WebGL Rendering. PMTiles are only available with this enabled.

Enable Vector Tiles in Data Layers:

In Vector Tiles - Setup, enable 'Use PMTiles' and 'Auto Detect Bounds'

Paste in the URL of the PMTiles layer:

https://iconmapproexamples.blob.core.windows.net/examples/localauthorities.pmtiles

Your report should now show the layer. Shapes that have matched the Power BI data will be shown in blue. Those that have not been matched to Power BI data will show in grey:

Icon Map Pro will attempt to join the Power BI data using the data in the Feature Reference field. It tries to match the values here against the values in each of the properties in the vector tile layer's shapes. This can sometimes generate false positives, so you may want to specify a specific property to match against:

I've also chosen here to remove features that haven't been matched, so the grey shapes are not shown.

You can now go ahead and apply conditional formatting as normal to format the shapes.

Additional notes​

You may find that when generating small shapes, these aren't shown correctly at low zoom levels. There is guidance on how to deal with thin within the documentation.

I thoroughly recommend reading more on PMTiles concepts and background.

Kudos and full credit to Brandon Liu for developing the PMTiles format.

Unlike the AzureMaps and ArcGIS Power BI visuals, Icon Map Pro doesn't have the ability to geocode data - to take a postal address or placename and turn it into a longitude and latitude to display on the map. This was an intentional decision for a number of reasons:

1. Doing this on the fly creates a performance hit before displaying the data, and it has to be done for each user browsing the map.
2. For security - many of our customers don't want the visual to be able to process data remotely.

A better approach is to geocode the data up front. You can then control where this process happens, and do it once as part of the data ingestion, meaning there is no delay or workload duplication.

Here's a guide to using the Azure Maps geocoding API in PowerQuery to convert addresses to longitude and latitude coordinates.

In this example I have a table of London hospitals and their addresses.

To add the geocoding functionality into PowerQuery I'm going to add a function.

Add a New Source as a Blank Query:

then click the Advanced Editor button on the toolbar.

Replace the default contents with this M code:

let
    // Function to call the Azure Maps Geocoding API
    CallAzureMapsGeocodeApi = (ApiKey as text, Address as text) as record =>
    let
        // Define the API endpoint
        ApiUrl = "https://atlas.microsoft.com/search/address/json?api-version=1.0&subscription-key=" & ApiKey & "&query=" & Uri.EscapeDataString(Address),
        
        // Make the GET request
        Response = Web.Contents(ApiUrl),
        
        // Parse the JSON response
        JsonResponse = Json.Document(Response),
        
        // Extract the first result
        FirstResult = if List.Count(JsonResponse[results]) > 0 then JsonResponse[results]{0} else null,
        
        // Extract the latitude and longitude
        Coordinates = if FirstResult <> null then FirstResult[position] else null,
        
        // Return a record with latitude and longitude
        Result = if Coordinates <> null then [Longitude = Coordinates[lon], Latitude = Coordinates[lat]] else [Longitude = null, Latitude = null]
    in
        Result
in
    CallAzureMapsGeocodeApi

Rename your query to Geocoder

Then click back on your query containing the address data

On the toolbar, in the "Add Column" section, click the "Invoke Custom Function" button:

Enter Location as the column name. Select Geocoder as the Function query. Then under ApiKey click the button and select 'Text'.

Enter your Azure Maps API Key. This is shown as the "Primary Key" in the "Authentication" section within the "Settings" section of your Azure Maps account.

Then under Address select Column Name and select the address column.

Click OK.

The function will now be called for the rows in your table:

Click the expand button in the new Location column header:

Untick the "Use original column name as prefix" checkbox

And click ok.

You should now have Longitude and Latitude columns in your table.

Highlight the two columns and change the Data Type to Decimal Number.

Click the Close & Apply button to load the data into your Power BI dataset.

You can now configure Icon Map Pro with your geocoded data:

Version 1.0.9.2 of Icon Map Pro is rolling out via Microsoft AppSource this week. Whilst we quietly rolled it out in January in preview, this release pushes one of our most requested features, clustering of circles and images, into general availability.

Whilst we already supported the clustering of circles, the options were limited. This enhanced version uses our new WebGL rendering capabilities to cluster both circles and images. It also introduces the ability to cluster based on a category field. This category field is then used to set the colour for the donut and pie chart segments, and the bars on the column and bar chart options. You can even apply the colour values to categories for images so that these can be included in the donut segments.

We've also introduced tooltips on the clusters so you can see the breakdown. As you'd expect from Icon Map Pro, there are lots of customisation options for both formatting and behaviour.

This functionality requires WebGL Rendering Mode to be enabled.

We’re submitting the next version of Icon Map Pro to Microsoft today for publishing on AppSource in a couple of weeks. I’m quite excited about this release for a number of reasons, but one of the main ones is our initial support for PMTiles.

I’ve recently been working with a customer who needed to create a choropleth (filled) map for Canada. Canada is a challenge as it has the largest coastline of any country, but really compact population centres, meaning that to represent census boundaries, we need to be able to plot very large polygons with a detailed coastline, whilst at the same time, really small polygons that might only cover a few blocks in a city centre.

In terms of doing this in Power BI, we’re unable to use a GeoJSON or shape file as they’re either too large – or once simplified, lose too much definition. As an example, the official GeoJSON file for Canada’s dissemination areas is over 1gb – far too large to import into a Power BI report by some orders of magnitude.

My normal recommendation in these cases is to use Well-Known Text (WKT) shapes loaded into the map via Power BI data model. But Power BI has a maximum number of characters limit that, even with the usual workarounds, is not enough to accommodate some of Canada’s largest territories and provinces.

This doesn’t mean it’s not possible to achieve already with Icon Map Pro though. We can use vector tiles to only retrieve the shapes that need to be displayed on the part of the map that we’re looking at, and at a resolution appropriate for the current zoom level. The issue with vector tiles is that they needed to be either pre-processed, and/or served using a GIS server. This could be an enterprise GIS solution such as Esri’s ArcGIS, an online provider such as Mapbox or an opensource server such as GeoServer. All provide workable solutions, but it’s an additional provider, and sometimes significant cost to add to the mix.

Enter PMTiles. PMTiles provides a new cloud-native way to serve vector (or raster) tiles. Designed to use a highly efficient compression algorithm, the resulting PMTiles file is a single file that sits, usually in cloud storage. It requires a server that supports HTTP range requests – supported by Azure Blob Storage and Amazon S3 buckets. Rather than the whole file being downloaded each time, HTTP range requests are used to download just the segment of the file for that tile. And the file sizes are much smaller than the original – meaning storage costs are minimal – and there’s no compute costs. No GIS server required. Canada’s 1gb dissemination areas file is less than 100mb as a PMTiles file.

This Power BI report, uses 4 levels of drilldown to present Canada’s census data from Territory/Province level, through Census Divisions, Census Sub Divisions to Dissemination Areas. As well as simply displaying the file, in Power BI we use the embedded properties to link it back to the data in Power BI data model, and use conditional formatting to create our filled, choropleth map on the fly. We’re able to either drill into each area at a time, or simply view all 75,000 dissemination areas at the same time.

And that's not our only use for PMTiles, we've also added some additional background maps thanks to Protomaps.

To create the PMTiles files used in this report, I used the open-source tool, Tippecanoe, but we’re working on building a Microsoft product range that will incorporate this capability, to make it easy to generate your own PMTiles in future. Watch this space - there's so much more coming!

In our February release of Icon Map Pro, you’ll notice a new settings option, “Rendering”, so I thought I’d explain in more detail what’s behind this option, and why it’s there.

Let’s start by explaining a bit more about the code libraries that Icon Map Pro uses to draw the map. If you go back to when I first created the original Icon Map visual nearly 10 years ago, there were 2 main opensource libraries used for creating web maps, OpenLayers and a more lightweight library, Leaflet.

The original Icon Map was built on top of Leaflet, which provided great support for most of the features required for mapping in Power BI. It also had a wide range of plugins available which added some of the more advanced capabilities.

At Tekantis, when we started to write Icon Map Pro last year, we didn’t want to provide fewer capabilities than were available in the older Icon Map visual, so again we chose Leaflet to ensure we were able to provide the full range of features.

However, Icon Map Pro is capable of drawing more rows of data than the original visual, which was limited to 20,000, then later 30,000 rows. When drawing large amount of complex objects, or images on the map, Leaflet starts to suffer from performance issues. We therefore started to look at how to overcome this.

A lot has happened in the web mapping world in the last 10 years. Volodymyr Agafonkin, who created the original Leaflet library, started to work for Mapbox and created a new map library that was built using WebGL rendering, using the power of the GPU for better performance. Whilst this library was initially open source, at some point Mapbox forked the library to create version 3, after which a Mapbox license key was required to use it.

The version 2 library became the Maplibre project and development of this library has continued separately to the Mapbox library. It’s this Maplibre library that is the foundation for many other mapping libraries such as the Azure Maps JavaScript API.

In Icon Map Pro we’ve been using a plugin that uses Maplibre on top of Leaflet to provide some of the features such as our vector tiles based background mapping. We’ve also provided some experimental layers using WebGL to provide better performance for rendering large GeoJSON layers for example. However, using this plugin has limitations as each layer using WebGL created a separate instantiation of Maplibre and WebGL, and we therefore started to hit limitations in regard to the number of WebGL instances possible, and in terms of memory.

In the February release of Icon Map Pro, we’re removed the experimental WebGL options (for new maps) and replaced them with a new WebGL rendering option. This now uses a single Maplibre instance to draw all the overlay and data-bound layers, which means we don’t hit the same limitations. It’s also enabled some additional functionality where items overlap on the map, such as combined tooltips.

To enable this, we’ve made it a single choice between using WebGL via Maplibre vs traditional rendering via Leaflet. In this initial release, not all overlay and data-bound layer types are available, but we’re planning to enable all of them going forward.

Currently we’re still using the Maplibre plugin for Leaflet, so all the current controls are available. The downside of this however, is that additional features such as the ability to rotate and tilt the map are not available, as these aren’t supported by Leaflet.

In a future release, we’re aiming to provide an additional option that will only use Maplibre, and we can therefore add these additional capabilities.

We had an interesting support request come through yesterday from a customer. They were using Power BI's rule based conditional formatting to specify line colours based on values associated with those lines:

However, the report viewers - the end users - had requested to be able to not only specify their own colours, but also the value ranges at which they take effect.

I was keen therefore to see if we could build that kind of capability using standard Power BI visuals. To achieve it I created a set of tables containing colour values for each of the ranges, and a set of corresponding numeric range parameter tables. The result is that the users can press a Power BI button to show the controls. 3 slider slicers allow them to enter the numeric ranges from which the colours take effect, and I used the new card slicer visual to allow them to pick from a set of colours.

A DAX measure is then used to return the appropriate colour based on the slicer selections and the value associated with the line

Track Colour = 
VAR _TrackValue = AVERAGE(Track[Track Value]) * 100
VAR _Colour = IF (
    ISBLANK(_TrackValue), "#000000",
    IF (_TrackValue < 'Range 1'[Range 1 Value], MAX ('Colours 1'[Selected Colour]),
        IF (_TrackValue < Range2[Range2 Value], MAX ('Colours 2'[Selected Colour]), 
            IF (_TrackValue < Range3[Range3 Value], MAX ('Colours 3'[Selected Colour]), 
                MAX ('Colours 4'[Selected Colour])
    ))))
RETURN _Colour

Here's the final report. Feel free to download the pbix file and deconstruct it.

Yesterday I published a blog about drawing drive time isochrones on the map using the Azure Maps API called from PowerQuery, all inside of Power BI. Today I've built a similar report, but this time I'm calling the Azure Maps routing API to draw the routes by road between Heathrow Airport and a number of the UK's other major airports.

Whilst Icon Map Pro has built in options for drawing lines (straight, curved or geodesic) as well as linestrings from GeoJSON or well-known text (WKT), it can't currently draw lines following the best path along roads. This isn't something that can be handled in the presentation layer, especially if traffic, low bridges, width restrictions, etc are to be taken into account.

On this basis, the Azure Maps API seems like a great way to handle this. I've started with a simple table of source and destination locations, from which I call the API.

To do this I created a PowerQuery function:

let
    GetRouteDirections = (apiKey as text, sourceLon as number, sourceLat as number, destLon as number, destLat as number) =>
    let
        // Define the API endpoint
        url = "https://atlas.microsoft.com/route/directions?api-version=2024-07-01-preview&subscription-key=" & apiKey,

        // Construct the request body
        requestBody = Json.FromValue([
            type = "FeatureCollection",
            features = {
                [
                    type = "Feature",
                    geometry = [
                        coordinates = {sourceLon, sourceLat},
                        type = "Point"
                    ],
                    properties = [
                        pointIndex = 0,
                        pointType = "waypoint"
                    ]
                ],
                [
                    type = "Feature",
                    geometry = [
                        coordinates = {destLon, destLat},
                        type = "Point"
                    ],
                    properties = [
                        pointIndex = 1,
                        pointType = "waypoint"
                    ]
                ]
            },
            optimizeRoute = "fastestWithTraffic",
            routeOutputOptions = {"routePath"},
            maxRouteCount = 1,
            travelMode = "driving"
        ]),

        // Convert the JSON request body to binary format
        requestBodyBinary = requestBody,

        // Make the API call
        response = Web.Contents(url, [
            Headers = [
                #"Content-Type" = "application/json"               
            ],
            Content = requestBodyBinary
        ]),

        // Parse the JSON response
        json = Text.FromBinary(response)
    in
        json
in
    GetRouteDirections

This returns a lot of information, so I created a function to extract out the lines making up the route, as well as the length and durations.

let
    ExtractMultiLineString = (responseText as text) =>
    let
        // Parse the response text into a JSON object
        jsonResponse = Json.Document(responseText),

        // Access the 'features' array in the JSON response
        features = jsonResponse[features],

        // Filter the features to find the one with geometry.type = "MultiLineString"
        multiLineStringFeature = List.First(List.Select(features, each _[geometry][type] = "MultiLineString"), null),

        // Extract additional properties
        distanceInMeters = if multiLineStringFeature = null then null else multiLineStringFeature[properties][distanceInMeters],
        durationInSeconds = if multiLineStringFeature = null then null else multiLineStringFeature[properties][durationInSeconds],
        durationTrafficInSeconds = if multiLineStringFeature = null then null else multiLineStringFeature[properties][durationTrafficInSeconds],

        // Convert duration to minutes
        durationInMinutes = if durationInSeconds = null then null else Number.Round(durationInSeconds / 60, 2),
        durationTrafficInMinutes = if durationTrafficInSeconds = null then null else Number.Round(durationTrafficInSeconds / 60, 2),

        distanceInKm = if distanceInMeters = null then null else Number.Round(distanceInMeters / 1000, 2),

        // Convert the MultiLineString feature to text
        multiLineStringText = if multiLineStringFeature = null then null else Text.FromBinary(Json.FromValue(multiLineStringFeature)),

        // Construct the output object
        result = [
            multiLineStringFeatureAsText = multiLineStringText,
            distanceInKm = distanceInKm,
            durationInMinutes = durationInMinutes,
            durationTrafficInMinutes = durationTrafficInMinutes
        ]
    in
        result
in
    ExtractMultiLineString

This is enough to display the routes in Icon Map Pro, but some of the routes I'd requested are long, far exceeding the maximum length of a field in Power BI. Whilst the report handles this by splitting these long routes into multiple rows, I decided to convert the GeoJSON returned by Azure Maps to Well-Known Text format, which is slightly more compact.

Again to do this I created a function, which also reduces the decimal precision of the coordinates, to further reduce the number of characters used:

let
    GeoJSONToWKT = (geoJsonText as text, precision as number) =>
    let
        // Parse the GeoJSON text to a record
        geoJson = Json.Document(geoJsonText),
        
        // Extract the geometry and its type
        geometry = geoJson[geometry],
        geometryType = Text.Upper(geometry[type]),
        coordinates = geometry[coordinates],
        
        // Function to format a single coordinate with the specified precision
        FormatCoordinate = (coord as number) => 
            Number.ToText(Number.Round(coord, precision), "F" & Number.ToText(precision)),
        
        // Function to format a single point (lon, lat)
        FormatPoint = (point as list) => 
            Text.Combine(List.Transform(point, each FormatCoordinate(_)), " "),
        
        // Function to format a list of points (e.g., for LineString)
        FormatLineString = (line as list) => 
            "(" & Text.Combine(List.Transform(line, each FormatPoint(_)), ", ") & ")",

        // Function to format a list of LineStrings (e.g., for MultiLineString or Polygon)
        FormatMultiLineString = (lines as list) =>
            "(" & Text.Combine(List.Transform(lines, each FormatLineString(_)), ", ") & ")",

        // Function to format a list of Polygons (e.g., for MultiPolygon)
        FormatMultiPolygon = (polygons as list) =>
            "(" & Text.Combine(List.Transform(polygons, each FormatMultiLineString(_)), ", ") & ")",

        // Match geometry type and convert to WKT
        WKT =
            if geometryType = "POINT" then
                "POINT (" & FormatPoint(coordinates) & ")"
            else if geometryType = "MULTIPOINT" then
                "MULTIPOINT " & FormatLineString(coordinates)
            else if geometryType = "LINESTRING" then
                "LINESTRING " & FormatLineString(coordinates)
            else if geometryType = "MULTILINESTRING" then
                "MULTILINESTRING " & FormatMultiLineString(coordinates)
            else if geometryType = "POLYGON" then
                "POLYGON " & FormatMultiLineString(coordinates)
            else if geometryType = "MULTIPOLYGON" then
                "MULTIPOLYGON " & FormatMultiPolygon(coordinates)
            else
                error "Unsupported geometry type: " & geometryType
    in
        WKT
in
    GeoJSONToWKT

And then finally, I created one further function to generate a random colour to assign to each route:

let
    GenerateDarkHexColorFromSeed = (seed as text) =>
    let
        // Convert the seed text into a list of character codes
        charCodes = List.Transform(Text.ToList(seed), each Character.ToNumber(_)),

        // Sum the character codes to create a simple numeric seed
        numericSeed = List.Sum(charCodes),

        // Generate pseudo-random RGB values using the numeric seed
        red = Number.Mod(numericSeed * 37, 256),
        green = Number.Mod(numericSeed * 59, 256),
        blue = Number.Mod(numericSeed * 73, 256),

        // Adjust RGB values to ensure they are dark enough
        minBrightness = 100, // Minimum brightness for each channel
        darkRed = if red > 255 - minBrightness then 255 - minBrightness else red,
        darkGreen = if green > 255 - minBrightness then 255 - minBrightness else green,
        darkBlue = if blue > 255 - minBrightness then 255 - minBrightness else blue,

        // Convert each component to a two-digit hexadecimal string
        redHex = Text.PadStart(Number.ToText(Number.RoundDown(darkRed), "X"), 2, "0"),
        greenHex = Text.PadStart(Number.ToText(Number.RoundDown(darkGreen), "X"), 2, "0"),
        blueHex = Text.PadStart(Number.ToText(Number.RoundDown(darkBlue), "X"), 2, "0"),

        // Combine the components into a hex color string
        hexColor = "#" & redHex & greenHex & blueHex
    in
        hexColor
in
    GenerateDarkHexColorFromSeed

I use this to create an SVG image that I show at the end of the lines, and also in the table. The image is created as a DAX measure, so I can assign the colour dynamically:

Destination Image = "data:image/svg+xml,<svg xmlns=""http://www.w3.org/2000/svg"" width=""20"" height=""20"" viewBox=""0 0 20 20""><circle cx=""10"" cy=""10"" r=""10"" fill=""" & SUBSTITUTE( MAX (Routes[Color]), "#", "%23") & """/></svg>"

Here's the final report. Feel free to download the pbix file and deconstruct it. There's hopefully some useful PowerQuery functions inside that can be put to use in other reports. You will need to use your own Azure Maps API Key - set in a PowerQuery parameter.