FOSS4G 2023 academic track

Digitization and update of road network databases represents a crucial topic for a good management of critical infrastructures by public administrations. Similarly to other European countries such as Cyprus (Christou et al., 2021), since 2001, Italian road-owning agencies have been required by the Ministry of Infrastructure and Trasport to build and maintain a road cadastre, i.e., a mapping inventory of their road networks. Such architecture should include georeferenced information about streets as well as all ancillary elements regulated by road regulations, ranging from safety and protection assets to traffic signs. In particular, due to the high frequency of new signals installation and substitution, traffic signs require a well-structured, flexible and efficient workflow for collecting and manipulating georeferenced data.

In agreement with the official national requirements, in 2019 the Province of Piacenza adopted and implemented a digital cadastre with GIS and WebGIS functionalities built on top of free and open-source software like PostgreSQL as Database Management System and QGIS for the manipulation of geodata. Such software infrastructure ensures flexibility of usage as well as the possibility to expand its functionalities with other easy-to-use open-source applications in an architecture (Gonzalez Alba et al., 2019, Gharbi & Haddadi, 2020). In this framework, this work illustrates a case study of a flexible and low-cost mapping methodology for documenting the current state of traffic signs. Indeed, mobile applications are able to substitute the old procedure that consisted in the documentation of element installation on paper support, implying the risk of transcript errors as well of loss or deterioration of the original survey document.

Before defining the required steps of mobile mapping, understanding how traffic signs are modelled inside the adopted DB model was crucial. Such elements are indeed implemented through a one-to-many relationship between an entity representing the sign holder (parent table) and another one for the signs themselves (child table). In this way, it is possible to collect and manage individually information about each sign (main ones and supplementary ones as well) always linked to their support pole.

Together with the road cadastre responsible, an analysis was conducted to understand the specific needs for the application and the type of users involved in the in-situ survey process. This phase resulted in the choice of two possible open-source solutions to be tested and compared in terms of compatibility and usability by users with different technical background, integration with the actual infrastructure and possibility of customisation: Qfield because of its native compatibility with QGIS libraries and ODK Collect thanks to its simplified graphic user interface (GUI) that resembles commonly used data collection forms without a visible GIS GUI.

For the entire workflow, differences in the two applications were evaluated. For instance, having a direct inheritance of the original QGIS attribute table for Qfield, while in the case of ODK Collect the definition of each attribute of the form is required. Peculiarities in implementing 1-n relationships and widget formats have been identified too, aiming to understand the reproducibility of both procedures. Once the form design was finalised for both the applications, a guided field survey was conducted in order to train new users and to test the usability of the mobile mapping solutions. For this purpose, a series of test sites was chosen, identifying roads to be surveyed with different features or conditions. A diverse sample of test users was involved in the data collection activity, ranging from people with no previous experience in geospatial technologies to GIS technicians.

Finally, the data collected with both applications were reviewed in QGIS environment in a validation phase aimed at identifying differences between the dataset, their completeness, their position accuracy and their coherence with a ground truth represented by photos of corresponding traffic signs taken on field with mobile devices. First, the validation consisted in checking if the mapped elements were located within buffers (of 5, 25 and 50 meters) calculated along the surveyed streets and then evaluating the coherence between the street code inserted in the element field and the one of the roads in whose buffer such sign is included. Hence, a similar approach was adopted for comparing the value of municipality associated to the single sign field to the administrative boundaries within which it falls. A semantic validation on the traffic sign type documented with the mobile mapping was conducted by comparing values with what was depicted in photos taken on field. The entire validation routine process was automatized as much as possible with Python scripts using the PyQGIS library. All the validation scripts together with sample dataset will be included in a Github repository in order to make them openly reusable and adaptable to other specific project needs. An analysis on the synchronization process of the collected data on the original main database was evaluated too, marking different approaches involving plugins or automatic scripts.

In order to evaluate user experiences with the different mobile applications, a LimeSurvey feedback form was provided to users who tested the tools on field. Such form was designed to collect insights on different steps of the workflow – form design, data collection and post-processing -, tracking and evaluating possible differences between users with different background and no previous knowledge of geospatial concepts. This resulted in highlighting potentials and issues linked to the adoption of Qfield or ODK Collect for traffic signs mapping.

