How to install PostGIS Extension in PostgreSQL 13

Learn how to install the PostGIS extension in PostgreSQL 13 with this step-by-step guide. Enhance your PostgreSQL database with advanced geospatial capabilities using PostGIS. #centlinux #postgres #linux

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

PostGIS is an open-source extension for the PostgreSQL relational database that adds support for geographic objects and geospatial queries. It turns PostgreSQL into a spatial database, enabling it to store, query, and manipulate spatial data such as points, lines, and polygons. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium (OGC) and is widely used in geographic information systems (GIS) applications. Here are some key features and aspects of PostGIS:

Key Features of PostGIS

Geometric Types:

• Supports a variety of geometric types such as Point, LineString, Polygon, MultiPoint, MultiLineString, and MultiPolygon, allowing for complex spatial data representations.

Spatial Functions:

• Provides a rich set of spatial functions for querying and manipulating spatial data, including functions for calculating distances, areas, lengths, intersections, and more.

Spatial Indexing:

• Uses R-tree-based GiST (Generalized Search Tree) indexes to improve the performance of spatial queries, making it efficient to handle large datasets.

Geographic Types:

• Includes support for geographic coordinates (latitude and longitude) in addition to Cartesian coordinates, enabling the handling of spatial data on a global scale.

Raster Data:

• Supports raster data storage and manipulation, allowing for the integration of raster imagery with vector data in spatial analyses.

Topology Support:

• Provides topology functions and data structures to model and analyze the topological relationships between spatial features.

Compatibility and Standards:

• Adheres to OGC standards and works seamlessly with other GIS tools and software, facilitating interoperability and data exchange.

3D and 4D Support:

• Extends support to 3D geometries (e.g., PointZ, PolygonZ) and 4D geometries (including time), allowing for advanced spatial and temporal analyses.

Use Cases

Geospatial Analysis:

• Ideal for performing complex geospatial analyses such as proximity searches, clustering, route finding, and spatial joins.

Mapping and Visualization:

• Supports the creation of interactive maps and visualizations using spatial data, often in conjunction with web mapping libraries and frameworks.

Urban Planning and Management:

• Used by urban planners and local governments for managing spatial data related to land use, zoning, infrastructure, and public services.

Environmental Monitoring:

• Employed in environmental science for tracking and analyzing ecological data, such as wildlife habitats, pollution levels, and climate change impacts.

Telecommunications:

• Helps telecommunications companies manage and analyze spatial data related to network coverage, tower locations, and service areas.

Transportation and Logistics:

• Used in transportation planning, logistics, and supply chain management to optimize routes, track shipments, and manage fleets.

Advantages

• Powerful and Flexible: Combines the robustness of PostgreSQL with advanced spatial capabilities, providing a powerful platform for geospatial data management.
• Open Source: Free to use and backed by a vibrant community of developers and users who contribute to its continuous improvement and support.
• Integration: Easily integrates with a wide range of GIS tools, applications, and libraries, making it a versatile choice for various geospatial projects.

PostGIS is an essential tool for anyone working with spatial data, offering extensive functionality and performance benefits for managing and analyzing geospatial information within the PostgreSQL database environment.

Recommended Book: PostGIS in Action, Third Edition (PAID LINK) by Leo S. Hsu and Regina Obe.
Recommended Online Training: Introduction to Spatial Databases with PostGIS and QGIS 3

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

Read Also: How to install GeoServer on RHEL 8

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

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.

Install PostgreSQL 13 on RHEL 8

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 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 in PostgreSQL 13

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

Connect to database server by using psql command and Install PostGIS Extension in PostgreSQL.

# 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

Final Thoughts

Installing the PostGIS extension in PostgreSQL 13 enhances your database with powerful geospatial capabilities, enabling advanced geographic data management and analysis. This guide is designed to help you navigate the installation process smoothly. If you need further assistance or prefer professional help, I’m here to support you.

Visit my Fiverr profile here to explore my services. I offer expert installation and configuration of PostGIS in PostgreSQL, as well as other database management and geospatial solutions. Let me handle the technical details so you can focus on leveraging the full potential of your geospatial data.