Install PostGIS on Ubuntu 24.04 / Debian 13

PostGIS is a spatial database extension for PostgreSQL that adds support for geographic objects, spatial indexing, and hundreds of geospatial functions. It turns PostgreSQL into a full-featured spatial database capable of storing, querying, and analyzing location data – points, lines, polygons, rasters, and 3D geometry.

Original content from computingforgeeks.com - post 32587

This guide covers installing PostGIS on Ubuntu 24.04 and Debian 13 using the PostgreSQL PGDG repository, enabling the extension, creating spatial tables, running spatial queries, and importing shapefiles. The PGDG repository provides PostGIS 3.5/3.6 packages that are significantly newer than what ships in default OS repositories.

Prerequisites

• Ubuntu 24.04 LTS or Debian 13 server
• Root or sudo access
• PostgreSQL installed from the PGDG repository (PostgreSQL 16 or 17 recommended)
• At least 1GB free disk space for PostGIS and its dependencies

Step 1: Install PostGIS on Ubuntu 24.04 / Debian 13

If you installed PostgreSQL from the official PGDG repository, PostGIS packages are already available in the same repository. The package name follows the pattern postgresql-VERSION-postgis-3 where VERSION matches your PostgreSQL major version.

Check which PostgreSQL version is running on your system:

psql --version

The output confirms the installed PostgreSQL major version:

psql (PostgreSQL) 17.4 (Ubuntu 17.4-1.pgdg24.04+2)

Install PostGIS for your PostgreSQL version. For PostgreSQL 17:

sudo apt update
sudo apt install postgresql-17-postgis-3 postgis

The postgresql-17-postgis-3 package installs the PostGIS extension libraries. The postgis package provides command-line tools like shp2pgsql and raster2pgsql for importing spatial data.

If you are running PostgreSQL 16, install postgresql-16-postgis-3 instead. The -3 suffix always refers to the latest PostGIS 3.x version available in the repository.

For additional PostGIS extras including topology and address standardizer support:

sudo apt install postgresql-17-postgis-3-scripts

Step 2: Enable PostGIS Extension in a Database

PostGIS is a PostgreSQL extension that must be enabled per database. Create a new database for spatial data and enable PostGIS inside it.

Switch to the postgres system user and create a database:

sudo -u postgres createdb gisdb

Connect to the new database and enable PostGIS:

sudo -u postgres psql -d gisdb

Inside the psql prompt, run the following SQL to create the PostGIS extension:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION postgis_raster;

Each CREATE EXTENSION command loads a different PostGIS module. The core postgis extension is required. The topology and raster extensions are optional but useful for advanced spatial workflows.

Step 3: Verify PostGIS Installation

Confirm PostGIS is installed and check the version. While still connected to the gisdb database:

SELECT PostGIS_version();

The output shows the PostGIS version along with the linked GEOS and PROJ library versions:

            postgis_version
---------------------------------------
 3.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

For more detailed version information including all linked libraries:

SELECT PostGIS_full_version();

This returns the full build details including GEOS, PROJ, GDAL, and libxml2 versions. Confirm GEOS and PROJ are present – these are critical for spatial operations and coordinate reprojection.

List all installed extensions to confirm everything loaded properly:

SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%';

You should see postgis, postgis_topology, and postgis_raster with matching installed versions.

Step 4: Create a Spatial Table with Geometry Column

With PostGIS enabled, you can create tables that store geometry data. A typical pattern is a table with a geometry column constrained to a specific type (point, polygon, line) and spatial reference system (SRID).

Create a table to store city locations with point geometry in WGS 84 (SRID 4326):

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    population INTEGER,
    geom GEOMETRY(Point, 4326)
);

The GEOMETRY(Point, 4326) type constrains this column to store only 2D points in the WGS 84 coordinate system (latitude/longitude). SRID 4326 is the standard coordinate system used by GPS and most web mapping applications.

Create a spatial index on the geometry column for fast spatial queries:

CREATE INDEX idx_cities_geom ON cities USING GIST (geom);

GiST (Generalized Search Tree) indexes are essential for spatial performance. Without this index, every spatial query would scan the entire table.

Step 5: Insert and Query Spatial Data

Insert some city records with their geographic coordinates. PostGIS provides ST_SetSRID and ST_MakePoint functions to create geometry values from longitude and latitude.

INSERT INTO cities (name, population, geom) VALUES
('Nairobi', 4397073, ST_SetSRID(ST_MakePoint(36.8219, -1.2921), 4326)),
('Lagos', 15946000, ST_SetSRID(ST_MakePoint(3.3792, 6.5244), 4326)),
('Cape Town', 4618000, ST_SetSRID(ST_MakePoint(18.4241, -33.9249), 4326)),
('Cairo', 10230350, ST_SetSRID(ST_MakePoint(31.2357, 30.0444), 4326)),
('Dar es Salaam', 7405000, ST_SetSRID(ST_MakePoint(39.2083, -6.7924), 4326));

Note that ST_MakePoint takes longitude first, then latitude. This is a common source of errors – geographic convention is lat/lon but PostGIS follows the mathematical convention of x (longitude) then y (latitude).

Query the data to see coordinates in Well-Known Text (WKT) format:

SELECT name, population, ST_AsText(geom) AS location FROM cities;

The result displays each city with its geometry as a readable point string:

     name      | population |          location
---------------+------------+----------------------------
 Nairobi       |    4397073 | POINT(36.8219 -1.2921)
 Lagos         |   15946000 | POINT(3.3792 6.5244)
 Cape Town     |    4618000 | POINT(18.4241 -33.9249)
 Cairo         |   10230350 | POINT(31.2357 30.0444)
 Dar es Salaam |    7405000 | POINT(39.2083 -6.7924)
