OGC MUDDI Standards Tutorial Part 2: MUDDI Burnaby Excavation Reporter

Introduction

Underground mapping has been a challenge simply because underground features and assets are much more difficult to survey or collect data for. On the other hand, the lack of reliable underground data can result in costly damage to critical infrastructure, public safety impacts, and environmental harm. The OGC MUDDI standard - Model for Underground Data Definition and Integration - was developed to address these and related challenges: mapping underground features and infrastructure better protects infrastructure, people, and the environment, and avoids costly damage during construction and maintenance activities.

The application developed in this tutorial is intended to demonstrate how to examine a specific site for underground utilities prior to a planned excavation. This way, steps can be taken to avoid damage to the underlying utility infrastructure. This specific use case integrates data from the city of Burnaby, Canada, for utilities related to sewer, including water supply, wastewater, and storm drains.

The tutorial is divided into two parts: the MUDDIloader and the MUDDIexcavationReporter. This tutorial covers the second exercise, which leverages data loaded into the MUDDI database in an OGC Geopackage to generate an excavation report on the potential utilities at the excavation site.

Below, we will outline the steps to read utility data from the MUDDI-modelled Geopackage for Burnaby. This allows for a report that queries across various Utility sectors, such as Water, Sewer, and wastewater, and produces a report about underground infrastructure in the area. The basic steps are reading the database, clipping it with a buffer around the site of interest, preparing the data for the report, and generating the HTML report. Note that there is a starting workspace you can begin with: MUDDIexcavationReporter_start.fmw. This includes the logic for generating site geometry and some HTML post-processing, allowing the user to focus more on the core MUDDI extraction and reporting workflow.

Step-by-Step Instructions

Part 1: MUDDI Geopackage

In this section, we will cover how to generate a report using a simplified MUDDI standard model in a Geopackage. 

To follow along with the tutorial, please download the zip file from here 

1. Start FME Workbench

Start FME Workbench and open the MUDDI-Burnaby-Report_start.fmw Workspace.

This should open a starting point for the workspace that looks like this

2. Add Geopackage Data

First, we need to add a source dataset. Click on the Reader button. In the Add Reader dialog, enter the following:

• Format: OGC Geopackage
• Dataset: Data\Burnaby_MUDDI_SLM.gpkg 
• Parameters:
  • Table:
    • access
    • sewernetworkappurtenance
    • sewerpipe
    • waterpipe
      • Click the ellipsis and select the tables

Then click OK twice. 

If you inspect the reader feature types, the output should look similar to this: 

3. Enable fme_feature_type Attribute

For each reader feature type, the fme_feature_type needs to be enabled for later use in the workspace. Open the first reader feature type and switch to the Format Attributes tab. Toggle on fme_feature_type, then click Apply. Select all for ‘Feature Types to Change’, and select fme_feature_type in the ‘Format Attributes to Expose’ parameter. 

Click OK twice. 

4. Clip Objects to Area of Interest

Add a Clipper to the workspace, and connect all the reader feature types from the previous step to the Candidate port. Connect the output of the Bufferer from the Excavation Bookmark to the Clipper port of the Clipper. Use Default Parameters.

5. Confirm Data

Add a NoFeaturesTester. Connect this to the Clipper Inside output port.

6. NoData Variable Assignment

Add two VariableSetters. Connect the first one to the NoFeaturesTester NoInput port. Use the following parameters:

• Variable Name: NoData
• Value: True
• Variable Scope: Global

Connect the second one to the NoFeaturesTester Output port. Use the following parameters:

• Variable Name: NoData
• Value: False
• Variable Scope: Global

7.  NoData Case

Connect the first VariableSetter, which was connected to the NoInput port, and connect it to the Aggregator located in the Handle No Data Case Bookmark.

The Handle No Data Case bookmark will basically ensure a Report is generated even if no data is found within the Area of Interest.

8. Layer setup

