pgRouting 4.0.2 with PostGIS

Option A: AWS Marketplace console

• Open the SecureAnyCloud Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace. Choose View purchase options, then Subscribe. One subscription covers every version in the listing.
• Once the subscription is active, choose Launch your software.
• Choose One-click launch from AWS Marketplace (or Launch from EC2 Console for the full launch wizard). In Version, choose pgRouting 4.0.2 with PostGIS on Ubuntu 24.04, then choose your Region.
• Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
• Set the security group. The listing's default group allows SSH (22) only. Restrict it to your own IP address, and do not open 5432 to the internet.
• Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run the following on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page. Replace the VPC, subnet, key pair and CIDR with your own values:

SG=$(aws ec2 create-security-group --group-name pgrouting \
  --description "pgRouting" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=pgrouting}]'

You do not need --metadata-options, because the AMI already makes IMDSv2 required. To give the database more room, add a --block-device-mappings entry for /dev/sda1 with a bigger VolumeSize.

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. Show the connection details. This command leaves out the two password lines. Read them with sudo cat /root/pgrouting-credentials.txt and keep them in your secrets manager:

sudo grep -v -e '^POSTGRES_SUPERUSER_PASSWORD=' -e '^ROUTINGAPP_PASSWORD=' /root/pgrouting-credentials.txt | head -12

# pgRouting 4.0.2 with PostGIS 3.6.4 on PostgreSQL 18.6 (sac AWS Marketplace image)
# Generated on THIS instance's first boot by pgrouting-firstboot.service; these passwords exist # nowhere else. Rotate a password (never on a command line) with:
sudo -u postgres psql
# then \password routingapp and update this file to match (pgrouting-credguard reads it at boot).
# Routing database 'routingdb' (owner routingapp; postgis 3.6.4 + pgrouting 4.0.2
# enabled; demonstration tables routing_edges + routing_vertices) on 127.0.0.1:5432. Connect locally with TLS:
# psql "host=127.0.0.1 dbname=routingdb user=routingapp sslmode=require"
# Private address of this instance: 172.31.81.209
#
# NO ROAD DATA SHIPS: the demonstration network is a small synthetic lattice. Load your own network

The commands that follow read the routingapp password from that file at run time, so it never appears on screen. In your own sessions, you can instead put the password in ~/.pgpass (mode 0600) as 127.0.0.1:5432:routingdb:routingapp:<ROUTINGAPP_PASSWORD>.

Check the PostgreSQL version, the service states and the listening ports:

psql --version systemctl is-active postgresql@18-main.service pgrouting-firstboot.service pgrouting-credguard.service
sudo ss -Hltn

psql (PostgreSQL) 18.6 (Ubuntu 18.6-1.pgdg24.04+2) active active active LISTEN 0 4096 0.0.0.0:22 0.0.0.0:* LISTEN 0 4096 127.0.0.54:53 0.0.0.0:* LISTEN 0 200 127.0.0.1:5432 0.0.0.0:* LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:* LISTEN 0 4096 [::]:22 [::]:*

PostgreSQL listens on 127.0.0.1:5432 only. Port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub.

Now connect as routingapp over TLS, list the extensions in routingdb and show the versions:

export PGPASSWORD="$(sudo sed -n 's/^ROUTINGAPP_PASSWORD=//p' /root/pgrouting-credentials.txt)" psql "host=127.0.0.1 dbname=routingdb user=routingapp sslmode=require" \
  -c "\dx" \
  -c "SELECT pgr_version() AS pgrouting, postgis_lib_version() AS postgis, version() ~ '^PostgreSQL 18\.6' AS postgresql_18_6;" \
  -c "SELECT ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

List of installed extensions Name | Version | Default version | Schema | Description -----------+---------+-----------------+------------+------------------------------------------------------------ pgrouting | 4.0.2 | 4.0.2 | public | pgRouting Extension plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language postgis | 3.6.4 | 3.6.4 | public | PostGIS geometry and geography spatial types and functions (3 rows) pgrouting | postgis | postgresql_18_6 -----------+---------+----------------- 4.0.2 | 3.6.4 | t (1 row) ssl | tls_version -----+------------- t | TLSv1.3 (1 row)

