18-Spatial Data – Visualizing the Geometry Data Type

Paginated Reports eBook Introduction, Resource and Sample Data Downloads

Analyzing data by time has become commonplace; it allows a better understanding of the past which might help predict the future. Timestamps can easily be added to datasets, either generated by an algorithm or entered by a living person. There is another aspect of our data we do not use as often as perhaps we should, and this is the spatial context of the data. Most of the data we collect has a spatial context which provides information about the location where it originates – such as the address of the customer, a region depicting organizational structure, or even latitude or longitude. The spatial context reveals insight about where events have occurred.

Visualizing spatial context is not as easy as visualizing time. For the latter, we can use a simple visualizing the when of our data. More complex charting types have to be used to visualize spatial context. We are using maps to visualize the spatial context of our data. Visualizing the spatial context in our data helps us to gain tremendous insights.

Unfortunately, the use of spatial data is not as easy. Sometimes one of the following tasks (sometimes all of them or even more) must be completed before we can enjoy spatial analysis and visualization. We have to:

  • Retrieve latitude and longitude from an address to visualize a point on a map
  • Replace or upgrade devices, make them capable of tracking their whereabouts
  • Digitize a paper floor plan.

Tracking the movement of devices in a shopping mall has its complexities as indoor tracking needs special consideration and planning. As spatial context helps us better understanding our data and retrieving insights from our data, preparing spatial data for use in analytical applications can become a project on its own.

From the early beginnings (at least for a very long time), SQL Server Reporting has been able to visualize both spatial data types that are supported by SQL Server, the geography, and the geometry data type. The geometry data type, for example, can be used to store spatial information of a floor plan. Besides being able to make sense of spatial data types, Reporting Services is also able to stack different spatial objects on top of each other.

This recipe describes the visualization of a floor plan; coloring shows the overall revenue of the stores. Data points are visualizing the movement (being honest, a position at a given point in time) of visitors (being precise, the signal of cell phones carried by the visitors). Visualizing the data points on top of the floorplan creates an information-rich data visualization. Both spatial objects, the floor plan, and the data points are stored using the geometry data type.

Note: In addition to the paginated report design demonstrated in this recipe, a similar technique is possible in Power BI reports using the Icon map custom visual from James Dales. At the time of this writing (September 2020), this is the only Power BI custom visual capable of using SQL Server geometry data. The latest version of the Icon map can be obtained from http://icon-map.com. In the future after development is completed, this custom visual may be available through AppSource.

Recipe Ingredients

Here you will find hints about the products and versions and the skills that are needed to build the report.

The Reporting Services report created with this report has been build using the Power BI Report Builder

Product Versions

  • Power BI Report Server (any version)
  • Microsoft SQL Server 2016 Reporting Services or newer, or
  • Paginated Reports
    Using Paginated Reports (meaning publishing the report to the Power BI Service) makes using Azure SQL DB necessary. At the time of this writing (September 2020), the on-premises data gateway does not support complex data types like the geometry and geography data type.

Skills you need

  • Some experience is needed to create a paginated report (experience creating SSRS reports is sufficient), e.g., creating a data source should not be a miracle.

Difficulty Level: 200

Contributed by Tom Martens

Introduction

Figure 1 shows the result of this recipe. This recipe demonstrates how the SQL Server geometry data type can be leveraged within a Paginated Report (or Reporting Services report).

We will create two spatial layers; one that represents a floor (e.g., inside a shopping mall or an office building), and a second layer that visualizes the position of a device. We are stacking two spatial layers to create an information-dense report. Color is used to track different devices.

Note: Use color wisely because as human beings are not good at differentiating many different colors and don’t all perceive color in the same way. For example, consider the perception of color-impaired users or how a report would look if printed in black and white.

A picture containing screenshot Description automatically generated

Figure 1. Visualizing positions on top of polygon Layer

How to build the report

To keep things simple, you will start with a new, empty report file. You will create a data source and two datasets. The examples in this recipe are from Power BI Report Builder. If you are using SQL Server Reporting Services Report Builder, there might be tiny differences

Create a data source that points to the PaginatedReportDate sample database. Figure 2 shows the data source dialog.

Figure 2. The data source

Create a new dataset named floors, using the PaginatedReportData data source.

