Ordnance Survey basemaps in Power BI

Data storytelling with our data

Power BI is a powerful tool for business analysis and data visualisation, but it can be tricky to know how to take your visuals to the next level. Usingvarious methods, we can enhance our dashboards with Ordnance Survey (OS) Data, utilising Addressing Data and even interactive basemapping.

OS offers a rich selection of data through the OS Data Hub. We will be looking at the OS APIs, specifically the OS Maps API, the OS NGD API — Tiles, OS Vector Tile API and the OS Places API. We can integrate the APIs with custom visuals, Python, R and through Power Query.

Before we get started you must open up an account on OS Data Hub and get your API key, which you can do here. You can sign up for an OpenData plan for free, or choose the Premium plan for limited access to premium data.

Using Custom Visuals for Basemaps in Power BI

The OS Maps API is a WMTS that can be fed into Power BI providing a highly detailed basemap in a variety of styles, utilising OS MasterMap and the iconic OS Leisure Maps. How do we do this? The easiest way is by using visuals. There are custom visuals that can be added from the ‘Get More Visuals’ within Power BI. One example is Icon Map, a free custom visual based on Leaflet that supports WMS overlays.

First, we need to get our ZXY API Endpoint Address from the projects page on the OS Data Hub. Be sure to choose your desired style and set the coordinate system to Web Mercator (EPSG:3857). It will look like this, with your API key at the end:

https://api.os.uk/maps/raster/v1/zxy/Light_3857/{z}/{x}/{y}.png?key=(insert api key}

Then, you will need to add some template data to your dashboard. Once this is done paste your endpoint URL into the WMS URL box in the visual tab of your Icon Map visual, being sure the CRS matches the API CRS of EPSG:3857. Your basemap should now appear, giving you a highly detailed backdrop to your maps within Power BI!

Can we go a step further? Using the MapBox Visual we can also pull in the OS Vector Tile API. Once again, grab your API key from the projects page on the Data Hub. The URL will look a little bit different this time:

https://api.os.uk/maps/vector/v1/vts/resources/styles?key={insert api key}&srs=27700

Enter your mapbox access token into the MapBox visual. Then, set your Map Style to reflect the above URL. Now you have a fully cusotmisable, interactive basemap for your dashboard in which you can pan, zoom and move around the map.

Using R to Create Visuals with OS APIS

What if we wanted to create something a bit more customisable? Using R and node.js we can utilise leaflet and other packages to create highly interactive visuals. For example, The Welsh Ambulance Trust and NHS Wales Modelling Collaborative created a dashboard that allows you to cycle through different styles of the OS Maps API on the fly, with custom symbolology for your data. Documentation on how to achieve this can be found here.

Following the steps in the documentation, first we download node.js, then powerbi visual tools through the command line prompt:

npm install -g powerbi visuals tools

Check your installation by typing pbiviz.json within command line, which should return information on your install. Now, to create a template project folder navigate to your project directory and give the command:

pbiviz new testproject -t rhtml.

Within your new project folder update the author, description, support URL and Github URL in the pbviz.json file. To build your visual, update the script R file for your visual. The ecample in the documentation uses OS Maps API and can be found here.

Once you have updated your pbiviz json and R script file, you can build your visual with the command pbiviz. This must be done in the project directory. Once this has completed a custom visual will be created in the dist folder of your project. This can be imported in Power BI as a custom interactive visual.

This gives us a fully interactive visual that can toggle between different basemap styles on the fly.

Addressing Data in Power BI with OS Places

The OS Places API contains rich addressing data from AddressBase Premium. Using Python, R or Power Query we can pull this data into our dashboards. The API returns 100 results per request, so we will need to page through requests in order to return more data.

To use Python, choose get data, search python and select ‘Python Script’. Paste the following script into the window, substituting your API key and the chosen parameters for the OS Places API call.

# Import packages
import pandas as pd
import requests as r
# Define API parameters
key = 'enter API key here'
query = 'enter OS Places query here (e.g EX4)'
url = f'https://api.os.uk/search/places/v1/find?query={query}&key={key}'
dataframe = pd.DataFrame()
# Make API requests with offsets and concatenate results
offsets = range(0, 1000, 100)
for offset in offsets:
url = f'https://api.os.uk/search/places/v1/find?query={query}&offset={offset}&key={key}'
response = r.get(url)
response.raise_for_status() # Check for errors
results = response.json()['results']
page = pd.json_normalize(results)
dataframe = pd.concat([dataframe, page])
# Delete page so only main dataframe is detected by PowerBI

Load this into the data frame to return the first 1000 results of the API call. The number of results can be adjusted using the offsets variable. Using R this can be done in exactly the same way by selecting R Script in the Get Data screen and pasting in the following script:

# Import packages
library("httr")
library("jsonlite")
library('gtools')
# Define API parameters
key <- 'Insert Key Here'
query <- 'Enter OS Places query here (e.g EX4)'
# Define empty dataframe and offset. Change second value to increase number of results.
offset= seq(0, 900, by=100)
dataframe = data.frame()
# Make API requests with offsets and concatenate results
for (value in offset) {
url <- paste0('https://api.os.uk/search/places/v1/find?query=',query,'&offset=',value)
data <- GET(url,add_headers(key=key))
json <- fromJSON(rawToChar(data$content), flatten=TRUE)
dataframe <- smartbind(dataframe,json$results)

Finally, the built in functions of power query can be used to bring in OS Places API. The advantages of this method are that it doesn’t require the use of python or R, however it does have a few more steps than using R and Python, and so is covered in the power query documentation.

In total this guide has given a high level overview of how to get OS Maps API, OS Vector Tile API and OS Places API into your dashboards in a variety of ways.

For more information related to this, reach out to Ollie Bowden or email ollie.bowden@os.uk.