Using Leaflet in a Power BI Custom Visual

For some of the spatial visualisations I’ve been working with in Power BI, I’ve had to create Custom Visuals as the out-of-the-box and visuals in the AppSource don’t quite hit the mark. I’m quite fond of Leaflet for map rendering. Here’s how I got it working with a Power BI Custom Visual.

Create a new Custom Visual via the Command Line:

pbiviz new LeafletCustomVisual

In the newly created directory, install Leaflet via npm:

npm install --save leaflet

You need the geojson as the typings for Leaflet (in the next step), depends on the package being available.

Install typings for Leaflet:

npm install --save-dev @types/leaflet

As Power BI Custom Visuals don’t currently support modules, you will need to grab the Leaflet object from the window. Create an inject.js file with the contents:

var L = window.L;

Add the Leaflet package and inject.js file to pbiviz.json in the externalJS key:

"externalJS": [
    "node_modules/leaflet/dist/leaflet.js",
    "inject.js",
    "node_modules/powerbi-visuals-utils-dataviewutils/lib/index.js"
  ],

Import the Leaflet stylesheet to style/visual.less :

@import (less) "node_modules/leaflet/dist/leaflet.css";

In src/visual.ts, change the constructor to:

private map: L.Map;

constructor(options: VisualConstructorOptions) {
    console.log('Visual constructor', options);
    this.target = options.element;
    // Fill the target element with the Leaflet map
    this.target.style.width = "100%";
    this.target.style.height = "100%";

    if (typeof document !== "undefined") {
        this.map = L.map(this.target).setView([0, 0], 2);
        L.tileLayer("https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png").addTo(this.map);
    }
}

You can find an example project on GitHub.

Translink GTFS

I recently became aware that Translink, Queensland’s public transport provider, openly publishes both a GTFS (General Transit Feed Specification) Static dataset and Realtime feed. The GTFS Static dataset provides information on routes, schedules, stops and pathing, while the Realtime feed provides information on current vehicle lat/long coordinates and delay information.

I created a web app to visualise the realtime feed on a map and supplement it with the static data (direction and vehicle type).

The initial version used moving markers to simulate vehicle movement. Speed was based both on static timetable information and adjusted using real-time speed (calculated from the distance traveled between updates of the feed). While the moving markers were appealing, the crude design of the web app was such that these calculations were performed by the browser instead of the server to offload processing. This resulted in a lot of data being sent to the browser, and was unsuitable for mobile devices with limited data caps. I ended up removing this functionality when optimising the reduction in data exchanged between the client and server.

View app in fullscreen
Source code on GitHub

Offline web apps

I was doing some work with a mobile web application that had to continue to operate in a disconnected scenario, as users may intermittently go in and out of areas with mobile network reception.

Adding offline support was reasonably straightforward in web browsers that support:

Example code available on GitHub..

Querying ArcGIS attribute subtypes and domains with SQL

ArcGIS geodatabases have the concept of domains, where an attribute can be restricted to a set of values, similar to a drop down list. Furthermore, geodatabases also allow an attribute to be specified as a subtype where the subtype determines which set of domains that apply to attributes. This can be used when setting up cascading drop down lists.

In an Enterprise Geodatabase, this subtype and domain metadata is stored in the GDB_ITEMS table in an XML definition. This ESRI technical article provides code on extracting domain values via SQL:

SELECT
  items.Name,
  codedValue.value('Code[1]', 'nvarchar(max)') Code,
  codedValue.value('Name[1]', 'nvarchar(max)') Value
FROM SDE.GDB_ITEMS AS items
INNER JOIN SDE.GDB_ITEMTYPES AS itemtypes ON items.Type = itemtypes.UUID
CROSS APPLY items.Definition.nodes('/GPCodedValueDomain2/CodedValues/CodedValue') AS CodedValues(codedValue)

This works well if there are no subtypes, as you can just join your table or view against the Code column and display the Value column. To go one step further an incorporate subtypes, we need to introduce another query that extracts the domains used by a subtype (change MyTable and MyAttribute to appropriate values):

SELECT
  SubtypeChildren.value('SubtypeCode[1]', 'varchar(50)') SubtypeCode,
  DomainName.value('.', 'varchar(50)') DomainName
FROM GDB_ITEMS i
CROSS APPLY i.Definition.nodes('//Subtype') AS Subtype(SubtypeChildren)
CROSS APPLY SubtypeChildren.nodes('FieldInfos/SubtypeFieldInfo/FieldName[text()="MyAttribute"]/../DomainName') AS SubtypeFieldInfo(DomainName)
WHERE Name = 'MyTable'

This returns the subtype code as well as the domain name. The subtype code can then be joined against your table or view, while the domain name can be joined against the items.Name from the first query. To be able to return the cascaded domain, we need to join again on the first query. For example, using CTEs:

