3
Dump OSM data to a postgis database
Guillem Borrell Nogueras edited this page 1 year ago
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