PostGIS is a spatial database extension for PostgreSQL that transforms a standard relational database into a full-featured geographic information system (GIS) backend. It adds support for geographic objects, spatial indexing, and hundreds of functions for analyzing and manipulating geospatial data. If you work with location data, mapping applications, or any kind of spatial analysis, PostGIS is the go-to extension for PostgreSQL.

In this guide, we walk through installing PostGIS on RHEL 10, Rocky Linux 10, and AlmaLinux 10 with PostgreSQL 17. We will enable the extension, verify the installation, create spatial tables, run spatial queries, set up GIST indexes, and cover common troubleshooting scenarios.

Prerequisites

Before you begin, make sure the following requirements are met:

  • A running instance of RHEL 10, Rocky Linux 10, or AlmaLinux 10
  • Root or sudo access on the server
  • PostgreSQL 17 installed and running (from the PGDG repository)
  • An active internet connection for package downloads

If you have not yet installed PostgreSQL 17, set up the PGDG repository and install it first. The steps below assume PostgreSQL 17 is already in place and the service is active.

Step 1 – Set Up the PGDG Repository

If you already configured the PostgreSQL Global Development Group (PGDG) repository during your PostgreSQL 17 installation, you can skip this step. Otherwise, install the repository RPM:

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

Disable the built-in PostgreSQL module so it does not conflict with PGDG packages:

sudo dnf -qy module disable postgresql

Verify that the PGDG repository is available:

sudo dnf repolist | grep pgdg

You should see entries for the PGDG 17 repository in the output. This confirms the repo is active and ready for use.

Step 2 – Install PostGIS for PostgreSQL 17

With the PGDG repository in place, install the PostGIS package built for PostgreSQL 17. The PGDG repo provides PostGIS 3.5 (the latest stable release at the time of writing):

sudo dnf install -y postgis35_17

This pulls in PostGIS along with its dependencies including GDAL, GEOS, and PROJ libraries. These are the core geospatial libraries that PostGIS relies on for coordinate transformations, geometry operations, and raster support.

If you also need raster support or address standardization, install the additional packages:

sudo dnf install -y postgis35_17-utils

Verify that the PostGIS shared library files are present:

ls /usr/pgsql-17/lib/postgis*.so

You should see output listing the PostGIS shared object files. This confirms the extension binaries are installed in the correct PostgreSQL library directory.

Step 3 – Enable PostGIS Extension in a Database

PostGIS is installed at the system level, but you need to enable it on a per-database basis. Connect to PostgreSQL as the postgres superuser and create a database for your GIS work:

sudo -u postgres psql

Create a new database (or use an existing one):

CREATE DATABASE geodb;
\c geodb

Now enable PostGIS in this database:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

The first command loads the core spatial functions and types. The second adds topology support, which is useful for working with network data and shared boundaries.

Verify that the extensions are loaded:

\dx

You should see postgis and postgis_topology listed in the installed extensions output.

Step 4 – Verify the PostGIS Installation

Run the version check function to confirm PostGIS is working correctly:

SELECT PostGIS_version();

Expected output will look similar to:

            postgis_version
---------------------------------------
 3.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

For more detailed version information including the GEOS, PROJ, and GDAL library versions, run:

SELECT PostGIS_full_version();

This output is valuable for troubleshooting and confirming that all dependent libraries are at the expected versions. As a DBA, I always check this after any PostGIS upgrade to make sure nothing was linked against outdated libraries.

Step 5 – Create a Spatial Table with a Geometry Column

Let us create a practical example. We will build a table to store point-of-interest locations using a geometry column with the WGS 84 coordinate system (SRID 4326):

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    geom GEOMETRY(Point, 4326),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The GEOMETRY(Point, 4326) column definition tells PostGIS to store point geometries using the WGS 84 spatial reference system. This is the coordinate system used by GPS devices and most web mapping applications.

Verify the table was created with the geometry column:

\d locations

You can also query the geometry_columns view to confirm PostGIS registered the column:

SELECT f_table_name, f_geometry_column, srid, type
FROM geometry_columns
WHERE f_table_name = 'locations';

