Processing TotalPave Roughness Data

Background

Information relating to the PNG National Road Network is stored and managed within the AWM (ThinkProject ASSET & WORK MANAGEMENT, formerly RAMM) road management system.

The AWM database is the authoritative source for carriageway geometry, asset information, and condition data used by the Department of Works to support network planning, maintenance prioritisation, and investment decisions.

Road roughness data, measured as International Roughness Index (IRI), is a key pavement condition indicator and is collected periodically using TotalPave (mounted phone) surveys. To ensure data is reliable and comparable over time, TotalPave survey outputs must be spatially aligned to the AWM carriageway network and correctly managed within the AWM Roughness Reading table.

This article describes the standard process for preparing TotalPave survey data, spatially processing it in QGIS, validating outputs, and importing the results into AWM, including the correct treatment of latest and historic roughness records.

Introduction

This procedure provides a standardised method for processing TotalPave survey data and importing the results into the AWM road management system.

It describes the required input files, data preparation steps, spatial processing using QGIS, validation checks, and the final import of roughness data into the AWM Roughness Reading table. The process ensures that TotalPave survey data is correctly cleaned, spatially aligned to carriageway sections, and managed in accordance with AWM data requirements.

This procedure is intended to support consistent, accurate, and auditable entry of road roughness data for the PNG National Road Network.                

Methodology

Required Input Files

This section lists the datasets and configuration files required to process TotalPave survey data through QGIS and prepare it for import into AWM. All files must be available and correctly formatted before commencing the methodology steps to ensure the process runs correctly end to end.

• TotalPave CSV survey files (CSV exported from TotalPave website/portal)
• Carriageway section geometry (GeoJSON exported from AWM)
• QGIS Processing Model file (DoWH SharePoint)

Retrieve, Clean and Load TotalPave Data

This section describes how TotalPave survey files are retrieved and cleaned to produce a single, consistent dataset suitable for spatial processing. It covers the removal of metadata, consolidation of survey files, and loading of validated roughness data into QGIS with correct spatial reference.

1. Access the Department of Works SharePoint and navigate to the appropriate TotalPave data.
2. Download all CSV files associated with the survey.
3. Open each downloaded TotalPave CSV file in Excel.
4. Copy all data rows from the first file and paste it into a new file.
5. In the new file, append the data from the second file, removing the duplicate header row. Repeat for each CSV file
6. Save the combined dataset as a single CSV.

1. Open QGIS and create a new project.
2. Add a base map (e.g. OpenStreetMap).
3. Go to Layer → Add Layer → Add Delimited Text Layer, and load the combined CSV file using EPSG:4326.
   1. Load the CSV file twice: once using the Start Longitude and Start Latitude fields as the X and Y fields, and once using the End Longitude and End Latitude fields as the X and Y fields.
4. Add the layers to the map. Rename the layers such that you know which one uses the Start geometries, and which one uses the End geometries.

TotalPave data layer in QGIS.

Export Required Data from AWM

This section describes how the required carriageway datasets are exported from AWM for use in QGIS processing. These exports provide the authoritative geometry and carriageway identifiers needed to correctly align and validate Roughometer survey data.

Export Carriageway Geometry

1. Open AWM and confirm the correct database is selected (Papua New Guinea).
2. In the Menu search and select Carriageway Section.
   Carriageway Section layer in AWM
3. Click on the Carriageway Section layer in the Map view, and select Configure.
   Navigating to the "Layer Configuration" pane from the Map view
4. Select "Columns", then click the Add button, then "All". Then click apply twice. This will add these columns to the export as attributes.
   The Layer Configuration pane,

   Layer Columns pane

   , and

   Layer Columns - Add pane

    
   Applying changes to selected columns, with the "Apply" button in green, then the "Apply" icon in the top-left.
5. Click on the Carriageway Section layer and select Export.
   Carriageway Section layer Export button
6. Choose the following options: All items, WGS 84 (Latitude/Longitude), GeoJSON and Original (As seen below). Then click Export. 
AWM GeoJSON export of Carriageway Geometry.
7. Add the Carriageway Section GeoJSON to the QGIS project.

Carriageway Section GeoJSON added to QGIS as a vector layer, as shown in red

The GeoJSON file should include the following attributes:

Run the QGIS Processing Model

1. If you don’t already have the model file navigate to it in SharePoint and download it.
   1. Currently, this is located at https://pngamb.sharepoint.com/:u:/r/sites/PNGDoWHAMB/Shared%20Documents/10_MANAGEMENT%20TEAM%20(MGT)/RMS%20Delivery/01.1%20Netwok%20-%20Network%20Condition/Network%20Condition%20Data/Scripts/QGIS%20processing%20model/totalpave%20processing%20model%20-%20using%20csv%20start%20and%20end%20points.model3?d=w71cc94e33cbf4e7c80d5fb5b7c06f07b&csf=1&web=1&e=cubsDF
2. Open QGIS and select Processing → Model Designer.
3. Open the QGIS Processing Model file.

QGIS Processing Model file open in Model Designer.

1. Assign all required inputs: Carriageway GeoJSON,  and the two TotalPave CSV layers. (Note you should have already loaded the Carriageway GeoJSON and Roughometer CSV into QGIS and can hence select the layer instead of the file as the input).
   TotalPave Processing Model run screen
2. Run the model.

Review and Export Model Output

This section explains how to review the outputs generated by the QGIS processing model to ensure all required fields and records are present and valid. It also describes how to export the verified output to CSV format for downstream validation and import into AWM.

1. Locate the output layer created by the model.
2. Open the attribute table and verify required fields are present.
   1. Export the output layer as a CSV file to a local directory (e.g. C:\Temp). To do this, click the layer and select Export → Save Features as. Then change the Format from "ESRI Shapefile" to "Comma Separated Value [CSV]". Then click the ellipsis next to File name in order to browse, and choose a location to save the file. If a Select Datum Transformations windows pops up, click "OK". 

Screenshot in QGIS of exporting the model output layer.

Screenshot in QGIS of the Save Feature as... window.

Validate Data Integrity

1. Open the exported CSV from the previous step. If an error shows up when opening, remove the layer from QGIS and attempt to reopen the file.
2. Compare row counts between the input CSV and the model output CSV.

Comparison of input vs output CSV.

1. Identify missing rows caused by missing values or spatial offset.
2. If there are missing rows in the output this is caused by missing values in the input or a spatial offset. If required, you can adjust the snap distance in the QGIS Model Designer by double clicking on the ‘Snap roughness data’ cell in the model and changing the tolerance.

Import data into AWM

Following the validation step, it is time to upload the data into AWM. Guidance for this process is provided in the article: Road Roughness - AWM Import Process

Validate Data Integrity after importing into AWM

This section covers a process to validate the roughness data loaded into AWM, against the original roughness survey data.