WITH domains AS (
  SELECT
    items.Name,
    codedValue.value('Code[1]', 'nvarchar(max)') Code,
    codedValue.value('Name[1]', 'nvarchar(max)') Value
  FROM SDE.GDB_ITEMS AS items
  INNER JOIN SDE.GDB_ITEMTYPES AS itemtypes ON items.Type = itemtypes.UUID
  CROSS APPLY items.Definition.nodes('/GPCodedValueDomain2/CodedValues/CodedValue') AS CodedValues(codedValue)
),
subtypes AS (
  SELECT
    SubtypeChildren.value('SubtypeCode[1]', 'varchar(50)') SubtypeCode,
    DomainName.value('.', 'varchar(50)') DomainName
  FROM GDB_ITEMS i
  CROSS APPLY i.Definition.nodes('//Subtype') AS Subtype(SubtypeChildren)
  CROSS APPLY SubtypeChildren.nodes('FieldInfos/SubtypeFieldInfo/FieldName[text()="MyAttribute"]/../DomainName') AS SubtypeFieldInfo(DomainName)
  WHERE Name = 'MyTable'
)
SELECT
  m.Category CategoryCode,
  d1.Value CategoryDescription,
  m.MyAttribute TypeCode,
  d2.Value TypeDescription
FROM MyTable m
INNER JOIN domains d1 ON d.Code = m.Category
INNER JOIN subtypes s ON s.SubtypeCode = m.Category
INNER JOIN domains d2 ON d2.Code = m.MyAttribute

The subtypes query above is limited to a single attribute as this was the use case I needed to support. To generalise it and return all fields, modify it to return the SubtypeCode, FieldName and DomainName.

web.config for React and Node.js on Azure App Service

I’ve been creating a web application using React and Node.js, and decided to host it on Azure App Service. I had a lot of difficulty finding an appropriate web.config rewrite rules that would host static assets, redirect deep React Router links to index.html and send API calls to Node.js via iisnode. I finally came across this post which got me most of the way there, and I just had to adapt it to use Node.js:

WebdriverIO with ChromeDriver headless without Selenium

I was excited to discover that WebdriverIO supports the use of ChromeDriver running in headless mode without the use of Selenium (because ChromeDriver supports the WebDriver protocol). Here’s a starter guide (assuming Node.js is installed):

  1. Install Chrome Web Browser
  2. Download ChromeDriver
  3. Run ChromeDriver
  4. Install WebdriverIO by running npm i webdriverio
  5. Run the following file (based on the WebdriverIO example)

const webdriverio = require('webdriverio')
const client = webdriverio.remote({
  host: "localhost",
  port: 9515,
  path: "/",
  desiredCapabilities: {
    browserName: "chrome"
  }
})
client
  .init()
  .url('https://duckduckgo.com/')
  .setValue('#search_form_input_homepage', 'WebdriverIO')
  .click('#search_button_homepage')
  .getTitle().then(function(title) {
    console.log('Title is: ' + title);
  })
  .end()

To run Chrome in headless, use the following config:

const client = webdriverio.remote({
  host: "localhost",
  port: 9515,
  path: "/",
  desiredCapabilities: {
    browserName: "chrome",
    chromeOptions: {
      args: ["headless", "disable-gpu"]
    }
  }
})

Creating a ArcGIS map service raster layer in PyQGIS

It’s relatively straightforward creating layers programatically in QGIS using PyQGIS. However, the documentation isn’t clear on how to use create a raster layer from a ArcGIS map service.

The list of raster providers can be found by searching for addRasterProviderDialog in qgsdatasourcemanagerdialog.cpp.

There are two mandatory parts to the data source string when specifying the ArcGIS map service, the url and layer, which need to be space separated. For example:

iface.addRasterLayer("url='http://gis.infrastructure.gov.au/infrastructure/rest/services/KeyFreightRoute/KFR/MapServer' layer='5'", "Key road freight routes", "arcgismapserver")

or using the constructor:

layer = QgsRasterLayer("url='http://gis.infrastructure.gov.au/infrastructure/rest/services/KeyFreightRoute/KFR/MapServer' layer='5'", "Key road freight routes", "arcgismapserver")

To understand what data source parameters are supported by the ArcGIS map service provider (AMSProvider), search for dataSource.Param in qgsamsprovider.cpp.

Websockets are easy

I was interested in getting a minimal example working for Websockets and it was surprisingly easy to get a demo working between Node.js and a browser.

First install the ws library:

npm install ws

Create an index.js file with the contents:

const WebSocket = require('ws');
const wss = new WebSocket.Server({ port: 8080 });

wss.on('connection', function(ws) {
  console.log('Starting connection');
  ws.on('message', function(message) {
    console.log('Received message: ' + message);
    ws.send('You sent ' + message);
  });
});

Start the Node.js process:

node index.js

In a browser console, send a message to the Node.js process:

var ws = new WebSocket("ws://localhost:8080");
ws.onmessage = function(e) {
    console.log(e.data);
}
ws.send('Hello World');