PostGIS: A powerful geospatial extension for PostgreSQL

PostGIS is a spatial extension for PostgreSQL that adds support for geographic and location-based data. It allows users to store, query, and analyze spatial data such as points, lines, and polygons directly in a PostgreSQL database. PostGIS enables powerful spatial operations such as calculating distances, measuring areas, performing spatial joins, and more. This makes it ideal for applications in mapping, Geographic Information Systems (GIS), and geospatial analytics. 

Installation

Despite PostGIS having its own command-line tools and the possibility to use certain features outside the server, the main functionality is in extending the features available in PostgreSQL databases. For this reason, this article will imply that the setup we want to achieve is extending the PostgreSQL server with PostGIS.

As of publishing, the only supported version of PostgreSQL is 16 on both Red Hat Enterprise Linux (RHEL) 9 and RHEL 10. Subsequent versions of PostgreSQL added to RHEL 9 and RHEL 10 will also come with supported PostGIS.

Install PostgreSQL server on RHEL 10

To install PostgreSQL server on RHEL 10, run the following:

# dnf install postgresql-server

This will install the default version of postgresql-server package.

Next, to install PostGIS, run:

# dnf install postgis

This will install PostGIS compatible with the default version of PostgreSQL. 

To choose a specific version (for example, 16), you can install specific PostgreSQL server by running:

# dnf install postgresql16-server

You can also install compatible PostGIS by running:

# dnf install postgresql16-postgis

Remember, the major version of PostgreSQL in the name of the package has to match.

Install PostgreSQL server on RHEL 9

In the RHEL 9 use case, modules will be used, and PostGIS will be present in the module that contains the PostgreSQL version of your choice.

Since the support for PostGIS starts with PostgreSQL version 16, it is necessary to first enable module containing PostgreSQL 16 by running the following:

# dnf module enable postgresql:16

Now, install PostgreSQL server:

# dnf install postgresql-server

Also install PostGIS:

# dnf install postgis

If you would like to use a different version of PostgreSQL, you will need to enable a different version of the module and install both aforementioned packages from that module.

Keep in mind that PostgreSQL 16 is the first version that has PostGIS available.

To use PostGIS extension with PostgreSQL server, it must be installed on the same system as the PostgreSQL server. The client that will be connecting to the server doesn’t need the PostGIS package installed.

After running the aforementioned commands, you should be able to continue with the practical examples in the next section.

Practical examples

The following queries require a working PostgreSQL server with PostGIS installed. For setup instructions, refer to the installation section above.

Add PostGIS extension to the PostgreSQL server

To add the PostGIS extension, simply make use of the CREATE EXTENSION query:

CREATE EXTENSION postgis;

You can verify if the extension was added successfully with the following:

SELECT postgis_full_version();

 Use the Geometry type of PostGIS extension

The PostGIS Geometry type supports various geometric shapes, including points, lines, polygons, polygons with holes, and collections.

To create a table using these types, use the following syntax:

CREATE TABLE geometries (name varchar, geom geometry);

To insert individual types, use the following INSERT query:

INSERT INTO geometries VALUES
  ('Point', 'POINT(0 0)'),
  ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
  ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
  ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
  ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

Project data using PostGIS extension

There are multiple ways to project data using PostGIS extension, which are mentioned in the upstream documentation.

For example, you can convert coordinates of an existing table for Broad St. subway station into geographics using this syntax:

SELECT ST_AsText(ST_Transform(geom,4326))
FROM nyc_subway_stations
WHERE name = 'Broad St'; 

Use the PostGIS Raster extension

PostGIS comes with a Raster extension that can be used for storing and analyzing raster data.

In order to use the Raster extension, it needs to be created:

CREATE EXTENSION postgis_raster;

Red Hat Enterprise Linux does not ship all Geospatial Data Abstraction Library (GDAL) drivers with PostGIS by default, as some might include outdated or unsupported code. Additionally, we strive to keep the PostGIS extension as lean as possible to maintain a minimal footprint and ensure stability.

To check which drivers are available, use:

SELECT short_name AS driver_name, long_name AS description, can_read, can_write FROM ST_GDALDrivers() ORDER BY driver_name;

To view metadata of a newly created raster, use this command:

SELECT ST_Metadata(ST_AddBand(ST_MakeEmptyRaster(5, 5, 0, 0, 1, -1, 0, 0, 4326), 1, '8BUI', 10, NULL));

To view the actual pixel values of some other newly created raster, use this command:

SELECT * FROM ST_DumpValues(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 4326), 1, '8BUI', 7, NULL));

Notable related tools

Other related tools worth mentioning include:

• GDAL: Translator for both vector and raster geospatial data formats. Even though the main use is as a library, this tool includes multiple command-line tools. Apart from simple conversions between data formats, it is also able to manipulate and modify the geospatial data.
• PROJ: Library for performing cartographic projections and coordinate transformations. This package also includes multiple command-line tools, but is mainly used as a library for other applications, including GDAL.
• GEOS: Command-line tool and a C++ library, that is used for vector geometry math. Unlike the other mentioned tools, it doesn’t handle coordinate systems or projections. It only works with geometry and math behind it, although there is a focus on algorithms used in GIS software. This library is also used by GDAL. You can use the command-line tool geosop to interact with the library without having to program and create the C++ application yourself. 

Notes

All mentioned packages, including PostGIS, can miss certain less-used features in RHEL to ensure a low storage footprint and the safety of provided packages. Some features might require old, unmaintained, or otherwise unsafe software, so they are not included to ensure the highest standards.

Sources

• PostGIS

• GDAL

• Introduction to PostGIS: Projecting Data

• Introduction to PostGIS: Geometries

• Raster reference

• Introduction to PostGIS: Rasters