Data Preparation

To display polygons, data loaded into Power BI should include a GeoJSON description of each feature. This must be added as a property. The easiest way to add this information is by using SIS Desktop.

In the following example, we take a file-based overlay, add the required GeoJSON property to each feature and export to SQL Server for ease of use.

  1. Load the polygon data into SIS Desktop and open the Overlay Properties > Schema tab.

  2. Select Edit an Expression and click on Built-in-functions > Spatial > GeoJSON; this adds the GeoJSON() expression to the Expression Builder.

  3. Click OK to add the column. You can change the name at this point from the default Expression to GeoJSON for ease of use.

  4. Click OK to save the Properties.

Things to remember

Adding the GeoJSON property can create verbose descriptions of intricate geometries. Cadcorp recommends that data is exported and stored in a SQL Server database for access by Power BI because it will preserve the full description in the database.

You can also export to Tab delimited txt files.

Be aware there is a Power BI restriction on the number of characters that can be used in the GeoJSON for each polygon. This is currently set to 32,000 characters. Although this may sound a lot, it is easy to exceed this limit when using 9 decimal places accuracy for each coordinate,

There are a number of ways to mitigate this:

1. Generalise the data as a one-off operation. Consider the scale of the Power BI report and the level of detail required. Use the Generalise Process to create geometries with fewer points.

2. Using SIS Desktop 9.0 or later, the GeoJSON can be created with fewer than 9 decimal places using this expression:

  • ST_AsGeoJSON(ST_GeneraliseDP(geometry,10),2)

  • ST_GeneraliseDP is the generalise command, run on the geometry of the feature. The figure is the tolerance for generalisation. The higher the figure, the greater the application of the generalisation algorithm.

  • ST_AsGeoJSON creates the GeoJSON as a string with an optional parameter (in this case 2) which defines the number of decimal places for each geometry point.