The process of developing information and technological tools for processing open data of smart cities used for consolidation, analytical processing and visualization of city data in the form of information panels is presented in this paper. Open data sets are available in Ternopil. Dashboards are developed by means of Microsoft Power BI, Power Query, Microsoft Bing Maps API services, Azure Machine Learning and ArcGIS. Their interaction with the random forest method makes it possible to cluster urban areas. The developed information panels are intended for managers of public utility companies and city administration. They improve the level of service delivery and enable you to track KPIs.
Over the last period of time, the world has experienced the increased migration of the
population to urban areas. Urbanization is accompanied by significant increase in the
number of urban residents [
The use of information and communication technologies in various aspects of people’s
everyday life generates big data sets [
At present, there are a large number of definitions for smart city among scientists from
different countries of the world. In this paper, we will assume that “A smart city is a city
that uses digital technologies, communication technologies and data analytics to create
effective and efficient service environment that improves the quality of life in the city and
contributes to its sustainable development” [
Improvements in computing technologies and networks resulted in the emergence of
the Internet of Things (IoT). Its purpose is to enable physical objects (things) to collect
data using sensors and exchange them via the Internet [
Open data generated by various IoT sensors play an important role in the development
of smart city. Integration and processing of data make it possible to create, implement, and
develop smart services with high social and economic characteristics. Open data sets and
collections in smart cities are produced in the urban digital environment. It is formed on
the basis of physical infrastructure (structures, buildings, etc.) and social factors – citizens,
institutions and organizations, educational and cultural institutions, authorities, etc. [
Open data can be [
Implementation of innovative digital services in smart cities requires consolidation of
open data sets. At the same time, there is the need to develop effective analytical tools
based on machine learning methods and algorithms. Researchers [
We will develop information and technological tools for consolidation and analytical processing of open data for smart city services and facilities.
At the moment, due to the martial law, open access to data is significantly limited in Ukraine. However, certain data sets that do not disclose critical information are published. In particular, there are a number of groups of open data sets on “Ternopil Open Data Portal” [14].
16 data sets are published and updated monthly: • • • • • • • • economy and investments – 2 data sets; healthcare – 1 data set; housing and communal services – 3 data sets; land resources – 1 data set; local self-government – 2 data sets; municipal property – 3 data sets; public transport – 3 data sets; urban infrastructure – 1 data set.
In this paper, we describe the development of information and technological tools. These tools are used for consolidation, analytical processing and visualization of open data sets. Data about calls to telephone hotlines of emergency dispatch services of “Ternopilvodokanal” [15] and data on emergency and routine maintenance of urban infrastructure are used [16].
Microsoft Power BI information and technological tools were used to process open data sets [17]. They were used to convert them into formats suitable for analytical processing and visualization. This makes it possible to create cross-platform interactive information dashboards [18] that can be accessed locally, via web interfaces, or through mobile applications. Data processing is carried out by Power Query software and algorithmic tools for consolidating heterogeneous and different types of data. They provide the opportunity to set up automated updating of information sets from dynamic open data sources on a scheduled basis.
The information and technological tool for tracking citizens’ requests to ME “Ternopilvodokanal” was developed in the form of the information dashboard. Data for 2023 are consolidated and processed in it.
At the stage of initial processing, the following fields are identified from the open data set: • • buildingNumber – number of the building; dayOfTheWeek – the name of the day of the week - used to track the load trend in relation to requests; month – the name of the month when the citizen’s appeal was received; nameOfTheCityPart – type of city location or neighborhood; quarter – number of the quarter in the year; reasonForTheRequest – a list of possible reasons for the request; requestAddresser – the name of the organization or enterprise to which the request was made; requesterDate – date of request; requestNumb – a unique identifier of the data set regarding citizens’ requests; status – the status of the process of processing citizens’ appeals; street – the name of the location; typeOfRequest – type of data set (accident, citizen appeal, resident complaint, etc.).
The set of open data sets regarding citizens’ appeals provides an opportunity to evaluate the performance indicators of utility companies operating municipal infrastructure. This makes it possible to develop ways to improve the quality of service (QoS).
Based on [19], the diagram of the processes of consolidation, analytical processing, and visualization of open data sets of Ternopil city was developed (see Fig. 1).
At the first stage, the collection of open data on citizens’ requests to ME “Ternopilvodokanal” is exported (see Fig. 1).
Then the input data sets are consolidated into xlsx files, submitted separately for each month of the calendar year. The applied functions of information technological tools used make it possible to automate the merging of all files from the collection based on the example of the first file.
The process of data consolidation, filtering and cleaning by means of Power Query information and technological tools is shown in Fig. 2. At the same time, the data can be viewed dynamically by quarter, month, and execution status. The key indicators of data collections are the number of appeals from citizens, their distribution by dates, type, reasons for appeals, and geographical location.
The developed information dashboard carries out analytical processing of open data regarding the requests to ME “Ternopilvodokanal”. As a result, recommendations regarding the identified problematic locations of the water supply network are formed. Decision-making support functions are implemented on the basis of machine learning. In particular, the method of unsupervised random forest [20] is used to identify clusters of geolocation data of citizen’ requests to ME “Ternopilvodokanal”. At the same time, the proximity matrix is used to measure the frequency of occurrence of geolocation point pairs in the same leaf of the final trees of the random forest.
The interaction of information and technological tools in Microsoft Power BI is organized by means of:
Microsoft Bing Maps API [21] – service for obtaining geolocation coordinates; Azure Machine Learning [22] – service for clustering geodata;
ArcGIS [23] – tool for clusters visualization on the map.
The interface of the developed information dashboard for consolidation, analytical processing and visualization of requests to ME “Ternopilvodokanal” is presented in Fig. 3. dashboard for tracking citizens’ requests to ME
Recommendations regarding problem clusters of the city are shown in Fig. 3. Due to the developed information dashboard for tracking citizens’ requests, more than six thousand six hundred of data sets on the requests of city residents for 2023 are consolidated in ME “Ternopilvodokanal”.
In order to test the dynamic data visualization, let us select the 2nd quarter of 2023 for the investigated period, executed requests, type of request – accident, request reason – clogged sewer in the apartment. The consolidated data collections for the second quarter of 2023 is presented in Fig. 4.
As the result of analytical processing, the information message about the fulfillment of one hundred and forty emergency visits, amounting 7.81% of the total number of citizen requests is produced.
The developed information dashboard for tracking citizen’ requests to ME “Ternopilvodokanal” provides functional capabilities for building-by-building grouping of data on citizen requests for a particular street. In particular, data on Zluky Street (Ternopil) are provided in Fig. 5.
The interface for mobile devices has been developed for the information dashboard for tracking citizens’ requests to ME “Ternopilvodokanal” (see Figure 6).
The information and technological tool for monitoring the processes of emergency and regular works of city infrastructure maintenance has been developed in the form of the information dashboard consolidating and processing data for the period from 2021 to 2023.
Based on [19], the diagram of the processes of consolidation, analytical processing, and visualization of open data sets on emergency and regular works of the city infrastructure maintenance is developed (see Fig. 7).
Information fields have been selected from the exported and consolidated data set on emergency and regular works: • • datelssue – the date of obtaining permission to perform emergency and regular works; day – the day of the week when the permission for the city infrastructure maintenance is received; • • • • • • • duration – work duration; endDate – date of completion of the works; id – unique record identifier; month – month of receipt of the permission; name – name of the enterprise or organization that has been issued the permission to carry out emergency and regular works; nameOwner – name of the territorial community; object – type of territory; placeWorks – address; quarter – quarter of permission receipt; startDate – date of the beginning of urban infrastructure maintenance; typeWork – type of works (emergency or scheduled); year – year of receiving the permission for the city infrastructure maintain urban maintenance.
The availability of data sets and collections makes it possible for the citizens to monitor the processes of emergency and regular works of the city infrastructure maintenance due to the information dashboard shown in Fig. 8.
From the data collections published in “Ternopil Open Data Portal”, 1169 data sets regarding the permissions for city infrastructure facilities maintenance are consolidated for the period from 2021 to 2023: •
446 – ME "Ternopilvodokanal"; • • 288 – ME "Ternopilmiskteplocomunenergo"; 279 – Ternopil city power supply company.
After receiving the permission, most of the works are completed within a few days.
The developed dashboard makes it possible to select dynamically data for analysis according to the time interval, type of work, institution, or location (see Figure 9).
The developed information dashboard for monitoring the processes of emergency and regular maintenance works is beneficial for city administration employees while developing and implementing KPIs. In particular, due to the it is possible to track the duration of work and, in case violation of the terms of execution, coordinate promptly further actions with the executors. The information dashboard has mobile interface that requires Internet connection.
Urban data sets are dynamic and require innovative solutions to analyze and display them. Information and technological tools for processing open data of smart cities are described in this paper. They are used for consolidation, analytical processing, and data visualization in the form of information dashboards. Rapid response of the employees of municipal enterprises and representatives of local authorities to the requests and needs of residents while implementing information dashboards make it possible to improve the city’s performance indicators and to assess the “smartness” indicators in general. Engineering, 11.11s (2023) 06-16. URL: https://ijisae.org/index.php/IJISAE/article/view/3430. [14] Open data portal of Ternopil. URL: https://opendata.ternopilcity.gov.ua. [15] 39(40). Data on the receipt of requests to telephone hotlines, emergency dispatch services, call centers, etc. of ME “Ternopilvodokanal”. URL: https://opendata.ternopilcity.gov.ua/dataset/65ba50be-d8ba-4e0d-96f485d6784369cf. [16] 17. Data on issued permits for disturbance of public amenities. URL: https://opendata.ternopilcity.gov.ua/dataset/a758a620-c4f6-4554-b5e53d18f6c29eb0. [17] Power BI. URL: https://www.microsoft.com/en-us/powerplatform/products/power-bi. [18] O. Palka, L. Dmytrotsa, Using information dashboards in smart cities, Model. Control
Inf. Technol. No. 6 (2023) 189–191. doi:10.31713/MCIT.2023.058. [19] O. Palka, Construction of a hyper-complex system "Smart City": information and technological tools (Computer Science), Ph.D. thesis, Ivan Puluj National Technical University, Ternopil, Ukraine, 2024, 102-108. URL: http://elartu.tntu.edu.ua/handle/lib/44719. [20] F. Sassite, M. Addou, F. Barramou, A machine learning and multi-agent model to automate big data analytics in smart cities, Int. J. Adv. Comput. Sci. Appl. 13.7 (2022). doi:10.14569/ijacsa.2022.0130754. [21] Bing Maps Dev Center. URL: https://www.bingmapsportal.com. [22] Azure Machine Learning. URL: https://azure.microsoft.com/enus/products/machine-learning. [23] ArcGIS Online. URL: https://www.arcgis.com/index.html.