PostGIS and OSM on Debian

Install PostgreSQL and PostGIS

sudo apt install postgresql

sudo apt install postgis

Get OSM Data

Get data in pbf-format of interesting region from this site:

https://extract.bbbike.org/

https://protomaps.com/

Import OSM Data to PostGIS

Get and Install osm2pqsql util:

sudo apt install osm2pgsql

Prepare:

sudo su postgres
createuser --no-superuser --no-createrole --createdb osm
createdb -E UTF8 -O osm osm
psql -d osm -c "CREATE EXTENSION postgis;"
psql -d osm -c "CREATE EXTENSION hstore;" # only required for hstore support
echo "ALTER USER osm WITH PASSWORD 'osm';" |psql -d osm

Make import:

osm2pgsql -c -d osm -U osm -W -H localhost -P 5432 -S ./default.style -E 3857 osm_mosreg_202204.pbf

or (slim mode with cache 2000Mb of RAM):

osm2pgsql -c -d osm -U osm -W -H localhost -P 5432 -s --cache=2000 -S ./default.style -E 3857 osm_mosreg_202204.pbf

Connect Geoserver and Postgis

//blog.techsample.ru/images/image/postgres_storage222415986.png

Simple Styling (OSM Toner)

Add lines and polygons:

planet_osm_line
planet_osm_polygon

//blog.techsample.ru/images/image/postgres_linesandpolys222650440.png

SLD-styles:

Line //blog.techsample.ru/storage/file/SIYKPjGvJ22XtqXdkuUrjXMisNI

Polygon //blog.techsample.ru/storage/file/GbwvEBaEQEbXXvx6DVqVY3gzEA

Tune PG

sudo mcedit /etc/postgresql/13/main/postgresql.conf

shared_buffers = 1GB
work_mem = 50MB
maintenance_work_mem = 10GB
autovacuum_work_mem = 2GB
wal_level = minimal
checkpoint_timeout = 60min
max_wal_size = 10GB
checkpoint_completion_target = 0.9
max_wal_senders = 0
random_page_cost = 1.0

sudo service postgresql restart

Connect to client

//gis.techsample.ru/geoserver/gwc/service/wmts/rest/ODH_space:osm_toner/generic/EPSG:3857/EPSG:3857:{z}/{y}/{x}?format=image/png