Step 6 – Insert and Query Spatial Data

Insert some sample point data into the locations table. We use the ST_SetSRID and ST_MakePoint functions to create geometry values from longitude and latitude coordinates:

INSERT INTO locations (name, category, geom) VALUES
('Central Park', 'park', ST_SetSRID(ST_MakePoint(-73.9654, 40.7829), 4326)),
('Times Square', 'landmark', ST_SetSRID(ST_MakePoint(-73.9855, 40.7580), 4326)),
('Brooklyn Bridge', 'landmark', ST_SetSRID(ST_MakePoint(-73.9969, 40.7061), 4326)),
('JFK Airport', 'transport', ST_SetSRID(ST_MakePoint(-73.7781, 40.6413), 4326)),
('Statue of Liberty', 'landmark', ST_SetSRID(ST_MakePoint(-74.0445, 40.6892), 4326));

Query the data to see the stored locations with their coordinates in a readable format:

SELECT name, category, ST_AsText(geom) AS coordinates
FROM locations;

Find all landmarks within 5 kilometers of Times Square using ST_DWithin with geography casting for accurate distance calculations:

SELECT a.name,
       ROUND(ST_Distance(a.geom::geography, b.geom::geography)::numeric, 2) AS distance_meters
FROM locations a, locations b
WHERE b.name = 'Times Square'
  AND a.name != 'Times Square'
  AND ST_DWithin(a.geom::geography, b.geom::geography, 5000)
ORDER BY distance_meters;

This query calculates actual distances in meters by casting the geometry to geography type. When working with lat/lon data, always cast to geography for distance calculations – otherwise you get results in degrees, which are not meaningful for real-world measurements.

Step 7 – Spatial Indexing with GIST

Spatial queries on large datasets will be painfully slow without proper indexing. PostGIS uses GiST (Generalized Search Tree) indexes to speed up spatial lookups. Create a spatial index on the geometry column:

CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

For tables where you frequently run distance queries with geography casts, also create an index on the geography type:

CREATE INDEX idx_locations_geog ON locations USING GIST (geom::geography);

Verify the indexes are in place:

\di+ locations

To confirm the query planner uses the spatial index, run an EXPLAIN on a spatial query:

EXPLAIN ANALYZE
SELECT name FROM locations
WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(-73.9855, 40.7580), 4326)::geography, 3000);

Look for “Index Scan using idx_locations_geog” in the output. If you see a sequential scan instead, the planner may decide the table is too small for the index to help – this is normal for tables with only a few rows. On production tables with thousands or millions of rows, the GIST index makes a dramatic difference.

Step 8 – Common Spatial Functions

PostGIS ships with hundreds of spatial functions. Here are the ones you will use most often in production work:

ST_Distance – Calculate Distance Between Geometries

Returns the distance between two geometries. Cast to geography for results in meters:

SELECT
    a.name AS from_location,
    b.name AS to_location,
    ROUND(ST_Distance(a.geom::geography, b.geom::geography)::numeric, 2) AS distance_meters
FROM locations a
CROSS JOIN locations b
WHERE a.name = 'Central Park' AND b.name = 'Brooklyn Bridge';

ST_Within – Test If One Geometry Is Inside Another

Returns true if the first geometry is completely inside the second. This is useful for checking if points fall within a defined area:

-- Create a polygon representing a bounding area around Manhattan
SELECT name
FROM locations
WHERE ST_Within(
    geom,
    ST_MakeEnvelope(-74.05, 40.68, -73.90, 40.88, 4326)
);

ST_Buffer – Create a Buffer Zone Around a Geometry

Creates a polygon representing all points within a given distance of a geometry. Useful for proximity analysis:

-- Find locations within a 2km buffer of Times Square
SELECT l.name
FROM locations l,
     (SELECT geom FROM locations WHERE name = 'Times Square') ts
WHERE ST_Within(
    l.geom,
    ST_Buffer(ts.geom::geography, 2000)::geometry
)
AND l.name != 'Times Square';

ST_AsGeoJSON – Export as GeoJSON

