INSTEAD OF AN INTRODUCTION

“Qlife Index Brno” is a location-based analysis of quarters in Brno focused on noise pollution, crime rate, internet quality and public transport availability. You can view individual characteristics, measured at building level, on a map outlining each quarter. 

The final output is an interactive dashboard which enable the users to search the Life Quality Index ratings for families, students and seniors.

Quality of life assessment for families

Quality of life assessment for seniors

    Quality of life assessment for students

HOW IT ALL STARTED

When we found out we were neighbors, we passionately discussed how much we loved the quarter we lived in and the city of Brno in general. Interestingly enough, some of the available data sets  contained information and measurements of criteria that were substantial to our earlier discussions about the life in Brno. 

CHOOSING THE DATA

Different institutions provide, manage and update data for their own area of interest. Some data sets provide information lacking standardized measurements, others are spread across different time spans or simply lacking localization. Many of the available data sets made it impossible to consolidate everything under a single data store for analysis. 

Luckily, we found the website DATA.BRNO, where we discovered the data set which contained the information we needed to reach our goal. Czechitas pointed us in the right direction and the city of Brno welcomed us with all our questions. We also communicated with Mr. Mikuláš Muroň, the creator of the data set we used, who was extremely helpful.

Finally, we decided to analyze the data on noise pollution, crime rate, internet quality and public transport accessibility. These are objective measures which represent indicators that most people see as necessary conditions for a high quality of life.

DATA USED

Data on Crime Rate

The crime rate data we used are recorded at street level. The density of crime acts and violations are calculated per meter for a given street. Subsequently, buildings and houses are evaluated/measured by the crime rate density.

The data on the Number and Type of Crime and Offense are managed by the South Moravian Region Office and the map application is available here.

Data on Noise Rate

Strategic noise maps (SHM) are provided at regular intervals by the Ministry of Healthcare as part of the Strategic Noise Mapping of the Czech Republic available in the form of a web application. These maps measure noise in the vicinity of land communications, railways, agglomerations and airports.
For the purpose of our analysis, the building objects were evaluated as an average of the measured noise in Db, 50 m from a given object.

Data on Internet Speed

NetMetr is a monitoring device of the CZ.NIC company that provides measurements of the quality of Internet access services. Measurements include the location and type information of Internet connection extracted by measurement of WLAN and LAN networks. This open data have been provided since 2007 and are available here. We cleaned and imported data points for each residential building in our analysis.

Data on Public Transport Accessibility

Detailed information on public transport in Brno are available on the web page  Integrated Transport System of the South Moravian Region. You can find there data on location of stops, public transport schedules and infrastructure objects.

Using these data, we created an index which interprets each residential building according to its accessibility to the city center.

MAPPING IT ALL TOGETHER - The Hackaton

The next challenge was to put all available information on a single map of Brno. 

Piece of cake, we thought, we have the perfect data set which includes the key to our success, coordinates! Then we realized we need to figure out how to read the binary code since this was the format of all coordinates in our data set. Here is an example of one of the nearly 30000 cells that we needed to convert:

0106000020E610000001000000010300000001000000070000002F7F85CC959930408EB1135E829748402C64AE0CAA993040F1BDBF417B97484003D19332A99930406EDFA3FE7A9748404EB51666A1993040C2A1B778789748401366DAFE959930403D27BD6F7C974840D0F1D1E28C9930405F950B957F9748402F7F85CC959930408EB1135E82974840

Let's get our first Hackaton started!

While Marija and our mentor Pavel tried to solve the "binary issue" in Python, Helena decided to play with the QGIS (an open-source, cross-platform desktop geographic information system). Since QGIS is a specialized tool and Helena is the master tech researcher, together they soon succeded in converting almost 30000 rows of data from binary into the standard coordinates format. 

At this point we were unstoppable and moments later we had all of our data on a map! We now wanted to outline it all, at quarters level. 

