OGC MUDDI Standards Tutorial Part 2: MUDDI 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 good underground data can result in costly damage to critical infrastructure, public safety impacts, and harm to the environment. The OGC MUDDI standard - Model for Underground Data Definition and Integration - was developed to address these and related challenges  - the mapping of 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 meant to demonstrate an application that can be used 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, leveraging the data loaded into the MUDDI database in an OGC Geopackage to generate an excavation report on the potential utilities located 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 involved are reading the database, clipping it by a buffer around the site of interest, preparing the data for the report, and then generating the HTML report. Note that there is a starting workspace you can begin with: MUDDIexcavationReporter_start.fmw. This includes the logic for site geometry generation and some of the HTML post-processing, which allows the user to focus more on the core MUDDI extraction and reporting workflow.

Step-by-Step Instructions

Part 1: MUDDI Geopackage

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

1. Start FME Workbench

Start FME Workbench, and open the MUDDI-Report_start.fme 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_Simplified_LM_v0.4.gpkg
• Parameters:
  • Table:
    • muddi_simplified_lm_data.access muddi_simplified_lm_data.container
    • muddi_simplified_lm_data.sewernetworkappurtenance
    • muddi_simplified_lm_data.sewernetworktier muddi_simplified_lm_data.sewerpipe
    • muddi_simplified_lm_data.tunnel muddi_simplified_lm_data.waternetworkappurtenance
    • muddi_simplified_lm_data.waternetworktier muddi_simplified_lm_data.waterpipe
    • muddi_simplified_lm_data.waterpumpingstation
      • 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 of the reader feature types, the fme_feature type needs to be enabled for use later in the workspace. Open the first reader feature type and switch to the Format Attributes tab. Toggle on fme_feature_type, then click OK. Repeat this process for the other nine reader feature types. 

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.

Workspace should now look like this

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:

Add a StringReplacer to the AttributeCreator output. Set the following parameters:

• Attributes: _layer
• Mode: Replace Text
• Case Sensitive: No
• Text To Replace: muddi_simplified_lm_data.
• Replace Text: <leave this empty>

9. Setup 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:

  Input Value	Output Value
  ExcavationSite	Excavation site
  waterpipe	Water pipe
  sewernetworkappurtenance	Sewer network
  access	Man hole
  sewerpipe	Sewer pipe

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 output of the AttributeKeeper in the Excavation Site Bookmark to the Supplier port. Set the following parameters:

• Requestor: 1
• Supplier: 1

11. Generate HTML Report

Add a 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

    • Column Contents	Column Name
      assetownerid	Asset Owner
      systemid	System ID
      recordid	Record ID
      utilitytype	Utility Type
      _label	Utility Subtype
      endingdepth_depth	Depth
      systemloaddate	Installation Date
      description	Description

  • 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 make an app out of it. An example of which can be found here: https://ogc-dggs-testing.fmecloud.com/fmeserver/apps/MUDDIexcavationReport