Install PostgreSQL and PostGIS

Prepare the open source relational database with geospatial support to receive IoT and RTLS data from Pareto Anywhere.

Prerequisites

Pareto Anywhere open source IoT middleware installed.

• 

• Run Pareto Anywhere on a PC

  Our step-by-step guide to install and run on a Windows/Mac/Linux PC (or server).

• 

• Run Pareto Anywhere on a Raspberry Pi

  Our step-by-step guide to install and run on any version of the ubiquitous Pi.

Installing PostgreSQL and PostGIS   Step 1 of 3

Install the pair on a specific operating system.

---

What's PostgreSQL?

PostgreSQL is the world's most advanced open source relational database.

What's PostGIS?

PostGIS extends the capabilities of the PostgreSQL relational database by adding support for storing, indexing, and querying geospatial data.

Install PostgreSQL Part 1

Follow the instructions specific to your operating system to install PostgreSQL:

• Linux
• Windows

Install PostGIS Part 2

Again, follow the instructions specific to your operating system to install PostGIS:

• Linux
• Windows

Now that PostgreSQL and PostGIS are installed, it is possible to create a database with the PostGIS extension, which is covered in Step 2 below.

Creating the database   Step 2 of 3

Create the database with PostGIS extension.

---

What's an extension?

PostgreSQL extensions, like PostGIS, package user-visible functions and/or use hooks to modify how the database handles certain processes.

Can I use pgAdmin?

The instructions below are for command line execution. If the pgAdmin interface is available, they can instead be executed through its graphical user interface.

Create the database Part 1

Open a terminal and then log in to PostgreSQL as the postgres user with the command sudo -u postgres psql

Once logged in, the terminal prompt should change to postgres=#, accepting database queries. Enter the following queries:

• CREATE USER reelyactive WITH ENCRYPTED PASSWORD 'paretoanywhere';
• CREATE DATABASE pareto_anywhere WITH OWNER reelyactive;

This will create a database called pareto_anywhere owned by user reelyactive with password paretoanywhere.

Create the PostGIS extension Part 2

Remain logged in as the postgres user and change to the pareto_anywhere database with the following command:

• \c pareto_anywhere

Then enter the following queries:

• CREATE EXTENSION postgis;
• SELECT postgis_full_version();

This will create the PostGIS extension on the pareto_anywhere database. The second query simply verifies the PostGIS version number.^*

^* ERROR: function postgis_full_version() does not exist would indicate that creation of the PostGIS extension was unsuccessful.

To log out of PostgreSQL, enter exit

Now that the database is created, it is possible to create tables in which data to write the IoT and RTLS data, which is covered in Step 3 below.

Creating the tables and indexes   Step 3 of 3

Create the database tables and indexes.

---

What's a table?

A database table is a structured collection of data organised into columns and rows, where each row is an entry.

What's an index?

A database index is a data structure designed to speed up data retrieval operations within a table by providing a rapid way to locate specific rows.

Alternative (OPTIONAL)

The /barnacles-postgres module provides scripts as a convenient alternative to Parts 1-3 below:

• Creating tables with npm run create-tables
• Creating indexes with npm run create-indexes

Connect to the database Part 1

Open a terminal and then log in to PostgreSQL as the reelyactive user on the pareto_anywhere database with the command psql -U reelyactive -h 127.0.0.1 -d pareto_anywhere

Once logged in, the terminal prompt should change to pareto_anywhere=>, accepting database queries. Enter the queries to create the tables and indexes, as indicated in Parts 2 & 3 below.

Create the tables Part 2

While logged in as the reelyactive user on the pareto_anywhere database, create the raddec, dynamb and spatem tables with the following queries:

• CREATE TABLE raddec ( _storeid bigint GENERATED ALWAYS AS IDENTITY, transmittersignature varchar(36) NOT NULL, timestamp timestamptz DEFAULT current_timestamp, raddec JSONB NOT NULL );
• CREATE TABLE dynamb ( _storeId bigint GENERATED ALWAYS AS IDENTITY, devicesignature varchar(36) NOT NULL, timestamp timestamptz DEFAULT current_timestamp, dynamb JSONB NOT NULL );
• CREATE TABLE spatem ( _storeId bigint GENERATED ALWAYS AS IDENTITY, devicesignature varchar(36) NOT NULL, timestamp timestamptz DEFAULT current_timestamp, spatem JSONB NOT NULL );
• SELECT AddGeometryColumn ('spatem','geom',4326,'POINT',3);

The fourth and final query above applies the PostGIS extension, adding a geometry column to the spatem table.

Create the indexes Part 3

While logged in as the reelyactive user on the pareto_anywhere database, create indexes on the raddec, dynamb and spatem tables with the following queries:

• CREATE INDEX raddec_transmitter_idx ON raddec (transmittersignature);
• CREATE INDEX raddec_timestamp_idx ON raddec USING BRIN(timestamp);
• CREATE INDEX dynamb_device_idx ON dynamb (devicesignature);
• CREATE INDEX dynamb_timestamp_idx ON dynamb USING BRIN(timestamp);
• CREATE INDEX spatem_device_idx ON spatem (devicesignature);
• CREATE INDEX spatem_timestamp_idx ON spatem USING BRIN(timestamp);

Our cheatsheet details the raddec, dynamb and spatem JSON output from the open source IoT middleware.

• 

• Developers Cheatsheet

  "Owl" you need to know about Pareto Anywhere's core data structures.

Validate the connection (OPTIONAL) Part 4

Observing data from Pareto Anywhere is as simple as running a specific startup script, or, for quick-and-dirty validation, run barnacles-postgres as described below.

• pareto-anywhere
• barnacles-postgres

git clone https://github.com/reelyactive/barnacles-postgres.git
cd barnacles-postgres
npm install
npm start

Tutorial prepared with ♥ by jeffyactive.

You can reelyActive's open source efforts directly by contributing code & docs, collectively by sharing across your network, and commercially through our packages.

Where to next?

Continue exploring our open architecture and all its applications.

• 

• Grafana with PostgreSQL

  Prepare Grafana to visualise IoT and RTLS data from Pareto Anywhere via a PostgreSQL database.

• 

• reelyActive Developers

  Browse all developer documentation and tutorials.

• 

• reelyActive

  Together, let's make sense of things.