=Paper=
{{Paper
|id=Vol-3742/paper1
|storemode=property
|title=Information and technological tools for analysis and visualization of open data in smart cities
|pdfUrl=https://ceur-ws.org/Vol-3742/paper1.pdf
|volume=Vol-3742
|authors=Oleh Palka,Lesia Dmytrotsa,Oleksii Duda,Nataliia Kunanets,Volodymyr Pasichnyk
|dblpUrl=https://dblp.org/rec/conf/citi2/PalkaDDKP24
}}
==Information and technological tools for analysis and visualization of open data in smart cities==
https://ceur-ws.org/Vol-3742/paper1.pdf
Information and technological tools for analysis and
visualization of open data in smart cities
Oleh Palka1,∗,†, Lesia Dmytrotsa1,∗,†, Oleksii Duda1,∗,†, Nataliia Kunanets2,∗,† and
Volodymyr Pasichnyk2,∗,†
1 Ternopil Ivan Puluj National Technical University, Ruska Str. 56, Ternopil, 46001, Ukraine
2 Lviv Polytechnic National University, S. Bandery str., 12, Lviv, 79000, Ukraine
Abstract
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.
Keywords
smart city, open data, information dashboard, clustering, unsupervised random forest,
information and technological tools, digital services 1
1. Introduction
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 [1]. It provides the basis for urban transformation and results
in global change in general. This is one of the most important trends of the past and
present century. According to forecasts [2], 66% of the world’s population will live in
cities by 2050. At present this figure equals to 54%. The urban population will increase
potentially by 2.5 billion people. This prospect will inevitably result in the expansion of
CITI’2024: 2nd International Workshop on Computer Information Technologies in Industry 4.0, June 12–14, 2024,
Ternopil, Ukraine
∗ Corresponding author.
† These authors contributed equally.
poleg1997@gmail.com (O. Palka); dmytrotsa.lesya@gmail.com (L. Dmytrotsa); oleksij.duda@gmail.com
(O. Duda); nek.lviv@gmail.com (N. Kunanets); vpasichnyk@gmail.com (V. Pasichnyk)
0000-0001-5607-279X (O. Palka); 0000-0003-2583-3271 (L. Dmytrotsa); 0000-0003-2007-1271
(O. Duda); 0000-0003-3007-2462 (N. Kunanets); 0000-0002-5231-6395 (V. Pasichnyk)
© 2024 Copyright for this paper by its authors. Use permitted under Creative Commons License Attribution 4.0 International (CC BY 4.0).
CEUR
ceur-ws.org
Workshop ISSN 1613-0073
Proceedings
urban environment and the need to create new cities. Therefore information technology
projects of smart cities are being actively implemented around the world.
The use of information and communication technologies in various aspects of people’s
everyday life generates big data sets [3]. The dynamic creation and updating of open data
sources makes it possible to arrange the processes city residents’ activities. It helps to
identify the relationship between human mobility and resource use in social and spatial
spheres of cities [4]. Analytical processing of open data and big data is used to support
“smart” decision-making processes. At the same time, intelligent data analysis and
machine learning methods are used. The interaction between information technologies,
social changes and political strategies creates a lot of new opportunities for the
development of smart cities [5].
2. Related Works
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” [6].
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 [7, 8]. IoT plays a key role in the
development of smart cities. It represents interconnected devices and systems operating
together in order to improve efficiency, sustainability and quality of urban life (QoL –
Quality of Life) [9]. For example, “smart” home and vehicle control applications have
attracted considerable attention in scientific and industrial communities. An extensive
range of network protocols have been developed to meet various needs of the smart city
based on the Internet of Things [10].
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. [11].
Open data can be [12]:
• obtained from official Internet resources of city institutions and organizations;
• obtained by means of analytical processing of big data;
• formed on the basis of information sets and collections of open data available to
the public.
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 [13] emphasize the
importance of intensification of data exchange processes, their analytical processing by
means of artificial intelligence and machine learning. After analytical processing of open
data, it is reasonable to develop information dashboards for their visualization.
3. Sets of open data in Ternopil city
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.
4. Dashboard for tracking citizens’ requests to ME
"Ternopilvodokanal"
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).
Figure 1: Activity diagram of the processes of consolidation, analytical processing and
visualization of open data regarding requests to ME “Ternopilvodokanal”
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.
Figure 2: The process of consolidating, filtering and cleaning data using Power Query
information and technological tools
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.
Figure 3: Information dashboard for tracking citizens’ requests to ME
“Ternopilvodokanal”
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.
Figure 4: Consolidated collections of open data on requests to ME “Ternopilvodokanal”
for the second quarter of 2023
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.
Figure 5: Building-by-building distribution of citizens’ requests on Zluky Street (Ternopil)
The interface for mobile devices has been developed for the information dashboard for
tracking citizens’ requests to ME “Ternopilvodokanal” (see Figure 6).
Figure 6: Interface of the information dashboard on citizens’ requests to ME
“Ternopilvodokanal” for mobile devices
5. Information dashboard for monitoring the processes of emergency
and regular works of city infrastructure maintenance
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).
Figure 7: Diagram of the processes of consolidation, analytical processing and
visualization of open data regarding emergency and regular works of the city
infrastructure maintenance
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.
Figure 8: Dashboard interface for monitoring the processes of emergency and regular
works of the city infrastructure maintenance in Ternopil
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).
Figure 9: Consolidation of data for 2022 on emergency and regular maintenance works at
10 Verbytskoho St., Ternopil
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.
6. Conclusion
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.
References
[1] E. Koomen, M. S. van Bemmel, J. van Huijstee, B. P. J. Andrée, P. A. Ferdinand, F. J. A.
van Rijn, An integrated global model of local urban development and population
change, Comput., Environ. Urban Syst. 100 (2023) 101935.
doi:10.1016/j.compenvurbsys.2022.101935.
[2] D. Mekonen, A. Bazezew, M. Anteneh, T. Kassie, Analysis of food security using various
indicators for policy implications: Empirical evidence from the two large cities of
Bahir Dar and Gondar, the Amhara region, Ethiopia, Geo 10.1 (2023).
doi:10.1002/geo2.122.
[3] A. Wang, A. Zhang, E. H. W. Chan, W. Shi, X. Zhou, Z. Liu, A review of human mobility
research based on big data and its implication for smart city development, ISPRS Int. J.
Geo-Information 10.1 (2020) 13. doi:10.3390/ijgi10010013.
[4] A. Wronkowski, Towards discovering human urban activity—tactics of human spatial
behavior, J. Hum. Behav. Soc. Environ. (2024) 1–19.
doi:10.1080/10911359.2024.2323052.
[5] M. Hilbert, Big data for development: A review of promises and challenges, Dev. Policy
Rev. 34.1 (2015) 135–174. doi:10.1111/dpr.12142.
[6] J. S. Gracias, G. S. Parnell, E. Specking, E. A. Pohl, R. Buchanan, Smart cities—a
structured literature review, Smart Cities 6.4 (2023) 1719–1743.
doi:10.3390/smartcities6040080.
[7] S. B. Altaf Khattak, M. Jia, M. Marey, M. M. Nasralla, Q. Guo, X. Gu, A novel single anchor
localization method for wireless sensors in 5G satellite-terrestrial network, Alex. Eng.
J. 61.7 (2022) 5595–5606. doi:10.1016/j.aej.2021.11.061.
[8] A. Ilyas, S. Mahfooz, Z. Mehmood, G. Ali, M. ElAffendi, Two-Way approach for
improved real-time transmission in fog-iot-based health monitoring system for
critical patients, Comput. Syst. Sci. Eng. 46.3 (2023) 3815–3829.
doi:10.32604/csse.2023.036316.
[9] R. Miranda, C. Alves, R. Sousa, A. Chaves, L. Montenegro, H. Peixoto, D. Durães, R.
Machado, A. Abelha, P. Novais, et al., Revolutionising the quality of life: the role of real-
time sensing in smart cities, Electronics 13.3 (2024) 550.
doi:10.3390/electronics13030550.
[10] A. Wang, A. Zhang, E. H. W. Chan, W. Shi, X. Zhou, Z. Liu, A review of human mobility
research based on big data and its implication for smart city development, ISPRS Int. J.
Geo-Information 10.1 (2020) 13. doi:10.3390/ijgi10010013.
[11] K. D. C. Adje, A. B. Letaifa, M. Haddad, O. Habachi, Smart city based on open data: A
survey, IEEE Access 11 (2023) 56726-56748. doi:10.1109/access.2023.3283436.
[12] R. Rattle, Negotiating privacy and trust in the geoaware smart city, PhD thesis,
Queensland University of Technology, Australia, 2023.
doi:10.5204/thesis.eprints.242467.
[13] C. D. Kokane, G. Mohadikar, S. Khapekar, B. Jadhao, T. Waykole, V. V. Deotare, Machine
learning approach for intelligent transport system in IOV-based vehicular network
traffic for smart cities, International Journal of Intelligent Systems and Applications in
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-96f4-
85d6784369cf.
[16] 17. Data on issued permits for disturbance of public amenities. URL:
https://opendata.ternopilcity.gov.ua/dataset/a758a620-c4f6-4554-b5e5-
3d18f6c29eb0.
[17] Power BI. URL: https://www.microsoft.com/en-us/power-
platform/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/en-
us/products/machine-learning.
[23] ArcGIS Online. URL: https://www.arcgis.com/index.html.