Sistema de BI basado en datos abiertos para el análisis de los factores que inciden en el tráfico y la contaminación en la ciudad de Madrid

Abstract

En los últimos años ha existido una gran controversia acerca de la temática de la contaminación y el tráfico en la ciudad de Madrid, y en casi cualquier gran ciudad del mundo, y de las medidas de reducción propuestas. El objetivo principal de este proyecto consiste en el análisis de la contaminación y el tráfico junto a la peligrosidad de las vías de circulación de la capital española a través de datos abiertos. Para ello, se ha desarrollado una solución Business Intelligence haciendo uso de herramientas Microsoft®, efectuando fases de análisis, diseño, desarrollo y pruebas. En cuanto al proceso de investigación, se han tenido en cuenta una gran cantidad de conjuntos de datos para evaluar si son adecuados para el estudio. Estos datasets mantienen una relación lógica que permite establecer una conexión entre los mismos, facilitando el estudio. Una vez seleccionados los datasets, se han cargado en la base de datos de staging para proporcionar a los datos un formateo previo y facilitar tareas posteriores. En esta fase, se tuvieron que resolver problemas de integración relativos a la calidad de los datos y a las limitaciones técnicas de los equipos utilizados en el desarrollo. En referencia al diseño del Data Warehouse, se ha realizado un diagrama reducido, para entender las relaciones entre las tablas de manera sencilla, y otro completo, en el que se detallan los atributos y medidas de cada tabla. A lo largo de los procesos ETL, el principal problema a solucionar fue el tiempo requerido para las consultas SQL, el cual fue resuelto mediante la creación de índices y la optimización de las consultas desarrolladas. En el último apartado, se han desarrollado cuadros de mando con la finalidad de facilitar la búsqueda de información y representarla de manera clara para extraer conclusiones. Se realizó un dashboard por cada tabla de hechos creada (análisis horario, análisis diario y análisis mensual), los accidentes producidos y los datos demográficos junto a las localizaciones de las estaciones de medida. En referencia a las conclusiones, se han conseguido extraer muchos datos interesantes como pueden ser: las mejores horas del mes son las 4 AM, las 3 AM, las 5 AM y las 7 AM, por ese orden; Agosto es el mes en el que menos se contamina y menos accidentes ocurren; El momento en el que más se contamina es en las primeras horas del día 1 de enero o fechas que coinciden con fin de vacaciones; Las mujeres provocan menos accidentes que los hombres y, por tanto, menos víctimas. En conclusión, se considera que el desarrollo de este proyecto ha servido para poner en práctica lo aprendido durante el grado y para mejorar las habilidades, tanto técnicas como analíticas. Además, se ha aplicado este conocimiento en un área de gran repercusión social como el estado del tráfico, los accidentes y la calidad del aire en base a la contaminación atmosférica y la meteorología.

Abstract:

In recent years, there has been a great controversy about the issue of pollution and traffic in the city of Madrid, as in almost any large city in the world, and the proposed reduction measures. The main objective of this project is the analysis of the contamination and the traffic as well as the danger of the roads of the Spanish capital. For this, a Business Intelligence solution has been developed using Microsoft® tools, carrying out analysis, design, development and testing phases. Regarding the research process, a large number of data sets have been taken into account to assess whether they are suitable for the study. These maintain a logical relationship that allows to establish a connection between them, facilitating the study. Once the datasets are selected, they were loaded into the staging database to provide the data with a specific format to facilitate subsequent tasks. In this phase, there were integration problems, that had to be solved, regarding the quality of the data and the technical limitations of the equipment used in the development. In reference to the design of the Data Warehouse, a reduced diagram has been made, to understand the relationships between the tables in a simple manner. Besides this, also a complete diagram has been designed in which the attributes and measurements of each table are detailed. Throughout the ETL processes, the main problem was the execution time of the SQL queries, which was solved by creating indexes and optimizing the queries developed. In the last section, dashboards have been developed with the aim of facilitating the search of the datas and representing it clearly to draw conclusions. A dashboard was made for each additional fact table (hourly analysis, daily analysis and monthly analysis), the accidents produced and the demographic data along with the locations of the measurement stations. Regarding the outcomes, we have managed to extract many interesting conclusions such as: the best hours of the month are 4 AM, 3 AM, 5 AM and 7 AM, in that order; August is the least pollutant month and when the minimum number of accidents occur; The moment that is the most contaminated is in the first hours of 1st January or dates that match with the end of vacations; Women cause fewer accidents than men and, therefore, fewer victims. In conclusion, it is considered that the development of this project has served to put into practice what was learned during the degree and to improve skills, both technical and analytical. In addition, this knowledge has been applied in an area of great social impact such as the state of traffic, accidents and air quality based on air pollution and meteorology.