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.