2 posts tagged with "drive times"

Drawing routes along roads using the Azure Maps API

December 6, 2024 · 6 min read

Co-founder of Tekantis

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.

alt text

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.

alt text

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.

Fire Station Drive Times with Azure Maps API

December 5, 2024 · 4 min read

James Dales

Co-founder of Tekantis

Yesterday Microsoft published a blog post about Isochrones in the Azure Maps Route Range API. Isochrones are a great way to represent the amount of time it takes to travel from a point. We've included an isochrone example in our sample Power BI report file since we launched Icon Map Pro six months or so ago.

With Microsoft now supporting isochrones in geoJSON format as part of their API, I was keen to see if I could incorporate this into Power BI's data load processes using Power Query, without needing to use a Python notebook.

To test this out I've built a report that shows the extent that all the fire stations in West Sussex can reach within a 10 minute drive time.

alt text

I started with a list of fire stations, their addresses and the corresponding longitudes and latitudes:

alt text

The plan was to use the coordinates as the start points for the drive time call for the Azure Maps API.

I created a Power Query function to call the API, which I then call for each fire station:

let
    
    CallAzureMapsRangeApi = (ApiKey as text, Longitude as number, Latitude as number, TimeBudget as number, TravelMode as text, Height as number, Length as number, Width as number) as text =>
    let
        // Define the API endpoint
        ApiUrl = "https://atlas.microsoft.com/route/range?api-version=2024-07-01-preview&subscription-key=" & ApiKey,
        
        // Create the request body
        RequestBody = Text.FromBinary(Json.FromValue([
            type = "Feature",
            geometry = [
                type = "Point",
                coordinates = {Longitude, Latitude}
            ],
            properties = [
                timeBudgetInSec = TimeBudget,
                travelMode = TravelMode,
                vehicleSpec = [
                    height = Height,
                    length = Length,
                    width = Width
                ]
            ]
        ])),
        
        // Define the headers
        Headers = [
            #"Content-Type" = "application/json"
        ],
        
        // Make the POST request
        Response = Web.Contents(ApiUrl, [
            Headers = Headers,
            Content = Text.ToBinary(RequestBody)
        ]),
        
        // Treat the response as plain text
        ResponseText = Text.FromBinary(Response)
    in
        ResponseText
in
    CallAzureMapsRangeApi

The function takes in the your Azure Maps API key, the coordinates, amount of time, vehicle type and dimensions of the vehicle as parameters.

Calling this returns a geoJSON file for each fire station:

alt text

Whilst Icon Map Pro can display geoJSON files, it can't display them when the whole file is stored in a field. It can however display geoJSON features stored in a row in your dataset, so I created another PowerQuery function to extract the isochrone polygon, and its centroid from the geoJSON:

let
    ExtractFeature = (GeoJsonText as text) as table =>
    let
        // Parse the input JSON text
        ParsedJson = Json.Document(GeoJsonText),

        // Extract the "features" array
        Features = ParsedJson[features],

        // Extract the "geometry" object for the "Polygon" type
        PolygonGeometry = List.First(
            List.Select(Features, each _[geometry][type] = "Polygon")
        )[geometry],

        // Extract coordinates for the "Point" type
        PointFeature = List.First(
            List.Select(Features, each _[geometry][type] = "Point")
        ),

        PointCoordinates = PointFeature[geometry][coordinates],
        Longitude = PointCoordinates{0},
        Latitude = PointCoordinates{1},

         // Convert the Polygon object to JSON text
        PolygonText = Text.FromBinary(Json.FromValue([
            type = PolygonGeometry[type],
            coordinates = PolygonGeometry[coordinates]
        ])),

        // Prepare the output table
        Result = Table.FromRecords({
            [
                Longitude = Longitude,
                Latitude = Latitude,
                Polygon = PolygonText
            ]
        })
    in
        Result
in
    ExtractFeature

From this we now just have the Polygon feature for each isochrone, and not the whole file.

alt text

This is enough to display the isochrones on the map, but I also wanted to add a circle showing the centroid, and add a label with the fire station name. To do this I added another set of rows for each fire station with the longitude and latitude, and appended it to the fire stations table. Now I have a set of rows for each isochrone, and a set of rows for each centroid.

alt text

Then in Icon Map Pro, I've enabled the circle layer, and WKT / GeoJSON (from data) layer and dragged in the corresponding fields. I also set up the label to show below the circles.

alt text

Finally I added in a reference layer with the UK's local authorities - but with West Sussex deleted. This meant that I could highlight the West Sussex area on the map.

The last touch was to add tooltips, and a Power BI slicer to be able to zoom in to specific fire stations.

alt text

Here's the final report using Icon Map Pro, and you can download the Power BI file to see how it was constructed. You will need to add your own Azure Maps API key (obtained from the Azure admin portal) as a Power Query parameter.