Install PostgreSQL 18 with PostGIS on Ubuntu 24.04

By /

PostGIS adds spatial database features to PostgreSQL. With PostGIS, PostgreSQL can store geographic data such as coordinates, points, routes, and boundaries. It can also answer location-based questions directly inside SQL.

For example, in a bus application, PostGIS can help answer questions like:

Which bus stops are near the user?
How far is this bus stop from Taipei Main Station?
Which stops are within 3 km?
Which stop is closest to the current GPS location?

In this post, we will install PostgreSQL 18 and PostGIS on Ubuntu 24.04, then test PostGIS with a simple bus stop example.

The example is based on a tested Ubuntu 24.04 server where PostgreSQL 18.3, PostGIS 3.6, and the sample spatial queries were verified successfully.

Environment

OS: Ubuntu 24.04
PostgreSQL: 18.3
PostGIS: 3.6
Database name: taiwan_bus
Server hostname: taiwan-bus
  1. Add the PostgreSQL PGDG Repository

Ubuntu 24.04 includes PostgreSQL packages in the default repository, but if you want PostgreSQL 18, use the PostgreSQL Global Development Group repository, usually called PGDG.

Update the package list first:

sudo apt update

Install postgresql-common:

sudo apt install -y postgresql-common

Run the PGDG repository setup script:

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Update the package list again:

sudo apt update
  1. Install PostgreSQL 18

Install PostgreSQL 18 and the PostgreSQL 18 client:

sudo apt install -y postgresql-18 postgresql-client-18

Check the PostgreSQL cluster status:

pg_lsclusters

In this example, PostgreSQL 18 is running on port 5432:

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

If you already had another PostgreSQL version installed, such as PostgreSQL 16, PostgreSQL 18 may use another port. Always check with:

pg_lsclusters
  1. Install PostGIS for PostgreSQL 18

Install the PostGIS package for PostgreSQL 18:

sudo apt install -y postgresql-18-postgis-3 postgresql-18-postgis-3-scripts

The package installation will also install many GIS-related dependencies, such as GEOS, PROJ, GDAL, and SFCGAL libraries.

In this tested environment, the packages included:

postgresql-18-postgis-3
postgresql-18-postgis-3-scripts

The installed PostGIS package version was:

3.6.3+dfsg-1.pgdg24.04+1

After installation, there was no need to restart services:

No services need to be restarted.
No containers need to be restarted.
  1. Create a PostgreSQL Database

Create a database named taiwan_bus:

sudo -u postgres createdb taiwan_bus

Connect to the database:

sudo -u postgres psql -d taiwan_bus

You should enter the PostgreSQL shell:

psql (18.3 (Ubuntu 18.3-1.pgdg24.04+1))
Type "help" for help.

taiwan_bus=#
  1. Enable PostGIS in the Database

PostGIS is enabled per database. Installing the PostGIS package does not automatically enable it in every database.

Inside psql, run:

CREATE EXTENSION IF NOT EXISTS postgis;

Expected output:

CREATE EXTENSION

Verify the PostGIS version:

SELECT PostGIS_Version();

In this tested environment, the output was:

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

This means PostGIS is working.

Exit psql:

\q
  1. Create a Simple Bus Stop Table

Now we will create a simple table to store bus stops.

Connect to the database again:

sudo -u postgres psql -d taiwan_bus

Create the table:

CREATE TABLE bus_stops (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL,
    location GEOGRAPHY(Point, 4326)
);

Expected output:

CREATE TABLE

The important column is:

location GEOGRAPHY(Point, 4326)

This means the column stores a real-world geographic point.

The meaning is:

GEOGRAPHY: A PostGIS type for real-world geographic coordinates
Point: A single point location
4326: The SRID for WGS 84 longitude/latitude coordinates

For GPS data, 4326 is commonly used.

  1. Insert Sample Bus Stops

PostGIS point coordinates use this order:

longitude, latitude

This is important. Do not insert latitude first.

Insert a few sample Taipei locations:

INSERT INTO bus_stops (name, city, location)
VALUES
(
    'Taipei Main Station',
    'Taipei',
    ST_SetSRID(ST_MakePoint(121.5170, 25.0478), 4326)::geography
),
(
    'Taipei 101',
    'Taipei',
    ST_SetSRID(ST_MakePoint(121.5645, 25.0330), 4326)::geography
),
(
    'Ximen Station',
    'Taipei',
    ST_SetSRID(ST_MakePoint(121.5080, 25.0421), 4326)::geography
),
(
    'Songshan Station',
    'Taipei',
    ST_SetSRID(ST_MakePoint(121.5786, 25.0493), 4326)::geography
);

Expected output:

INSERT 0 4

The function used here is:

ST_MakePoint(121.5170, 25.0478)

This creates a point using:

longitude = 121.5170
latitude  = 25.0478

Then this part sets the coordinate system:

ST_SetSRID(..., 4326)

Finally, this part converts the value to the geography type:

::geography
  1. Query the Bus Stop Locations

Run:

SELECT
    id,
    name,
    city,
    ST_AsText(location::geometry) AS location_text
FROM bus_stops;

Expected output:

 id |        name         |  city  |      location_text
----+---------------------+--------+-------------------------
  1 | Taipei Main Station | Taipei | POINT(121.517 25.0478)
  2 | Taipei 101          | Taipei | POINT(121.5645 25.033)
  3 | Ximen Station       | Taipei | POINT(121.508 25.0421)
  4 | Songshan Station    | Taipei | POINT(121.5786 25.0493)
(4 rows)

ST_AsText() converts the PostGIS point into a readable text format.

For example:

POINT(121.517 25.0478)

Again, the order is:

longitude latitude
  1. Calculate Distance from Taipei Main Station

Now calculate the distance from Taipei Main Station to every stop.

Run:

SELECT
    name,
    ROUND(
        ST_Distance(
            location,
            ST_SetSRID(ST_MakePoint(121.5170, 25.0478), 4326)::geography
        )
    ) AS distance_meters
FROM bus_stops
ORDER BY distance_meters;

Expected output:

        name         | distance_meters
---------------------+-----------------
 Taipei Main Station |               0
 Ximen Station       |            1106
 Taipei 101          |            5066
 Songshan Station    |            6218
(4 rows)

This is the first useful PostGIS feature.

Instead of manually calculating distance in application code, PostgreSQL can calculate it directly.

The key function is:

ST_Distance(...)

Because the column uses GEOGRAPHY(Point, 4326), the result is returned in meters.

  1. Find Bus Stops Within 3 km

A common feature in a bus app is:

Show nearby bus stops.

For example, find all bus stops within 3 km of Taipei Main Station:

SELECT
    name,
    city,
    ROUND(
        ST_Distance(
            location,
            ST_SetSRID(ST_MakePoint(121.5170, 25.0478), 4326)::geography
        )
    ) AS distance_meters
FROM bus_stops
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(121.5170, 25.0478), 4326)::geography,
    3000
)
ORDER BY distance_meters;

Expected output:

        name         |  city  | distance_meters
---------------------+--------+-----------------
 Taipei Main Station | Taipei |               0
 Ximen Station       | Taipei |            1106
(2 rows)

This result makes sense.

Only these two stops are within 3 km:

Taipei Main Station
Ximen Station

These two are not returned because they are farther than 3 km:

Taipei 101
Songshan Station

The key function here is:

ST_DWithin(location, target_location, distance_in_meters)

For example:

ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(121.5170, 25.0478), 4326)::geography,
    3000
)

This means:

Find rows where location is within 3000 meters of the target point.
  1. Create a Spatial Index

For a small table with only four rows, an index does not matter.

But for a real bus application, you may have thousands or tens of thousands of stops. You should create a spatial index.

Run:

CREATE INDEX bus_stops_location_idx
ON bus_stops
USING GIST (location);

Expected output:

CREATE INDEX

This creates a GiST index on the location column.

A normal B-tree index is not enough for spatial search. For PostGIS spatial columns, GiST is commonly used.

Did this guide save you time?

Support this site

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top