Converts geometry to GeoJSON format, which is the standard for web mapping applications:

SELECT name, ST_AsGeoJSON(geom)::json AS geojson
FROM locations
LIMIT 3;

Step 9 – Configure PostGIS for GIS Applications

When serving spatial data to GIS applications like QGIS, GeoServer, or web mapping frameworks, a few PostgreSQL tuning adjustments help with performance.

Edit the PostgreSQL configuration file:

sudo vi /var/lib/pgsql/17/data/postgresql.conf

Adjust these parameters based on your available memory and workload. For a dedicated GIS server with 16 GB of RAM, reasonable starting values are:

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 256MB
maintenance_work_mem = 1GB
random_page_cost = 1.1
max_parallel_workers_per_gather = 4

The work_mem setting is particularly important for spatial queries. Complex geometry operations (joins, buffers, intersections) use significant memory during sorting and hashing. Setting this too low forces PostgreSQL to spill to disk, which slows spatial queries dramatically.

If your application connects from remote hosts, make sure pg_hba.conf allows those connections:

sudo vi /var/lib/pgsql/17/data/pg_hba.conf

Add a line for your application network (adjust the subnet to match your environment):

# Allow GIS application server
host    geodb    gisuser    10.0.0.0/24    scram-sha-256

Also set listen_addresses in postgresql.conf if the server should accept remote connections:

listen_addresses = '*'

Restart PostgreSQL to apply all changes:

sudo systemctl restart postgresql-17

Verify the service is running:

sudo systemctl status postgresql-17

Open the PostgreSQL port in the firewall if remote access is needed:

sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --reload

Troubleshooting

ERROR: could not open extension control file postgis.control

This means the PostGIS package is not installed, or it was installed for a different PostgreSQL version. Confirm the correct package is in place:

rpm -qa | grep postgis

The package name must match your PostgreSQL major version. For PostgreSQL 17, you need postgis35_17. If you see a package for a different version, remove it and install the correct one.

ERROR: could not load library postgis-3.so

This usually indicates a library dependency problem. Check for missing shared libraries:

ldd /usr/pgsql-17/lib/postgis-3.so | grep "not found"

If any libraries show as “not found”, install the missing packages. Common missing dependencies include GEOS, PROJ, and GDAL libraries. Reinstalling PostGIS usually resolves this:

sudo dnf reinstall -y postgis35_17

Spatial Queries Return Wrong Distances

If your distance calculations return values that seem off (tiny decimal numbers instead of meters), you are likely computing distances on geometry types stored with SRID 4326 without casting to geography. Remember that geometry distance with lat/lon data returns results in degrees. Always cast to geography for meter-based calculations:

-- Wrong (returns degrees)
SELECT ST_Distance(a.geom, b.geom) FROM ...

-- Correct (returns meters)
SELECT ST_Distance(a.geom::geography, b.geom::geography) FROM ...

Slow Spatial Queries on Large Tables

If spatial queries run slowly, the first thing to check is whether a GIST index exists on the geometry column:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table_name'
AND indexdef LIKE '%gist%';

If no GIST index exists, create one. After creating the index, run ANALYZE on the table so the query planner has updated statistics:

CREATE INDEX idx_your_table_geom ON your_table_name USING GIST (geom);
ANALYZE your_table_name;

PostGIS Extension Upgrade After PostgreSQL Update

After upgrading PostGIS packages, you need to update the extension inside each database that uses it:

ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION postgis_topology UPDATE;

Then verify the updated version:

SELECT PostGIS_full_version();

Conclusion

You now have PostGIS up and running on RHEL 10, Rocky Linux 10, or AlmaLinux 10 with PostgreSQL 17. The setup covers the core extension installation, spatial table creation, data insertion and querying, GIST indexing for performance, and the most commonly used spatial functions. From here, you can connect GIS applications like QGIS or GeoServer to your spatial database and start building location-aware applications. For production deployments, keep an eye on your spatial index usage and tune work_mem based on the complexity of your spatial queries.

RELATED ARTICLESMORE FROM AUTHOR

LEAVE A REPLY

Please enter your comment!
Please enter your name here