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 a variety of utilities, including water supply, electricity, telecom, wastewater and storm drains.
The tutorial is divided into two parts: the MUDDILoader and the MUDDIExcavationReporter. This tutorial covers the first exercise - loading the data from various utilities into the MUDDI data model stored in an OGC geopackage.
Below, we will go over the steps to populate an OGC Geopackage that is utilizing the MUDDI data model. Using data across various utility sectors, such as water, electrical, sewer, and telecommunications - you will learn the basic process of how to move from GIS data into a MUDDI-modelled Geopackage. Note that there are two starting workspaces you can begin with: start_easy.fmw and start_advanced.fmw. The start_easy.fmw one already has the source datasets loaded, while start_advanced.fmw only includes some of the more involved AttributeCreators but otherwise lets you build most of the workspace yourself.
Downloads
- MUDDI_Loader_Part1.zip (43.1 MB)
Step-by-Step Instructions
If you want a beginner-friendly starting point, please open the workspace titled MuddiLoader_Starter_Beginner.fmw.
The Beginner workspace will skip the Adding Reader portions of each Section below.
Part 1: City of Burnaby Infrastructure GeoJSON Data
In this part, we will cover how to move City of Burnaby GeoJSON data into a MUDDI-modelled Geopackage.
1. Start FME Workbench and Add GeoJSON Data
Start FME Workbench and click on New to open a blank workspace.
First, we need to add a Source dataset. Click on the Reader button. In the Add Reader dialog, enter the following:
- Format: GeoJSON
-
Dataset:
- Data/Part 1/Sanitary_Fittings.geojson
- Data/Part 1/Sanitary_Main_.geojson
- Data/Part 1/Sanitary_Service_Line.geojson
- Click on the drop-down arrow and select Select Multiple Folders/Files. From there, click Add Files and browse to the desired datasets. Select all that apply.
Repeat the above steps for the Watermains, Storm Fittings, and Storm Service Lines.
- Format: GeoJSON
-
Dataset:
- ./Watermain.geojson
- /Storm_Fitting.geojson
- /Storm_Service_Line.geojson
2. Modify Schema to match MUDDI Geopackage
Add a Cloner to the output of the source GeoJSON feature types. Leave parameters as default.
Add a BulkAttributeRenamer to the output of the Cloner. Set the following parameters:
- Rename: All Attributes
- Action: Change Case
- Mode: Rename
-
Case Change Type: lowercase
3. Date handling
Add a StringReplacer to the output of the BulkAttributeRenamber. Set the following parameters:
- Attributes: instdate
- Mode: Replace Text
- Case Sensitive: No
- Text to Replace: -07:00
Add a Tester to the output of the StringReplacer. Set the following parameters:
- Left Value: instdate
- Operator: Attribute Has a Value
- Logic: OR
- Left Value: install_date
- Operator: Attribute Has a Value
Add a DateTimeConverter to the Passed port of the Tester. Set the following parameters:
- Datetime Attributes: instdate
- Input Format: %Y-%m-%dT%H:%M:%SZ$
- Output Format: %Y%m%d (FME date)
- Repair Overflow: Yes
- Passthrough Nulls, Empties, or Missing: No
Add a second DateTimeConvert to the Failed port of the Tester. Set the following parameters:
- Datetime Attributes: install_date
- Input Format: <Auto detect FME and ISO formats>
- Output Format: %Y%m%d (FME date)
- Repair Overflow: Yes
- Passthrough Nulls, Empties, or Missing: Yes
Add a DateTimeStamper to the Output and Rejected Ports of the first DateTimeConverter and the Output port of the second DateTimeConverter. Set the following Parameters:
- Type: Date
- Time Zone: Coordinated Universal Time (UTC)
- Include Fractional Seconds: Yes
- Update Time: Each Feature
- Output Format: ISO
- Output Attribute: systemloaddate
4. Filter Features to MUDDI Classes
Add a FeatureTypeFilter to the output port of the DateTimeStamper. Open the Parameters and Click Update. This should populate the Feature Types that you are reading in. If this does not fully populate a list you can type each of your input Feature types.
-
Feature Type Names:
- Sanitary_Main_
- Sanitary_Service_Line
- Sanitary_Fittings
- Watermain
- Storm_Fitting
- Storm_Service_Line
Part 2: waterpipe MUDDI Class
Add an AttributeValueMapper to the Watermain port of the FeatureTypeFilter. Set the following parameters:
-
Attribute Selection:
- Input Attribute: material
- Output Attribute: material
- Mapping Direction: Forward (Input To Output)
Input Value Output Value CI Cast Iron ACP Asbestos Cement DI Ductile Iron STL Steel CU Copper
Add an AttributeManager to the output of the AttributeValueMapper. Set the following parameters:
| Input Value | Output Attribute | Value | Type | Action |
|---|---|---|---|---|
| install_date | dateofinstallation | date | Rename | |
| untid | recordid | buffer | Rename | |
| diameter | diameter_width | int64 | Rename | |
| dataowner | City of Burnaby | varchar(15) | Set Value | |
| dataprovider | City of Burnaby | varchar(15) | Set Value | |
| systemid | @UUID() | varchar(200) | Set Value | |
| name | recordid | varchar(200) | Set Value | |
| diameter_unitofmeasure | Millimetres | varchar(11) | Set Value | |
| lifecyclestatus | Submitted | varchar(9) | Set Value | |
| horizontalcrs | 4326 | unit16 | Set Value | |
| businesscontact | City of Burnaby | varchar(15) | Set Value | |
| datalastupdated | systemloaddate | varchar(200) | Set Value | |
| material | pipetype | buffer | Set Value | |
| startingdepth_unitofmeasure | Metres | varchar(6) | Set Value | |
| endingdepth_unitofmeasure | Metres | varchar(6) | Set Value | |
| utilitytype | Water | varchar(5) | Set Value |
Part 3: sewerpipe MUDDI Class
Add another AttributeValueMapper. Connect the Sanitary_Main_, Sanitary_Service_Line, Storm_Service_Line to the new AttributeValueMapper_2. Set the following parameters:
-
Attribute Selection:
- Input Attribute: material
- Output Attribute: material
- Mapping Direction: Forward (Input To Output)
Input Value Output Value CI Cast Iron ACP Asbestos Cement DI Ductile Iron STL Steel CU Copper PVC PVC` HDPE HDPE
Add an AttributeManager to the output of the AttributeValueMapper_2. Set the following parameters:
| Input Value | Output Attribute | Value | Type | Action |
|---|---|---|---|---|
| pipediam | diameter_width | int64 | Rename | |
| diameter | diameter_width | int64 | Rename | |
| upselev | depth | real64 | Rename | |
| dwnelev | downstreamdepth_depth | real64 | Rename | |
| diam | diameter_width | int64 | Rename | |
| dataowner | City of Burnaby | varchar(15) | Set Value | |
| dataprovider | City of Burnaby | varchar(15) | Set Value | |
| systemid | @UUID() | varchar(200) | Set Value | |
| name | recordid | varchar(200) | Set Value | |
| diameter_unitofmeasure | Millimetres | varchar(11) | Set Value | |
| lifecyclestatus | Submitted | varchar(9) | Set Value | |
| horizontalcrs | 4326 | unit16 | Set Value | |
| businesscontact | City of Burnaby | varchar(15) | Set Value | |
| datalastupdated | systemloaddate | varchar(200) | Set Value | |
| material | pipetype | buffer | Set Value | |
| startingdepth_unitofmeasure | Metres | varchar(6) | Set Value | |
| endingdepth_unitofmeasure | Metres | varchar(6) | Set Value | |
| utilitytype | Sewer | varchar(5) | Set Value | |
| recordid | unitid | varchar(6) | Set Value | |
| systemid | @UUID() | varchar(200) | Set Value |
Part 4: sewernetworkappurenance MUDDI Class
Add a TestFilter. Connect the Sanitary_Fittings and Storm_Fitting from the FeatureTypeFilter to the TestFilter. Set the following parameters:
-
Test:
-
If:
- mhdpth ATTRIBUTE_VALUE_NULL OR mhdpth ATTRIBUTE_IS_MISSING
- Output Port: sewernetworkapputenance
-
Else If:
- mhdpth ATTRIBUTE_HAS_A_VALUE
- Output Port: access
-
If:
Add an AttributeManager to the sewernetworkappurtenance output port of the TestFilter. Set the following parameters:
| Input Value | Output Attribute | Value | Type | Action |
|---|---|---|---|---|
| name | unitid | buffer | Set Value | |
| dataowner | City of Burnaby | varchar(15) | Set Value | |
| dataprovider | City of Burnaby | varchar(15) | Set Value | |
| recordid | unitid | buffer | Set Value | |
| appurtenancetype | Other | varchar(5) | Set Value | |
| horizontalcrs | 4326 | unit16 | Set Value | |
| businesscontact | City of Burnaby | varchar(15) | Set Value | |
| utilitytype | Sewer | varchar(5) | Set Value | |
| lifecyclestatus | Submitted | varchar(9) | Set Value | |
| dataofinstallation | instdate | varchar(200) | Set Value |
Add an AttributeManager to the access output port of the TestFilter. Set the following parameters:
| Input Value | Output Attribute | Value | Type | Action |
|---|---|---|---|---|
| dataowner | City of Burnaby | varchar(15) | Set Value | |
| dataprovider | City of Burnaby | varchar(15) | Set Value | |
| recordid | unitid | buffer | Set Value | |
| name | unitid | buffer | Set Value | |
| lifecyclestatus | Submitted | varchar(9) | Set Value | |
| horizontalcrs | 4326 | unit16 | Set Value | |
| businesscontact | City of Burnaby | varchar(15) | Set Value | |
| utilitytype | Sewer | varchar(5) | Set Value | |
| accesstupe | Manhole | varchar(7) | Set Value |
6. Assign a Unique Identifier
Add a UniqueIdentifierGenerator to each output port of the previous AttributeManagers. Set the following parameters:
- Unique Identifier Type: Universally Unique Lexicographically Sortable Identifier(ULID)
- Generated ID: systemid
7. Add a Geopackage Writer
Add a writer to the workspace. In the Add Writer dialog, set the following:
- Format: OGC Geopackage
- Dataset: Burnaby_MUDDI_SLM.gpkg
- Table Definition: Import from Dataset
From there, open up the Parameters and set the following:
- Overwrite Existing Geopackage: Yes
- Template: .\Burnaby_MUDDI_SLM_Template.gpkg
- Coordinate System: EPSG:4326
- Always Create Tables: Yes
Click OK twice.
Now an Import Writer Feature Types dialog will appear. Change the Dataset, and point it to the Template we set earlier.
- Format: OGC Geopackage
- Dataset: .\Burnaby_MUDDI_SLM_Template.gpkg
-
Parameters:
-
Tables:
- access, waterpipe, sewerpipe, sewernetworkappurtenance
- Click the ellipsis button to select tables
- access, waterpipe, sewerpipe, sewernetworkappurtenance
-
Tables:
Click OK. This should add all the Feature types we will use for this tutorial.
Connect the waterpipe Feature Type to the UniqueIdentifierGenerator from the Watermain stream.
Connect the sewernetworkappurtenance Feature Type to the UniqueIdentifierGenerator from the sewernetworkappurtenance stream.
Connect the access Feature Type to the UniqueIdentifierGenerator from the access stream.
Connect the sewerpipe Feature Type to the UniqueIdentifierGenerator from the Sanitary/Storm stream.
The workspace should now look like this:
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.