Visualising Data with Angular and ngx-charts using NodeJS and MySQL

Introduction

Data visualisation is used to display data in a way that one can identify patterns and relationships between them. In this article i will explain how i created an application that creates timeline, bar and scatter graphs from the Gapminder dataset.

You can browse the code of the project while reading through this blog HERE.

Designing the database

Database design is the organization of data according to a database model. At first we must take into consideration how our csv data is structured. A CSV (Comma-Separated Values) is a delimited text file that uses a comma to separate values. A CSV file stores tabular data (numbers and text) in plain text. The gapminder dataset includes various data with indexes ranging from economics to health and infrastructure. You can browse the datasets here.

small portion of the Life Expectancy (years) dataset
Figure1. The data table format

Preparing the data

Right now we are stuck with some CSV files that don’t have the same years and countries. Lets work it out with python. We will use a filename convention to automate things a bit. Every CSV dataset that will be normalised will have follow this format:

The process of normalising the csv files

Setting up and Filling the database

First you have to set up MySQL on your machine. MySQL is available for downloading here. The default settings on the installation guide will do.

For windows users

If you want to change it or disable it you can find the my.ini file inside the
C:\ProgramData\MySQL\MySQL Server 8.0 and change the line: secure-file-priv=” ” . This can mess up the mysql server if its running. If the server cant start after this a complete re-install and DELETING the MySQL80 service after uninstalling is required. (This can be done with “sc.exe delete MySQL80” in any cmd running as an administrator.) After this we can re install the sql server and then it can run again normally.

Setting up the server

After the database is ready we need someone to serve the data to our app. We will use NodeJS for setting up the server. Node is available for downloading here. You also need to install mysql, cors and express with the node package manager like this:

Connections between the App the server and the database

Setting up the application UI

The application will be built using Angular and Angular Material available here.

The app structure

Wait a second, where did those data came?

The process above uses dynamic queries to produce procedurally the desired graph. To get these data we need a data service that will talk to our node API. Angular got us covered with injectables. Angular consists of components. Components are the building blocks of the entire application. An injectable provides services to a component.

Ngx-charts

Ngx-charts is using Angular to render and animate the SVG elements with all of its binding and speed goodness, and uses d3 for the excellent math functions, scales, axis and shape generators, etc. Examples of Ngx-charts can be browsed here.

Lets prepare the data for the ngx-charts

The timeline and bar graphs have have exactly the same data format.

The graph is hungry, lets feed it

Before that we have to define the graph options. We can adjust the size, the colours, the axis labels and much more.

Summary

At first we prepared the data with some scripting automation. We then proceeded to set up the database and populate its tables. Finally we created an API so our front-end app can talk to the server, prepare the data and feed it to the graph library.

Software Engineer, writing code and blogging about it. Found something interesting? Buy me a coffee! https://ko-fi.com/leobouts