First boot loads a small synthetic network into routingdb, so you can try routing straight away. It is a 6 x 6 grid of junctions in south-west London (60 streets), plus a separate three-junction "island" about 40 km away that is deliberately not connected to the grid.

Each street is a PostGIS LINESTRING in EPSG:4326 whose cost is its real length in metres. The routing_vertices table holds the junctions, built from the street geometry with pgr_extractVertices. The SQL that builds the network is /usr/local/share/sac/pgrouting-demo-network.sql. Run the following to summarise it:

export PGPASSWORD="$(sudo sed -n 's/^ROUTINGAPP_PASSWORD=//p' /root/pgrouting-credentials.txt)" psql "host=127.0.0.1 dbname=routingdb user=routingapp sslmode=require" -v ON_ERROR_STOP=1 <<'SQL' SELECT kind, count(*) AS streets, round(sum(cost)::numeric) AS total_metres FROM routing_edges GROUP BY kind ORDER BY kind; SELECT id, name, source, target, round(cost::numeric, 1) AS metres, ST_AsText(geom) AS wkt FROM routing_edges ORDER BY id LIMIT 3; SELECT count(*) AS junctions FROM routing_vertices; SELECT component, count(*) AS junctions FROM pgr_connectedComponents('SELECT id, source, target, cost, reverse_cost FROM routing_edges') GROUP BY component ORDER BY junctions DESC; SQL

kind | streets | total_metres ---------+---------+-------------- island | 2 | 1380 lattice | 60 | 54221 (2 rows) id | name | source | target | metres | wkt ----+----------+--------+--------+--------+------------------------------------ 1 | ew-c0-r0 | 4 | 10 | 695.2 | LINESTRING(-0.2 51.45,-0.19 51.45) 2 | ew-c0-r1 | 5 | 11 | 695.0 | LINESTRING(-0.2 51.46,-0.19 51.46) 3 | ew-c0-r2 | 6 | 12 | 694.9 | LINESTRING(-0.2 51.47,-0.19 51.47) (3 rows) junctions ----------- 39 (1 row) component | junctions -----------+----------- 4 | 36 1 | 3 (2 rows)

pgr_connectedComponents confirms two separate pieces: the 36-junction grid and the 3-junction island.

Pick the junctions nearest two points. Here they are the south-west and north-east corners of the grid, found with the PostGIS <-> distance operator. Ask pgr_dijkstra for the shortest route between them.

Then check that two other algorithms find a route of the same length: pgr_aStar (which uses the street coordinates as a heuristic) and pgr_bellmanFord.

export PGPASSWORD="$(sudo sed -n 's/^ROUTINGAPP_PASSWORD=//p' /root/pgrouting-credentials.txt)" psql "host=127.0.0.1 dbname=routingdb user=routingapp sslmode=require" -v ON_ERROR_STOP=1 <<'SQL' -- The junctions nearest two points: the south-west and north-east corners of the lattice CREATE TEMP VIEW ends AS SELECT (SELECT id FROM routing_vertices ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-0.20, 51.45), 4326) LIMIT 1) AS a, (SELECT id FROM routing_vertices ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-0.15, 51.50), 4326) LIMIT 1) AS b; -- Shortest path, street by street, with the running distance in metres SELECT d.seq, d.node, e.name AS street, round(d.agg_cost::numeric, 1) AS metres_so_far FROM ends, pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', ends.a, ends.b) d LEFT JOIN routing_edges e ON e.id = d.edge ORDER BY d.seq; -- Three independent algorithms must agree on the length of the best route SELECT 'pgr_dijkstra' AS algorithm, round(max(agg_cost)::numeric, 1) AS metres FROM ends, pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', ends.a, ends.b) UNION ALL SELECT 'pgr_aStar', round(max(agg_cost)::numeric, 1) FROM ends, pgr_aStar('SELECT id, source, target, cost, reverse_cost, ST_X(ST_StartPoint(geom)) AS x1, ST_Y(ST_StartPoint(geom)) AS y1, ST_X(ST_EndPoint(geom)) AS x2, ST_Y(ST_EndPoint(geom)) AS y2 FROM routing_edges', ends.a, ends.b) UNION ALL SELECT 'pgr_bellmanFord', round(max(agg_cost)::numeric, 1) FROM ends, pgr_bellmanFord('SELECT id, source, target, cost, reverse_cost FROM routing_edges', ends.a, ends.b); SQL

