Processing Roughometer 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 Roughometer surveys. To ensure data is reliable and comparable over time, Roughometer 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 Roughometer 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 Roughometer 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 Roughometer 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 Roughometer 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.

• Roughometer CSV survey files
• Carriageway section geometry (GeoJSON exported from AWM)
• Carriageway section export (CSV exported from AWM)
• Road filter CSV (Manually created)
• QGIS Processing Model file (DoWH SharePoint)

Retrieve, Clean and Load Roughometer Data

This section describes how Roughometer 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 Roughometer data.
2. Download all CSV files associated with the survey.
3. Open each downloaded Roughometer CSV file in Excel.
4. Save a copy with "cleaned" suffix.
5. Remove the metadata rows at the top of the file (likely 22 rows). Example in screenshot below.

Roughometer data before removing metadata.

1. Copy all data rows from the first file and paste it into a new file.
2. In the new file, append the data from the second file, removing the duplicate header row.
3. 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.
4. Confirm latitude and longitude fields are correctly recognized in the X and Y fields.
5. Add the layer to the map.

Roughometer 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.
3. Click on the Carriageway Section layer and select Export.
4. 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.

Export Carriageway Numbers

1. Open the Carriageway Section grid view in AWM by clicking on the layer Carriageway Section and selecting Show Grid.
2. Ensure the Carriageway No column is selected by clicking on Columns button above the table and checking that Carriageway No is ticked in the Carriageway Section Columns panel.
3. Select Export, which is above the table, and select All items and Microsoft Excel (Shown below). Now open this Excel file and convert the file to CSV and save.

Excel export of Carriageway Section data.

Create Road Filter File

This section outlines how to generate a road filter file that identifies the specific roads covered by the Roughometer survey. The road filter is then used in the QGIS processing model to restrict analysis to relevant roads only.

1. Load the exported GeoJSON file into QGIS by selecting Layer → Add Layer → Add Vector Layer.
2. Open the attribute table by right clicking on the layer and selecting Open Attribute Table.
3. Using the Select tool, hold shift and highlight all carriageway sections which lie on your previously imported layer.
4. At the bottom of the screen select Show All Features → Show Selected Features.
5. Copy the resulting highlighted table from the previous step into an Excel file using Ctrl C and Ctrl V. (It should look something like the table shown in the image below).

Highlighted table of selected Carriageway Sections to be copied in QGIS.

1. In Excel delete all columns other than road_id, and then remove duplicate entries in the road_id column so that each road appears once. (Note you may just be left with one entry in the column if there is only one road).
2. Export the final list as a CSV, this is now your road filter file.

Run the QGIS Processing Model

1. If you don’t already have the model file navigate to it in SharePoint and download it.
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, Carriageway CSV, road filter CSV and Roughometer CSV. (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).
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.
3. 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.

Screenshot in QGIS of exporting the model output layer.

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

This section outlines the process for importing validated Roughometer roughness data into the AWM Roughness Reading table.

1. Open Citrix Workspace and launch RAMM Manager.
2. Confirm the correct database is selected (Papua New Guinea).
3. Go to File → Import File → Load.
4. Select the model output CSV file and click Next.
5. Choose the Roughness table ‘rough’ and click Next.
6. Choose Delimiter ‘Comma’ and click Next.
7. Select survey_number in the Fields in rough table, then select the dropdown and select an existing survey header or create a new one.

RAMM Manager showing dropdown for survey_number.

1. Still on the same page, look through table and see if any columns are empty. If so, select the column and unlink it from the CSV. Then leave it blank or populate it yourself.
2. If your data is historical and not the latest readings, you can select the ‘latest’ column and unlink it. Then from the dropdown select ‘Not latest’ and select link again to link it back to the column. 
3. Next sequentially select Next, Load, Validate, Check Duplicates and Move to RAMM. If you encounter any errors in these steps, they will need to be resolved before continuing.

Validate and Finalise Import

This section describes the final checks performed within AWM to confirm that Roughometer roughness data has been imported correctly and is visible in the Roughness Reading table. 

1. Open AWM and search for Roughness Reading.
2. Filter the table for the survey used, by selecting the filter icon above the table. Then click Add and select Survey as the column to filter by with the value to filter for being the survey used.
3. Visually check the data is now in the AWM Roughness Reading table. 

AWM Roughness Reading table after being imported.

1. If this is the latest survey on this particular section of road we need all other surveys for Roughness Reading on that road to be turned to historic. To do this, click process data and then in the status check window tick Set most recent flags for Roughness Data. Then begin processing. (Screenshots of how to do this are provided below).

Where to find the Process Data button.

Where to find the Set most recent flags for Roughness data button.