Install PostGIS for PostgreSQL on CentOS/RHEL 8

PostGIS is an extension for PostgreSQL database for adding support of Geospatial data. Here, you will learn how to install PostGIS on CentOS / RHEL 8 or similar Linux distros.

 

Table of Contents:

• What is PostGIS?
• PostGIS Features
• Environment Specification
• Update Linux Software Packages
• Install PostgreSQL Yum Repository on CentOS / RHEL 8
• Install PostGIS Extension on CentOS / RHEL 8
• Configure PostGIS extension for PostgreSQL
• Conclusion

 

What is PostGIS?:

PostGIS is an open source software program that adds support for geographic objects to the PostgreSQL object-relational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium (OGC).

Technically PostGIS was implemented as a PostgreSQL external extension.

The PostGIS implementation is based on "light-weight" geometries and indexes optimized to reduce disk and memory footprint. Using light-weight geometries helps servers increase the amount of data migrated up from physical disk storage into RAM, improving query performance substantially.

Recommended Book: PostGIS in Action, Third Edition (PAID LINK) by Leo S. Hsu and Regina Obe.

 

PostGIS Features:

Some of the famous features of PostGIS are:

• Geometry types for Points, LineStrings, Polygons, MultiPoints, MultiLineStrings, MultiPolygons and GeometryCollections.
• Spatial predicates for determining the interactions of geometries using the 3x3 DE-9IM (provided by the GEOS software library).
• Spatial operators for determining geospatial measurements like area, distance, length and perimeter.
• Spatial operators for determining geospatial set operations, like union, difference, symmetric difference and buffers (provided by GEOS).
• R-tree-over-GiST (Generalized Search Tree) spatial indexes for high speed spatial querying.
• Index selectivity support, to provide high performance query plans for mixed spatial/non-spatial queries.
• For raster data, PostGIS WKT Raster (now integrated into PostGIS 2.0+ and renamed PostGIS Raster)

 

Environment Specification:

We are using a minimal Red Hat Enterprise Linux 8 virtual machine with following specifications.

• CPU - 3.4 Ghz (2 cores)
• Memory - 2 GB
• Storage - 20 GB
• Operating System - Red Hat Enterprise Linux 8.4
• Hostname – postgis-01.centlinux.com
• IP Address - 192.168.116.238/24

 

Update Linux Software Packages:

By using a SSH client, connect with postgis-01.centlinux.com as root user.

You can update software packages in your Linux operating system, by executing following command.

# dnf update -y

If the above command updates your Linux Kernel, then you should reboot your operating system with the new Linux Kernel.

# reboot

After reboot, verify the version of your Linux Kernel and operating system.

# uname -r
4.18.0-305.19.1.el8_4.x86_64

# cat /etc/os-release
NAME="Red Hat Enterprise Linux"
VERSION="8.4 (Ootpa)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="8.4"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Red Hat Enterprise Linux 8.4 (Ootpa)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:8.4:GA"
HOME_URL="https://www.redhat.com/"
DOCUMENTATION_URL="https://access.redhat.com/documentation/red_hat_enterprise_linux/8/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"

REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8"
REDHAT_BUGZILLA_PRODUCT_VERSION=8.4
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8.4"

 

Install PostgreSQL Yum Repository on CentOS / RHEL 8:

PostGIS is extension to PostgreSQL. Therefore, you have to install PostgreSQL database before installing PostGIS extension.

You can also install the PostgreSQL database from source. But the preferred way is to install is by adding PostgreSQL official yum repository in your Linux server.

This yum repository provides all the required packages to install PostgreSQL and PostGIS on your Linux distribution.

Install PostgreSQL yum repository by executing following dnf command.

# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Disable the built-in PostgreSQL module in standard yum repository.

# dnf -qy module disable postgresql

Now, you can install your preferred version of PostgreSQL database by using dnf command.

Although, PostgreSQL 14 has been released, but we are using PostgreSQL 13 due to the stability and support by PostGIS extension.

# dnf install -y postgresql13-server

Initialize the PostgreSQL database server.

# /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK

Enable and start PostgreSQL database service.

# systemctl enable --now postgresql-13
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service → /usr/lib/systemd/system/postgresql-13.service.

Verify the status of PostgreSQL database service to ensure that it is started successfully.

# systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vend>
   Active: active (running) since Sun 2021-10-10 14:02:48 EDT; 16s ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 2183 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGD>
 Main PID: 2189 (postmaster)
    Tasks: 8 (limit: 5819)
   Memory: 16.8M
   CGroup: /system.slice/postgresql-13.service
           ├─2189 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─2190 postgres: logger
           ├─2192 postgres: checkpointer
           ├─2193 postgres: background writer
           ├─2194 postgres: walwriter
           ├─2195 postgres: autovacuum launcher
           ├─2196 postgres: stats collector
           └─2197 postgres: logical replication launcher

Oct 10 14:02:48 rhel8.centlinux.com systemd[1]: Starting PostgreSQL 13 database>
Oct 10 14:02:48 rhel8.centlinux.com postmaster[2189]: 2021-10-10 14:02:48.096 E>
Oct 10 14:02:48 rhel8.centlinux.com postmaster[2189]: 2021-10-10 14:02:48.096 E>
Oct 10 14:02:48 rhel8.centlinux.com systemd[1]: Started PostgreSQL 13 database >

Verify the version of your PostgreSQL database.

# psql -V
psql (PostgreSQL) 13.4

Connect with PostgreSQL database and set the password for PostgreSQL Administrator (postgres).

# su - postgres
$ psql
psql (13.4)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD '123';
ALTER ROLE
postgres=# \q
$ exit
logout

By default, PostgreSQL service runs on local interface, you can check this by executing following command.

# ss -tulpn | grep 5432
tcp   LISTEN 0      128        127.0.0.1:5432      0.0.0.0:*    users:(("postmaster",pid=2189,fd=7))
tcp   LISTEN 0      128            [::1]:5432         [::]:*    users:(("postmaster",pid=2189,fd=6))

To make PostgreSQL service accessible across the network, you have to run it on all network interfaces.

Edit PostgreSQL configuration file in vim text editor.

# vi /var/lib/pgsql/13/data/postgresql.conf

Locate following directive therein.

#listen_addresses = 'localhost'

And replace it with following directive.

listen_addresses = '*'

Allow network clients to access PostgreSQL service in pg_hba.conf file.

# echo "host all all 192.168.116.0/24 md5" >> /var/lib/pgsql/13/data/pg_hba.conf

Restart Database service to apply changes.

# systemctl restart postgresql-13.service

Verify the PostgreSQL service, now it is listening on all interfaces.

# ss -tulpn | grep 5432
tcp   LISTEN 0      128          0.0.0.0:5432      0.0.0.0:*    users:(("postmaster",pid=2321,fd=6))
tcp   LISTEN 0      128             [::]:5432         [::]:*    users:(("postmaster",pid=2321,fd=7))

Allow PostgreSQL service in Linux firewall.

# firewall-cmd --permanent --add-service=postgresql
success
# firewall-cmd --reload
success

 

Install PostGIS Extension on CentOS / RHEL 8:

Some of the required packages by PostGIS are not available in standard and PostgreSQL yum repositories, therefore, you are required to install EPEL (Extra Packages for Enterprise Linux) yum repository.

# dnf install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm

You also need to enable PowerTools repository.

For CentOS / Rocky Linux you can execute following command to enable PowerTools repository.

# dnf -y config-manager --set-enabled PowerTools

Whereas, for RHEL, execute the following command.

# subscription-manager repos --enable codeready-builder-for-rhel-8-x86_64-rpms
1 local certificate has been deleted.
Repository 'codeready-builder-for-rhel-8-x86_64-rpms' is enabled for this system.

Build cache for newly installed yum repositories.

# dnf makecache
Updating Subscription Management repositories.
Extra Packages for Enterprise Linux Modular 8 - 3.1 kB/s | 8.7 kB     00:02
Extra Packages for Enterprise Linux 8 - x86_64  3.5 kB/s | 6.1 kB     00:01
PostgreSQL common RPMs for RHEL/CentOS 8 - x86_  37  B/s | 195  B     00:05
PostgreSQL 14 for RHEL/CentOS 8 - x86_64         64  B/s | 195  B     00:03
PostgreSQL 13 for RHEL/CentOS 8 - x86_64         54  B/s | 195  B     00:03
PostgreSQL 12 for RHEL/CentOS 8 - x86_64         46  B/s | 195  B     00:04
PostgreSQL 11 for RHEL/CentOS 8 - x86_64         66  B/s | 195  B     00:02
PostgreSQL 10 for RHEL/CentOS 8 - x86_64         59  B/s | 195  B     00:03
PostgreSQL 9.6 for RHEL/CentOS 8 - x86_64        57  B/s | 195  B     00:03
Red Hat Enterprise Linux 8 for x86_64 - BaseOS  2.0 kB/s | 4.1 kB     00:02
Red Hat CodeReady Linux Builder for RHEL 8 x86_ 2.7 kB/s | 4.5 kB     00:01
Red Hat Enterprise Linux 8 for x86_64 - AppStre 2.8 kB/s | 4.5 kB     00:01
Metadata cache created.

Now, you can install PostGIS extension on you PostgreSQL server. There are many versions of PostGIS available in PostgreSQL yum repository. Choose the version that matches your database version.

# dnf install -y postgis31_13

 

Configure PostGIS extension for PostgreSQL:

Connect to PostgreSQL database server by using psql command.

# su - postgres
Last login: Sun Oct 10 14:04:03 EDT 2021 on pts/0
$ psql
psql (13.4)
Type "help" for help.

Create a new database.

postgres=# create database test1;
CREATE DATABASE

Connect to newly createdc database.

postgres=# \c test1
You are now connected to database "test1" as user "postgres".

Execute following commands to enable your required PostGIS extensions for test1 database.

test1=# CREATE EXTENSION postgis;
CREATE EXTENSION
test1=# CREATE EXTENSION postgis_raster;
CREATE EXTENSION
test1=# CREATE EXTENSION postgis_topology;
CREATE EXTENSION
test1=# CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION
test1=# CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
test1=# CREATE EXTENSION address_standardizer;
CREATE EXTENSION
test1=# CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION
test1=# CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION

Now, create a table and corresponding index with a Geospatial datatype.

test1=# CREATE TABLE mytable (
test1(#   id SERIAL PRIMARY KEY,
test1(#   geom GEOMETRY(Point, 26910),
test1(#   name VARCHAR(128)
test1(# );
CREATE TABLE
test1=# CREATE INDEX mytable_gix
test1-#   ON mytable
test1-#   USING GIST (geom);
CREATE INDEX

Now, insert some test data in this table.

test1=# INSERT INTO mytable (geom) VALUES (
test1(#   ST_GeomFromText('POINT(0 0)', 26910)
test1(# );
INSERT 0 1

Now, query the data as follows.

test1=# SELECT id, name
test1-# FROM mytable
test1-# WHERE ST_DWithin(
test1(#   geom,
test1(#   ST_GeomFromText('POINT(0 0)', 26910),
test1(#   1000
test1(# );
 id | name
----+------
  1 |
(1 row)

Exit from psql prompt.

test1=# \q
$ exit
logout

 

Conclusion:

In this article, you have learned about installation of PostGIS extension for PostgreSQL on CentOS /  RHEL 8 or similar Linux distos.