Install PostgreSQL and PostGIS

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

PostgreSQL and PostGIS

The TL;DR (Too Long; Didn't Read)

Learn how to install the pair for IoT and RTLS applications with Pareto Anywhere.

What's Pareto Anywhere?
Pareto Anywhere is open source IoT middleware that makes the data from just about anything usable.
Why include PostGIS?
To handle the real-time location (RTLS) data provided by Pareto Anywhere, and to facilitate geospatial queries and analysis.
Can I just install the pair?
Sure! Just follow Step 1 to install PostgreSQL & PostGIS and skip the rest.

Prerequisites

Pareto Anywhere open source IoT middleware installed.

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 and PostGIS

Install PostgreSQL Part 1

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

Open a terminal and then:

  1. Update the package list with the command sudo apt update
  2. Upgrade to the latest package versions with the command sudo apt upgrade
  3. Install PostgreSQL with the command sudo apt install postgresql     <1 min

The installed PostgreSQL version will depend on your OS version (ex: postgresql-16 on Ubuntu 24.10).

Download the latest PostgreSQL version from EDB and follow the installer instructions.

The default installer settings should apply to common use cases.

Install PostGIS Part 2

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

Open a terminal and then install PostGIS with the command sudo apt install postgresql-postgis     <1 min

The installed PostGIS version will depend on your OS version (ex: postgresql-16-postgis-3 on Ubuntu 24.10).

Run the StackBuilder program provided by the PostgreSQL installation, select the Spatial Extensions / PostGIS option and complete the installation.

The default installer settings should apply to common use cases.

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.
PostgreSQL database creation

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.

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.
PostgreSQL tables creation

See Creating tables and Creating indexes in the barnacles-postgres documentation.

Contributor
reelyActive half-barnowl

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.