Steps

Install, upgrade and start a modern Postgresql cluster

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt install postgresql-14 postgresql-14-postgis-3 osm2pgsql
sudo pg_createcluster 14 postgis
sudo systemctl enable postgresql@14-postgis.service
sudo systemctl start postgresql@14-postgis

After running the commands, you should get...

guillem@EURMP1519550L:~$ sudo systemctl status postgresql@14-postgis
● postgresql@14-postgis.service - PostgreSQL Cluster 14-postgis
   Loaded: loaded (/lib/systemd/system/postgresql@.service; indirect; vendor preset: enabled)
   Active: active (running) since Tue 2022-09-13 08:51:15 UTC; 3min 24s ago
  Process: 7453 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 14-postgis start (code=exited, status=0/SUCCESS)
 Main PID: 7461 (postgres)
    Tasks: 7 (limit: 4915)
   CGroup: /system.slice/system-postgresql.slice/postgresql@14-postgis.service
           ├─7461 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/postgis -c config_file=/etc/postgresql/14/postgis/postgresql.conf
           ├─7466 postgres: 14/postgis: checkpointer
           ├─7467 postgres: 14/postgis: background writer
           ├─7468 postgres: 14/postgis: walwriter
           ├─7469 postgres: 14/postgis: autovacuum launcher
           ├─7470 postgres: 14/postgis: stats collector
           └─7471 postgres: 14/postgis: logical replication launcher

Sep 13 08:51:10 EURMP1519550L systemd[1]: Starting PostgreSQL Cluster 14-postgis...
Sep 13 08:51:15 EURMP1519550L systemd[1]: Started PostgreSQL Cluster 14-postgis.

Check the port where Postgres is running by finding the socket

guillem@EURMP1519550L:~$ ls /var/run/postgresql/
10-main.pg_stat_tmp/    12-main.pg_stat_tmp/    14-main.pg_stat_tmp/    14-postgis.pg_stat_tmp/ 14-postgis.pid          .s.PGSQL.5435           .s.PGSQL.5435.lock

Enter the database with:

guillem@EURMP1519550L:~$ sudo -u postgres psql -p 5435
psql (14.5 (Ubuntu 14.5-1.pgdg18.04+1))
Type "help" for help.

postgres=#

Install postgresql extensions

guillem@EURMP1519550L:~$ sudo -u postgres psql -p 5435
psql (14.5 (Ubuntu 14.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# create database pgsnapshot;
CREATE DATABASE
postgres=# \c pgsnapshot
You are now connected to database "pgsnapshot" as user "postgres".
pgsnapshot=# create extension postgis;
CREATE EXTENSION
pgsnapshot=# create extension hstore;
CREATE EXTENSION
pgsnapshot=# \dx
                                List of installed extensions
  Name   | Version |   Schema   |                        Description
---------+---------+------------+------------------------------------------------------------
 hstore  | 1.8     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 3.2.1   | public     | PostGIS geometry and geography spatial types and functions
(3 rows)

pgsnapshot=# \password postgres
Enter new password for user "postgres":
Enter it again:

Download and load OSM data

Download the data dump from https://download.openstreetmap.fr/

wget https://download.openstreetmap.fr/extracts/north-america/mexico-latest.osm.pbf

sudo -u postgres osm2pgsql --create --database pgsnapshot --cache 20000 --number-processes 2 --slim -P 5435 --keep-coastlines --extra-attributes --hstore-all mexico-latest.osm.pbf

Then change the postgresql.conf and pg_hba.conf files to allow remote connections to the database.

Interesting Postgis features

You can compute minimum distances with KNN as follows:

select
	st_distance(st_transform(st_setsrid(st_point(-120, 12), 4326), 3857), way) as distance
from
	public.planet_osm_roads
where
	highway in ('motorway', 'trunk', 'primary', 'secondary')
order by
	st_transform(st_setsrid(st_point(-120, 12), 4326), 3857) <-> way
limit
	1

Index

Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International