This work aims at presenting a case study for the adoption of a mobile mapping solution in the field of public administration, understanding potentials and limitations of these possible approaches, also in terms of introducing new users to FOSS4G applications. Because of this, the transparency of the entire workflow is being documented on a dedicated Github repository with informative guides, a QGIS demo project, ODK format definition files and all codes adopted for validation and synchronization purposes.

This work is based on the design and development of a system aimed at monitoring the urban transformations of the area used for the Expo 2015 exhibition in Milano, exploiting the potential offered by the storage and management of geographic data in a GIS environment (Burrough, 1986). The system is designed to collect and analyze data showing the changes of the urban landscape going through pre-Expo, to Expo and post-Expo transformations (Gaeta & Di Vita, 2021).

One of the reasons behind this work is the fact that a complete digital database documenting the urban transformations in the Expo 2015 area is not yet available. In fact, all the data which were needed to implement the GIS were originally represented by maps (in paper or in digital non georeferenced format) of development projects and by cartographic work attached to city plans. After checking the compatibility of the process with the original licenses, the maps had been made openly accessible to the public after being scanned, so they had to be geo-referenced and vectorized in order to be able to insert the data in the GIS database.

The implementation and use of GIS technology implied (i) the definition of the database conceptual and logical model; (ii) the acquisition of a large number of geographic data layers, which were structured according to the design of a relational database. Layers which were acquired included data on: cadastral parcels; buildings; players involved in the urban transformations; land regulations; open spaces; land cover; functional lots; public transport stops; roads and underground utility lines.

The structure of the DB has been designed based on a relational model (Codd, 1970) by following the standard methodology defined in 1975 by the ANSI - SPARC Committee, going through successive phases and originating the external, conceptual and logical models. Following this strategy, the external model was defined on what were assumed to be the future users’ needs in terms of data storage, consultation and queries on the data. Aiming at documenting also the timeline of the urban transformations of the area, the Entity Relationship Diagram (ERD) was designed integrating in a unique conceptual scheme the temporal dimension of the transformations, going from the pre-Expo, to the Expo which took place in Milano from May to October 2015 and finally the post-Expo layout of the area. Subsequently, the logical model of the database was also designed.

The data acquisition required to research a large number of sources, which were mainly represented by images of maps available online on the websites of the different stakeholders, ranging from public administration channels and OpenStreetMap crowdsourced geodata to official Expo 2015 communication platforms. They were then geo-referenced in order to acquire spatial elements in vector format which were afterwards stored in the spatial database of the GIS, becoming easily manageable and upgradeable in an interactive way. Notably, the topological models of the streets and of the underground network of the district heating were implemented, in the latter case also connecting each building with the corresponding segment of the network (Cazzaniga et al., 2013). Finally, the topological consistency and coherence of such network and its components was validated.

The application of GIS technologies to monitor the transformations of the entire site allowed to understand and analyze the different phases of the evolution of the urban territory, identifying critical issues and strengths of the development projects. Indeed, in the GIS environment it is now possible to perform reproducible elaborations and analysis useful to understand how the area changed in time, especially from an urban planning point of view. This approach can provide insights on the surface covered by buildings in the different periods and on the change of destination or decommissioning of exhibition pavilions in the post-EXPO environment. Moreover, the database model allows users to query the data in order to identify underground services as well as buildings that may be affected by future works on roads or structures located in the area of interest. Such functionalities and retrieved information could be crucial especially considering the recent construction of a critical structure like the new Galeazzi hospital, which has been operative since 2022. Finally, the possibility to present the project, the data and its related metadata and to communicate them also to a wider audience of non-technical users was envisaged through the publication of a WebGIS-on the Internet, which was tested with a demo. In future, by implementing further improvements, this prototype could lead to a decision support system, to be used as a tool to understand the area for the benefit of all actors involved with different expertise and background in the urban transformations. In particular, the choice of the web platform was driven by the possibility to make the project as accessible as possible also through expandable tools in support of geo-narratives and storytelling as well as easy-to-understand dashboards for visualizing quantitative analysis results.

The whole project has been developed by using free and open-source technologies, namely MySQL Workbench for the development of the database model, QGIS for the implementation of the system and GeoNode for the testing of the publication of the System on the Internet. The choice to use free and open-source technologies is both an economical and ethical solution aimed at knowledge sharing and at making the DB flexible and easily expandable, facilitating the integration of new data, their updating and the implementation of future functionalities, paying attention also to the technical accessibility even by non-expert users.