During our deep brainstorming session, another mentor, Honza Dupal stopped by for a visit and introduced the Digital vector geographic database of the Czech Republic. This valuable resource enabled us to upload another layer onto our map in QGIS.
This is what it looked like:

All of our data mapped in QGIS

BEAUTIFUL BEGINNINGS WITH Power BI

The magic had just started and it was time to upload all of the data, including the layered maps to Power BI:

All of our data and layered maps in Power BI

This felt like victory. Life was beautiful, our map is neat and we were ready to celebrate!

However, this was only a beautiful beginning. Data cleaning was only a click away:

CONSTRUCTING THE QLIFE INDEX

The data we chose to work with use buildings recorded in the RÚIAN (Registry of territorial identification, addresses and real estate).

Distinction

Based on this construction object, it was possible to differentiate residential buildings from non-residential buildings. In the overall evaluation, residential buildings were assigned value 0, whereas value 1 means that the building is not intended for living (non-residential).

Standardization by Scaling of Attributes

We used a simple procedure to scale all data into an index; for each entry we converted the actual value on a scale of 0 to 1. To achieve it, we took inspiration from the well-known United Nations Human Development Index formula and adjusted it to our needs.

This way we were able to formulate our indexes to ensure that they follow the same order of magnitude. Finally, we standardized the attribute values so that they were comparable.

Weighing of Attributes - the Info Page

We used value 1 as a base value which we then multiplied by the values given by the attribute's importance for a given social group. If the attribute is not considered important for a given social group, the base value 1 is multiplied by 0.5, whereas if an attribute is considered to be very important for a given social group the value 1 is multiplied by 1.5.

INTERACTIVE DASHBOARD POSSIBILITIES

Color Scale

Using Power BI we were able to create interactive dashboards which contain data points color formatted according to our data analysis. To make the visual more intuitive, we used a color scale from green to red, where green is the most desirable and red is the least desirable.

The Way It Works

Users are able to select and zoom in on an area of interest or onto a certain data point on the map. Each data point pops up and provides detailed information, such as coordinates, quarter and the final calculation showing a percentage of how high the point is recommended for a given social group. The top right part of the dashboard shows the number of residential objects meeting the user's criteria.

Finally, users can easily switch between each dashboard and a reach the Information Page explaining the scaling and weighing of attributes.

The Way It Works

Some More to Play With

The detailed data used for this project made it possible to differentiate among residential buildings on an exact street.

Below is a close up of the street Švestková in Brno - Maloměřice and Obřany, where the crime rate index varies within a short distance:

With the geo-coordinates, for each evaluated residential building, it is easy to use Google's street view to see the real-life surroundings. The screenshot below ilustrates why the chosen data point above is in red. Despite not being far away, the houses in this area are quite isolated from the rest of the neighborhood.

Below, there is a nearby green data point which has a much more positive crime rate ranking:

The Google street view of the same exact location confirms that this side of the street is a denser and better looking residential area:

Looking into the Future - The Conclusion

Working on this project was a tremendous learning experience for us. Except from the learning perspective, the project also revealed a vision of how the city of Brno can shape the quality of life for its residents by using the data-driven solutions. We were pleasantly surprised that the authorities and the City of Brno have already taken steps towards the data-driven governance, which is described in detail in the broader strategic program Brno2050.

No matter how and what type of data is collected and measured within the city, they need to be accurate, updated timely and presented in a format appropriate for the public. This too was also an opportunity for us and our project. We succeeded to deliver the data to the public in a understandable and beneficial way.

The dream goal of our project is to gather more data and develop their automated updating. We would love to analyze other attributes that are significant for the quality of life in Brno.

A Thank you!

We would love to thank our mentor, Pavel Klammert, for his invaluable guidance and Czechitas for organizing the Data Academy.

Of course, a special thank you goes to our families for the patience and support, especially in the intense three months behind us. Thank you, Barunka and Ilin!

Last but not least, we were honored to have Mičuda as our model: