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 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.