Copy and paste (if you are reading the eBook) or enter the following SQL script for the dataset:

SELECT
floors.floorid
,floors.ID
,floors.geomshape
,floors.geomType
FROM
floors;

Figure 3 shows the floors dataset. These records will enable us to visualize the layout of the floor of the building.
A screenshot of a cell phone Description automatically generated
Figure 3. The floors dataset

Create the Positions Dataset

This dataset represents the movement of devices. Of course, it uses the same data source, but now the table positions is used.

The SQL statement for copy/pasting (if you are using an ebook on you PC):

SELECT
positions.number
,positions.floorid
,positions.deviceid
,positions.[position]
,positions.duration
FROM
positions;

Creating the Map visual

  1. Insert a map
    To insert a map to the new report, choose “Insert Map.”
  2. Adding layers to the map
    By default, a spatial layer is drawn using a Bing Map. Clicking inside the map opens the Map Layer dialog. The Map Layers is shown in Figure 4.

Figure 4. The Map Layers dialog

  1. Removing the Bing Maps Layer
    Remove the “Bing” layer using the context
  2. Add a new layer – the floor layer
    To add a new layer, use the New Layer Wizard.
    Choose the option “SQL Server spatial query” and then click Next. Figure 13-5 shows the Map Layer wizard with the spatial query option selected.

Figure 5. Map Layer Wizard – SQL Server spatial query

  1. Select the floors dataset
    Just select the floors dataset and hit Next
  2. Choose spatial data and map view options
    Assign the field “geomshape” to the property “Spatial field” and choose “Polygon” as “Layer type.” Figure 6 shows the selection. Hit Next.

Figure 6. Spatial data and view option

  1. Select “Basic Map” – hit Next
  2. Choose “Single color map” – hit Finish

Depending on the dimensions of the map and some other arrangements, your first layer may look like the one in Figure 7 (the screenshot shows the important part of the report).

Figure 7. The rendered floor

The positions dataset

Adding the data points that are representing the devices to the map visual is similar to creating the floors layer. Due to this reason, the steps creating the points layer is short. Only if there is a difference a more detailed explanation is used.

  1. Add a new layer – use the New layer wizard
  2. Select “SQL Server spatial query” – hit Next
  3. Select the “positions” dataset. Hit Next
  4. Choose spatial data and map view options
    Assign the field “position” to the “Spatial field” property and choose “Point” as “Layer type.” Hit Next.
  5. Choose map visualization
    Select “Bubble Map.” Hit Next
    Figure 8 shows the selection of map visualization “Bubble Map.”

Figure 8. Choose Bubble Map

  1. Choose the analytical dataset
    Select the positions dataset. Hit Next
  2. Choose color theme and data visualization
    Make sure you select [Sum(duration)] to visualize the size. Figure 9 shows this selection (the figure shows just an extract of the dialog). Hit Finish

Figure 9. Use bubble size to visualize data

  1. Color Rule context menu
    To color the point by deviceID open the context menu of the PointLayer, this is shown by Figure 10.

Figure 10. Color Rule context menu

  1. Color Rule definition
    Select “Visualize data by using color palette.” Make sure that you select [deviceid] for the data field. Figure 11 shows this dialog.
    Be aware that the number of colors that we can differentiate is limited.

Figure 11. Color Rule Definition

Done!

Now we can upload the report to the Power BI Report Server.

Final Thoughts

Visualizing spatial context allows us to derive valuable insights from our data. Nevertheless, we have to consider some complexities. The effort to create a layer that represents a floor plan must not be underrated, especially if this is not our daily task. Creating polygons from an ESRI shapefile is not as easy as drawing polygons that resemble the product we love. Quite often, software used in engineering departments drawing floorplans can export these drawings to a shapefile. Exporting a shapefile is just the first step, as we now have to extract the polygons from this shapefile. Extracting the information we need from a shapefile quite often requires the use of another tool like QGIS.

Next to the technical challenges that come with the tracking of the position of devices, we also have to consider ethical issues. Tracking mobile devices inside a building comes with its difficulties, but fortunately, technology is available offering exactly this.

From my personal experience, there are many use cases where the visualization of spatial context enables business users to make more informed decisions. For this, I hope this recipe provides you with some new ideas.

Related References

%d bloggers like this: