Visualizing PostgreSQL Data With Angular To Analyze What Passengers Find Rude to Do During a Flight

The Angular framework has recently undergone major updates, including a brand-new website. I decided to refresh my Angular skills and develop a small project. Given my background in data analysis, I opted to create a data visualization dashboard using an interesting dataset. Project Overview For this project, I explored data from the "41 Percent of Fliers Say It’s Rude To Recline Your Airplane Seat" article. This dataset provided a light-hearted yet insightful foundation for my dashboard. Development Stack As it’s a small pet project to try out my skills with the new Angular version, I also tried to use a combination of tools that was new for me and make it a bit more interesting. So the final stack looked like this: Framework: Angular 18, the latest version of the popular framework. Database: PostgreSQL, hosted on the ElephantSQL free service. Data visualization tool: Flexmonster Pivot Table & Charts, a JavaScript library for web reporting. I considered several options for data visualization tools, but Flexmonster stood out for its ability to handle the data volume efficiently and provide quick integration into my stack. It also has both charts and pivot functionality available out-of-the-box. Implementation Steps Setting Up Angular: I started by setting up an Angular project using Angular CLI. This included creating components, services, and routing modules. Configuring PostgreSQL: I set up a PostgreSQL database on ElephantSQL, ensuring that the data was correctly structured and optimized for querying. Integrating Flexmonster: Using Flexmonster's API, I integrated the pivot table and charts into my Angular application. This step involved customizing the visualizations to match the dataset and enhance user interactivity. Creating the project Let's create a basic Angular project with the following commands: ng new flying-etiquette --no-standalone --ssr=false --style=css cd flying-etiquette Now we will continue with the data source configuration. Creating a PostgreSQL database Let's create a PostgreSQL database and fill it with data from the CSV file so we can analyze passengers' feelings about certain behaviors. I've used the ElephantSQL service to create a remote PostgreSQL instance for free. Unfortunately, the service is shutting down and new accounts cannot be created. Feel free to use other similar services, such as Render, Neon, or Amazon RDS. To manage the database, I've used DBeaver. It's a free cross-platform solution that provides many useful features for working with different SQL databases. Connect DBeaver to your remote database in the following way: Click the New Database Connectionbutton in the top left corner: In the popup window, choose the PostgreSQL database option and click Next. Fill in the information for connecting to your database and click Finish. Here's an example: Once the connection has been established, your database instance will appear in the Database Navigator list: After downloading the CSV file with data, import it to the PostgreSQL database in the following way: In the Database Navigator, go to your database connection (e.g., jvdheadr) -> Databases -> database name (e.g., jvdheadr) -> Schemas. Right-click the schema name where you want to import the data (e.g., public) and select Import Data. In the popup window, choose the CSV import source and click Next. Locate the CSV data file on your computer. Then, ensure the Importer settings are configured correctly. Pay special attention to the Column identifier, Quote char, and Set empty strings to NULL options. Once you are finished, click Next. Configure the Tables mapping. If you are okay with the default configuration, click Next. To avoid errors, check the Disable batches and Ignore duplicate rows errors options. Then, click Proceed. Once the data is imported, you will see the newly created table. Check if all columns and data were exported correctly. Now we have a configured database. Searching through the guides on installing Flexmonster I discovered it has a special Data Server, that optimizes the work with different data sources. The process was also very straightforward so I decided to stick to this option. But let’s install the tool itself first. Installing Flexmonster To install and configure Flexmonster for our project, install Flexmonster CLI: npm install -g flexmonster-cli Flexmonster CLI provides quick and convenient access to all the Flexmonster tools. Once the Flexmonster CLI is installed, open the newly created project in a rich text editor or an IDE of your choice. Personally, I use Visual Studio Code. However, if you prefer working only with the terminal or a command line interface, you are welcome to continue doing it. Install Flexmonster Angular wrapper for our project using Flexmonster CLI: flexmonster add ngx-flexmonster Then, import FlexmonsterPivotModule into src/app/app.module.ts file located in our project: import { FlexmonsterPivotModule } from 'ngx-flexmonster'; @NgModule({ // ... imports: [ FlexmonsterPivotModule, // Other imports ], // ... }) And import Flexmonster styles into src/styles.css file: @import "flexmonster/flexmonster.min.css"; Finally, insert the directive in src/app/app.component.html: If you start the project at this stage, you will see a pivot table, but it's empty. Let's fix this by connecting a PostgreSQL database and Flexmonster. Connecting to the PostgreSQL database Now that Flexmonster has been added to the Angular project and our database has been created, we need to connect Flexmonster to the database. To do this, we need to use Flexmonster Data Server. Here's how this tool works: Connects to various data sources. Aggregates data on a server. Sends the aggregated data to Flexmonster. The Data Server can be installed with the following command: flexmonster add fds -r The -r flag will automatically open the Data Server after it's installed. Let's establish a connection between the Data Server and the database: Step 1. Click the "Add New Index" button: Step 2. Fill in the information about the index so it is similar to the following and click "Create": Here's a connection string to the sample PostgreSQL database: Server=stampy.db.elephantsql.com;Port=5432;Database=jvdheadr;User Id=jvdheadr;Password=4O7qgJ2C9VONkyn0Bp57V1I2EFggBhXd; Now let's connect Flexmonster Pivot to the Data Server. Specify the [report] attribute for the directive in the following way: Finally, let's preset which fields should be displayed in the pivot table, so we can visualize data as soon as the page loads: <fm-pivot [toolbar]="true" [report]="{ dataSource: { type: 'api', url: 'http://localhost:9500', index: 'flying-etiquette' }, slice: { // Fields displayed in rows rows: [{ uniqueName: 'Is it rude to knowingly bring unruly children on a plane?', // Filter blank members filter: { exclude: [''] } }], // Fields displayed in columns columns: [{ uniqueName: 'Gender', // Filter blank members filter: { exclude: [''] } }, { uniqueName: '[Measures]' }], // Measures measures: [{ uniqueName: 'RespondentID', aggregation: 'count' }] } }"> And that's it! Our project is ready. Let's start it by running the following command: npm start When the project is built, you will see the pivot table and be able to analyze the data. You can get the ready-to-use version of this project on my GitHub and use it as a starting point when developing your own app or simply to explore the dataset Conclusion The overall process of creating an Angular app, embedding the visualization tool and connecting it to PostgreSQL is pretty straightforward and fast to perform. So you can easily use this as a tutorial or an example for your Angular reporting application. Plus, now you get to know things people consider the rudest to do on your airplane trip!) The Angular framework has recently undergone major updates, including a brand-new website . I decided to refresh my Angular skills and develop a small project. Given my background in data analysis, I opted to create a data visualization dashboard using an interesting dataset. Angular framework Angular framework brand-new website brand-new website Project Overview For this project, I explored data from the "41 Percent of Fliers Say It’s Rude To Recline Your Airplane Seat" article . This dataset provided a light-hearted yet insightful foundation for my dashboard. "41 Percent of Fliers Say It’s Rude To Recline Your Airplane Seat" article "41 Percent of Fliers Say It’s Rude To Recline Your Airplane Seat" article Development Stack As it’s a small pet project to try out my skills with the new Angular version, I also tried to use a combination of tools that was new for me and make it a bit more interesting. So the final stack looked like this: Framework: Angular 18, the latest version of the popular framework. Database: PostgreSQL, hosted on the ElephantSQL free service. Data visualization tool: Flexmonster Pivot Table & Charts, a JavaScript library for web reporting. Framework: Angular 18, the latest version of the popular framework. Database: PostgreSQL , hosted on the ElephantSQL free service. PostgreSQL PostgreSQL ElephantSQL ElephantSQL Data visualization tool: Flexmonster Pivot Table & Charts , a JavaScript library for web reporting. Flexmonster Pivot Table & Charts Flexmonster Pivot Table & Charts I considered several options for data visualization tools, but Flexmonster stood out for its ability to handle the data volume efficiently and provide quick integration into my stack. It also has both charts and pivot functionality available out-of-the-box. Implementation Steps Setting Up Angular: I started by setting up an Angular project using Angular CLI. This included creating components, services, and routing modules. Configuring PostgreSQL: I set up a PostgreSQL database on ElephantSQL, ensuring that the data was correctly structured and optimized for querying. Integrating Flexmonster: Using Flexmonster's API, I integrated the pivot table and charts into my Angular application. This step involved customizing the visualizations to match the dataset and enhance user interactivity. Setting Up Angular : I started by setting up an Angular project using Angular CLI. This included creating components, services, and routing modules. Setting Up Angular Configuring PostgreSQL : I set up a PostgreSQL database on ElephantSQL, ensuring that the data was correctly structured and optimized for querying. Configuring PostgreSQL Integrating Flexmonster : Using Flexmonster's API, I integrated the pivot table and charts into my Angular application. This step involved customizing the visualizations to match the dataset and enhance user interactivity. Integrating Flexmonster Creating the project Let's create a basic Angular project with the following commands: ng new flying-etiquette --no-standalone --ssr=false --style=css ng new flying-etiquette --no-standalone --ssr=false --style=css cd flying-etiquette cd flying-etiquette Now we will continue with the data source configuration. Creating a PostgreSQL database Let's create a PostgreSQL database and fill it with data from the CSV file so we can analyze passengers' feelings about certain behaviors. I've used the ElephantSQL service to create a remote PostgreSQL instance for free. Unfortunately, the service is shutting down and new accounts cannot be created. Feel free to use other similar services, such as Render , Neon , or Amazon RDS . ElephantSQL ElephantSQL Render Render Neon Neon Amazon RDS Amazon RDS To manage the database, I've used DBeaver . It's a free cross-platform solution that provides many useful features for working with different SQL databases. DBeaver DBeaver Connect DBeaver to your remote database in the following way: Click the New Database Connectionbutton in the top left corner: In the popup window, choose the PostgreSQL database option and click Next. Fill in the information for connecting to your database and click Finish. Here's an example: Click the New Database Connection button in the top left corner: New Database Connection In the popup window, choose the PostgreSQL database option and click Next . PostgreSQL Next Fill in the information for connecting to your database and click Finish . Here's an example: Finish Once the connection has been established, your database instance will appear in the Database Navigator list: Database Navigator After downloading the CSV file with data , import it to the PostgreSQL database in the following way: CSV file with data CSV file with data In the Database Navigator, go to your database connection (e.g., jvdheadr) -> Databases -> database name (e.g., jvdheadr) -> Schemas. Right-click the schema name where you want to import the data (e.g., public) and select Import Data. In the popup window, choose the CSV import source and click Next. Locate the CSV data file on your computer. Then, ensure the Importer settings are configured correctly. Pay special attention to the Column identifier, Quote char, and Set empty strings to NULL options. Once you are finished, click Next. Configure the Tables mapping. If you are okay with the default configuration, click Next. To avoid errors, check the Disable batches and Ignore duplicate rows errors options. Then, click Proceed. In the Database Navigator , go to your database connection (e.g., jvdheadr) -> Databases -> database name (e.g., jvdheadr) -> Schemas. Database Navigator Right-click the schema name where you want to import the data (e.g., public) and select Import Data . Import Data In the popup window, choose the CSV import source and click Next . CSV Next Locate the CSV data file on your computer. Then, ensure the Importer settings are configured correctly. Pay special attention to the Column identifier , Quote char , and Set empty strings to NULL options. Once you are finished, click Next . Importer settings Column identifier Quote char Set empty strings NULL Next Configure the Tables mapping . If you are okay with the default configuration, click Next . Tables mapping Next To avoid errors, check the Disable batches and I gnore duplicate rows errors options. Then, click Proceed . Disable batches gnore duplicate rows errors Proceed Once the data is imported, you will see the newly created table. Check if all columns and data were exported correctly. Now we have a configured database. Searching through the guides on installing Flexmonster I discovered it has a special Data Server, that optimizes the work with different data sources. The process was also very straightforward so I decided to stick to this option. But let’s install the tool itself first. Installing Flexmonster To install and configure Flexmonster for our project, install Flexmonster CLI: npm install -g flexmonster-cli npm install -g flexmonster-cli Flexmonster CLI provides quick and convenient access to all the Flexmonster tools. Once the Flexmonster CLI is installed, open the newly created project in a rich text editor or an IDE of your choice. Personally, I use Visual Studio Code . However, if you prefer working only with the terminal or a command line interface, you are welcome to continue doing it. Visual Studio Code Visual Studio Code Install Flexmonster Angular wrapper for our project using Flexmonster CLI: flexmonster add ngx-flexmonster flexmonster add ngx-flexmonster Then, import FlexmonsterPivotModule into src/app/app.module.ts file located in our project: FlexmonsterPivotModule src/app/app.module.ts import { FlexmonsterPivotModule } from 'ngx-flexmonster'; @NgModule({ // ... imports: [ FlexmonsterPivotModule, // Other imports ], // ... }) import { FlexmonsterPivotModule } from 'ngx-flexmonster'; @NgModule({ // ... imports: [ FlexmonsterPivotModule, // Other imports ], // ... }) And import Flexmonster styles into src/styles.css file: src/styles.css @import "flexmonster/flexmonster.min.css"; @import "flexmonster/flexmonster.min.css"; Finally, insert the directive in src/app/app.component.html : src/app/app.component.html If you start the project at this stage, you will see a pivot table, but it's empty. Let's fix this by connecting a PostgreSQL database and Flexmonster. Connecting to the PostgreSQL database Now that Flexmonster has been added to the Angular project and our database has been created, we need to connect Flexmonster to the database. To do this, we need to use Flexmonster Data Server. Here's how this tool works: Connects to various data sources. Aggregates data on a server. Sends the aggregated data to Flexmonster. Connects to various data sources. Aggregates data on a server. Sends the aggregated data to Flexmonster. The Data Server can be installed with the following command: flexmonster add fds -r flexmonster add fds -r The -r flag will automatically open the Data Server after it's installed. -r Let's establish a connection between the Data Server and the database: Step 1. Click the "Add New Index" button: Step 1. "Add New Index" Step 2. Fill in the information about the index so it is similar to the following and click "Create" : Step 2. "Create" Here's a connection string to the sample PostgreSQL database: Server=stampy.db.elephantsql.com;Port=5432;Database=jvdheadr;User Id=jvdheadr;Password=4O7qgJ2C9VONkyn0Bp57V1I2EFggBhXd; Server=stampy.db.elephantsql.com;Port=5432;Database=jvdheadr;User Id=jvdheadr;Password=4O7qgJ2C9VONkyn0Bp57V1I2EFggBhXd; Now let's connect Flexmonster Pivot to the Data Server. Specify the [report] attribute for the directive in the following way: [report] Finally, let's preset which fields should be displayed in the pivot table, so we can visualize data as soon as the page loads: <fm-pivot [toolbar]="true" [report]="{ dataSource: { type: 'api', url: 'http://localhost:9500', index: 'flying-etiquette' }, slice: { // Fields displayed in rows rows: [{ uniqueName: 'Is it rude to knowingly bring unruly children on a plane?', // Filter blank members filter: { exclude: [''] } }], // Fields displayed in columns columns: [{ uniqueName: 'Gender', // Filter blank members filter: { exclude: [''] } }, { uniqueName: '[Measures]' }], // Measures measures: [{ uniqueName: 'RespondentID', aggregation: 'count' }] } }"> <fm-pivot [toolbar]="true" [report]="{ dataSource: { type: 'api', url: 'http://localhost:9500', index: 'flying-etiquette' }, slice: { // Fields displayed in rows rows: [{ uniqueName: 'Is it rude to knowingly bring unruly children on a plane?', // Filter blank members filter: { exclude: [''] } }], // Fields displayed in columns columns: [{ uniqueName: 'Gender', // Filter blank members filter: { exclude: [''] } }, { uniqueName: '[Measures]' }], // Measures measures: [{ uniqueName: 'RespondentID', aggregation: 'count' }] } }"> And that's it! Our project is ready. Let's start it by running the following command: npm start npm start When the project is built, you will see the pivot table and be able to analyze the data. You can get the ready-to-use version of this project on my GitHub and use it as a starting point when developing your own app or simply to explore the dataset on my GitHub on my GitHub Conclusion The overall process of creating an Angular app, embedding the visualization tool and connecting it to PostgreSQL is pretty straightforward and fast to perform. So you can easily use this as a tutorial or an example for your Angular reporting application. Plus, now you get to know things people consider the rudest to do on your airplane trip!)