Add an AttributeCreator to the Output port of the NoFeaturesTester. Create the following parameter:

9. Set Up Report Values

Add an AttributeValueMapper to the StringReplacer output. Set the following Parameters:

• Input Attribute: _layer
• Output Attribute: subType
• Default Output value: _layer
• Mapping Direction: Forward (Input to Output)
• Value Map:

Add an AttributeCreator to the AttributeValueMapper. Set the following parameters: 

Workspace should look like this now: 

10. Merge Excavation with MUDDI Model

Add a FeatureMerger to the canvas. Connect the AttributeCreator output from the previous step to the Requestor port. Connect the AttributeKeeper output in the Excavation Site Bookmark to the Supplier port. Set the following parameters:

• Requestor: 1
• Supplier: 1

11. Generate HTML Report

Add an HTMLReportGenerator to the Merged port of the FeatureMerger. Connect the GeometryCoercer to the HTMLReportGenerator as well. Set the following parameters:

• Page Title: Excavation Report
• Page Contents:
  • Header: 
    • Text: Excavation Report
    • Header Level: H2
    • Text Alignment: Left
    • Color: 0,0,0

  • Custom HTML:

    <p>
    <b>         &nbsp;Longitude: </b>@Value(_site_long)
    <b>         &nbsp;Latitude: </b>@Value(_site_lat)
    <b>         &nbsp;Buffer: </b>$(exBuffer) meters
    <p>

Table: 
Table Style: Default

• Map (Esri Leaflet):
  • Label Attribute: _label
  • Escape HTML: Yes
  • Layer Color: 0,0,255
  • Basemap: Street
• Custom HTML:

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
 border: 1px solid black;
 border-collapse: collapse;
}
</style>
</head>
<body>
<center>
<table style="width:200px%"><b>
<tr><th>Layer</th>
<tr><td style="color:Tomato;">Excavation site</td></tr>
<tr><td style="color:Green;">Electrical network</td></tr>
<tr><td style="color:Purple;">Electrical duct</td></tr>
<tr><td style="color:DodgerBlue;">Water pipe</td></tr>
<tr><td style="color:Yellow;">Telcom cable</td></tr>
<tr><td style="color:Brown;">Sewer network</td></tr>
</tr>
</body>

12. Retrieve the Variables

Connect the HTMLReportGenerator Output to the VariableRetriever. Connect the GeometryCoercer from the Excavation Site Bookmark to the Aggregator. 

The workspace should now look like this:

13. HTML Writer

Add a writer to the workspace:

• Format: HTML
• Dataset: ./Muddi_excavationReport_tutorial.html

Connect it to the StringReplacer_3. 

14. Report Value Assignment

Add an AttributeCreator. Connect it to the output of the AttributeCreator connected to the AttributeValueMapper, and to the AttributeCreator_2 located in the Excavation Site Bookmark. Set the following parameters:

15. Keep Specific Attributes

Add an AttributeKeeper to the AttributeCreator from the previous step. The attributes we want to keep are:

• Attributes to Keep:
  • Asset Owner
  • Depth
  • Description
  • Record ID
  • Subtype
  • System ID
  • Utility Type

16. GeoJSON Writer

Add a writer to the workspace:

• Format: GeoJSON
• Dataset: <same location as HTML writer>/excavationReport.geojson

Then click OK. 

In the Feature Type dialog, set:

• Feature Type Name: excavationReport

Connect this to the output of the AttributeKeeper.

The GeoJSON could be used to pass the data directly to downstream processes.

17. Run the Workspace

The workspace can now be run. It should produce both an HTML file and a GeoJSON file. The HTML output should look similar to this

Next Steps

You could then publish this to FME Flow and build an app from it. An example of which can be found here: https://ogc-dggs-testing.fmecloud.com/fmeserver/apps/MUDDIexcavationReport

Data Attribution 

The data used here originates from data made available by the City of Burnaby, British Columbia. It contains information licensed under the Open Government License - Burnaby.