CREATE VIEW seq | node | street | metres_so_far -----+------+----------+--------------- 1 | 4 | ns-c0-r0 | 0.0 2 | 5 | ns-c0-r1 | 1112.6 3 | 6 | ns-c0-r2 | 2225.1 4 | 7 | ns-c0-r3 | 3337.7 5 | 8 | ns-c0-r4 | 4450.3 6 | 9 | ew-c0-r5 | 5562.9 7 | 15 | ew-c1-r5 | 6257.3 8 | 21 | ew-c2-r5 | 6951.7 9 | 27 | ew-c3-r5 | 7646.1 10 | 33 | ew-c4-r5 | 8340.5 11 | 39 | | 9034.9 (11 rows) algorithm | metres -----------------+-------- pgr_dijkstra | 9034.9 pgr_aStar | 9034.9 pgr_bellmanFord | 9034.9 (3 rows)

On a grid, many routes have the same length, and any of them is a correct answer. You can also return the route as one PostGIS geometry, ready for a web map (GeoJSON) or for further spatial queries:

export PGPASSWORD="$(sudo sed -n 's/^ROUTINGAPP_PASSWORD=//p' /root/pgrouting-credentials.txt)" psql "host=127.0.0.1 dbname=routingdb user=routingapp sslmode=require" -v ON_ERROR_STOP=1 <<'SQL' -- The route as ONE geometry, ready for a web map (GeoJSON) or another PostGIS query WITH ends AS ( SELECT (SELECT id FROM routing_vertices ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-0.20, 51.45), 4326) LIMIT 1) AS a, (SELECT id FROM routing_vertices ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-0.15, 51.50), 4326) LIMIT 1) AS b), route AS ( SELECT ST_LineMerge(ST_Union(e.geom)) AS geom FROM ends, pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', ends.a, ends.b) d JOIN routing_edges e ON e.id = d.edge) SELECT ST_GeometryType(geom) AS type, ST_NumPoints(geom) AS points, round(ST_Length(geom::geography)::numeric) AS metres, left(ST_AsGeoJSON(geom, 4), 70) || '...' AS geojson FROM route; SQL

type | points | metres | geojson ---------------+--------+--------+--------------------------------------------------------------------------- ST_LineString | 11 | 9035 | {"type":"LineString","coordinates":[[-0.2,51.45],[-0.2,51.46],[-0.2,51... (1 row)

The following script answers three common questions on the same network:

• A road closure. Inside a transaction, give one street a negative cost (pgRouting skips edges with a negative cost). Compare the route before and after, then roll the change back so the table is untouched.
• A service area. pgr_drivingDistance returns every junction reachable within a distance (here 1,000 m of the centre junction).
• No route. Routing to the island returns an empty result, not an error and not an invented route.

export PGPASSWORD="$(sudo sed -n 's/^ROUTINGAPP_PASSWORD=//p' /root/pgrouting-credentials.txt)" psql "host=127.0.0.1 dbname=routingdb user=routingapp sslmode=require" -v ON_ERROR_STOP=1 <<'SQL' \set edges 'SELECT id, source, target, cost, reverse_cost FROM routing_edges' CREATE TEMP VIEW pts AS SELECT (SELECT id FROM routing_vertices ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-0.20, 51.45), 4326) LIMIT 1) AS sw, (SELECT id FROM routing_vertices ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-0.15, 51.45), 4326) LIMIT 1) AS se, (SELECT id FROM routing_vertices ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-0.175, 51.475), 4326) LIMIT 1) AS mid, (SELECT id FROM routing_vertices ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-0.50, 51.80), 4326) LIMIT 1) AS island; -- Close a street (a road closure) inside a transaction, compare the routes, then roll the closure back BEGIN; SELECT 'open' AS street_ew_c2_r0, round(max(agg_cost)::numeric, 1) AS metres FROM pts, pgr_dijkstra(:'edges', pts.sw, pts.se); UPDATE routing_edges SET cost = -1, reverse_cost = -1 WHERE name = 'ew-c2-r0'; SELECT 'closed' AS street_ew_c2_r0, round(max(agg_cost)::numeric, 1) AS metres FROM pts, pgr_dijkstra(:'edges', pts.sw, pts.se); ROLLBACK; -- Everything reachable within 1,000 m of the centre junction (a service area) SELECT count(*) AS junctions_within_1000m FROM pts, pgr_drivingDistance(:'edges', pts.mid, 1000); -- The island is not connected to the lattice: no route is an empty result, not an error SELECT count(*) AS route_rows_to_island FROM pts, pgr_dijkstra(:'edges', pts.sw, pts.island); SQL