(5 rows)

To get coordinates as GeoJSON (useful for web mapping):

SELECT name, ST_AsGeoJSON(geom) AS geojson FROM cities LIMIT 2;

Step 6: Spatial Queries – ST_Distance, ST_Within, ST_Intersects

The real power of PostGIS is spatial queries – finding distances between points, checking if geometries overlap, and filtering by proximity. These functions work directly in SQL and benefit from GiST indexes.

Calculate Distance Between Cities

Use ST_Distance with the geography type cast for distances in meters on the Earth’s surface:

SELECT
    a.name AS city_a,
    b.name AS city_b,
    ROUND(ST_Distance(a.geom::geography, b.geom::geography) / 1000) AS distance_km
FROM cities a, cities b
WHERE a.name = 'Nairobi' AND b.name != 'Nairobi'
ORDER BY distance_km;

The ::geography cast is important. Without it, ST_Distance returns degrees (useless for real distances). With the geography cast, it calculates great-circle distance in meters which we divide by 1000 for kilometers:

 city_a  |    city_b     | distance_km
---------+---------------+-------------
 Nairobi | Dar es Salaam |         623
 Nairobi | Cairo         |        3518
 Nairobi | Lagos         |        3351
 Nairobi | Cape Town     |        3862
(4 rows)

Find Cities Within a Radius

Use ST_DWithin to find all cities within a given distance. This query finds cities within 4000 km of Nairobi:

SELECT name, ROUND(ST_Distance(geom::geography,
    ST_SetSRID(ST_MakePoint(36.8219, -1.2921), 4326)::geography) / 1000) AS distance_km
FROM cities
WHERE ST_DWithin(geom::geography,
    ST_SetSRID(ST_MakePoint(36.8219, -1.2921), 4326)::geography,
    4000000)
AND name != 'Nairobi';

The third argument to ST_DWithin is the radius in meters (4000000 = 4000 km). This function uses the spatial index for fast filtering – much better than computing distance for every row and filtering with WHERE.

Check if Points Intersect a Bounding Box

Use ST_Intersects with ST_MakeEnvelope to find cities within a rectangular area. This query finds cities within East Africa (roughly 28-42E, 12S-5N):

SELECT name FROM cities
WHERE ST_Intersects(geom, ST_MakeEnvelope(28, -12, 42, 5, 4326));

The result returns cities whose coordinates fall within the bounding box:

     name
---------------
 Nairobi
 Dar es Salaam
(2 rows)

Step 7: Import Shapefiles with shp2pgsql

Shapefiles are the most common format for exchanging GIS data. PostGIS includes shp2pgsql, a command-line tool that converts shapefiles to SQL and loads them into PostgreSQL.

The basic syntax for importing a shapefile:

shp2pgsql -s 4326 -I /path/to/shapefile.shp table_name | psql -d gisdb

The flags control how the import works:

Flag	Purpose
-s 4326	Set the SRID (coordinate system) of the source data
-I	Create a GiST spatial index on the geometry column
-a	Append data to an existing table instead of creating a new one
-D	Use PostgreSQL dump format for faster bulk loading
-W latin1	Specify encoding of the DBF attribute file

For example, to import a countries shapefile into a table called world_borders:

shp2pgsql -s 4326 -D -I /data/TM_WORLD_BORDERS-0.3.shp world_borders | psql -U postgres -d gisdb

After import, verify the data loaded correctly:

psql -U postgres -d gisdb -c "SELECT COUNT(*) FROM world_borders;"

To export data back to a shapefile, use pgsql2shp:

pgsql2shp -f /tmp/export.shp -h localhost -u postgres gisdb "SELECT * FROM cities"

Step 8: Connect PostGIS with QGIS Desktop

QGIS is the most popular open-source GIS desktop application. It connects directly to PostGIS databases, letting you visualize, edit, and analyze spatial data stored in PostgreSQL.

Before connecting from QGIS, make sure PostgreSQL is configured to accept remote connections. Edit postgresql.conf to listen on the server’s IP address:

sudo vi /etc/postgresql/17/main/postgresql.conf

Set the listen address to accept connections from your network:

listen_addresses = '*'

Edit pg_hba.conf to allow your QGIS workstation to connect:

sudo vi /etc/postgresql/17/main/pg_hba.conf

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

host    gisdb    postgres    192.168.1.0/24    scram-sha-256

Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

Open the firewall port for PostgreSQL (TCP 5432):

sudo ufw allow 5432/tcp

In QGIS, add a new PostGIS connection through Layer > Add Layer > Add PostGIS Layers. Enter the connection details:

• Name – A label for this connection (e.g., “Production GIS DB”)
• Host – Your PostgreSQL server IP address
• Port – 5432
• Database – gisdb
• Authentication – Basic, with your PostgreSQL username and password

Click “Test Connection” to verify connectivity. Once connected, QGIS will list all spatial tables in the database. Select a table and click “Add” to load it as a map layer. You can then style, query, and analyze the data using QGIS tools while PostGIS handles the spatial computations on the server side.

If you have PostGIS on Debian servers or PostGIS on RHEL/Rocky Linux, QGIS can connect to all of them simultaneously – useful for comparing datasets across different servers.

Conclusion

PostGIS is now running on your Ubuntu 24.04 or Debian 13 server with spatial tables, indexes, and query capabilities ready to use. You have a working setup for storing geometry data, running distance and proximity queries, and importing shapefiles from external GIS sources.

For production deployments, enable SSL encryption on PostgreSQL, set up regular database backups with pg_dump, and monitor query performance using pg_stat_statements. The PostGIS project on GitHub has detailed documentation on advanced topics like raster analysis, 3D geometry, and topology management.