CREATE VIEW BEGIN street_ew_c2_r0 | metres -----------------+-------- open | 3475.8 (1 row) UPDATE 1 street_ew_c2_r0 | metres -----------------+-------- closed | 5700.2 (1 row) ROLLBACK junctions_within_1000m ------------------------ 3 (1 row) route_rows_to_island ---------------------- 0 (1 row)

With the street closed, the route detours one block north and grows from 3,475.8 m to 5,700.2 m. The east-west streets are about 695 m and the north-south streets about 1,113 m, so only the centre junction and its two east-west neighbours are within 1,000 m.

Loading your own network

Replace the demonstration tables with your own network when you are ready. pgRouting needs an edge table with id, source, target and cost columns, and optionally reverse_cost. With PostGIS geometry, you can build source and target exactly as the demonstration does, with pgr_extractVertices. Common data sources include:

• OpenStreetMap. Load an extract (for example from Geofabrik) with osm2pgrouting or osm2pgsql. Run the loader on your workstation or a separate loader instance, through an SSH tunnel to this database. OpenStreetMap data is licensed under the ODbL, and you manage its licence and currency.
• Your own GIS data. Load a shapefile or GeoPackage with ogr2ogr (GDAL) or QGIS over the same tunnel.

ogr2ogr -f PostgreSQL "PG:host=127.0.0.1 dbname=routingdb user=routingapp sslmode=require" roads.gpkg -nln roads -lco GEOMETRY_NAME=geom

To drop the demonstration network: DROP TABLE routing_edges, routing_vertices; as routingapp.

Run these checks to confirm that the PostgreSQL unit carries the metadata block, routingapp is not a superuser, PostgreSQL listens on loopback with this instance's own certificate, and the fwupd refresh timer is masked:

systemctl show -p IPAddressDeny postgresql@18-main.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper FROM pg_roles WHERE rolname IN ('routingapp', 'postgres') ORDER BY 1;"
sudo -u postgres psql -tAc "SHOW listen_addresses;"
sudo -u postgres psql -tAc "SHOW ssl_cert_file;"
systemctl is-enabled fwupd-refresh.timer 2>/dev/null || true

IPAddressDeny=169.254.169.254/32 fd00:ec2::254/128 168.63.129.16/32 postgres|t routingapp|f 127.0.0.1 /etc/postgresql/18/main/sac-server.crt masked

PostgreSQL accepts connections on localhost only. You have two ways to reach it:

• SSH tunnel (no change to the instance). On your workstation, run ssh -N -L 5432:127.0.0.1:5432 ubuntu@<public-ip>. Then connect your client to 127.0.0.1:5432 as routingapp with sslmode=require.
• Open it to your VPC. In /etc/postgresql/18/main/conf.d/10-sac.conf, change listen_addresses = '127.0.0.1' to listen_addresses = '*' and restart PostgreSQL. Then add an inbound rule for 5432 to the instance's security group, from your application subnet or VPC CIDR only (never 0.0.0.0/0). Remote clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/postgresql/18/main/sac-server.crt) and the role's password:

sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/18/main/conf.d/10-sac.conf
sudo systemctl restart postgresql@18-main.service

pg_hba.conf refuses a plain-text (non-TLS) connection from another host, and scram-sha-256 refuses a wrong password:

OUT="$(PGPASSWORD=<set-at-first-boot> psql "host=127.0.0.1 dbname=routingdb user=routingapp sslmode=require" -c "SELECT 1" 2>&1 || true)" echo "${OUT#*failed: }"

FATAL: password authentication failed for user "routingapp"