update strategy about openstreetmap-tile-server

[dottgonzo]

+1 hope to find some time to help you, this is a must

[Istador]

Yes, doing an import (resulting in an osm2pgsql --create) and then another import looses the data of the first import.

 -c|--create
    Remove existing data from the database. This is the default if --append is not specified. 

Adding an append option (resulting in an osm2pgsql --append) to update data is a good idea - but probably not for what you want to do with it.

The --append operation takes very long. Especially with larger PBF files, like on a country or continent level. In my opinion it should only be used to update existing data sets with small files that contain differential updates. The --append also outputs a list of tiles touched, that can be fed into render_expired to delete or re-render updated tiles now or when requested the next time.

---

If you are initially setting up a new tile server that should render more than one region, you are better off merging all PBF files into one PBF file and importing just this one merged file.

osmconvert a.osm.pbf --out-o5m | osmconvert - b.osm.pbf --out-o5m | osmconvert - c.osm.pbf -o=merged.osm.pbf
osm2pgsql --create [...] merged.osm.pbf

If you have to extend an already existing tile server with a new region you probably have to use --append. Note that you don't need to extend the image or update the entrypoint script run.sh to do that. You can call osm2pgsql directly inside the container with docker run --entrypoint=osm2pgsql or docker exec $containerId osm2pgsql.

---

Docs:

• Merging 2 countries with osm2pgsql - OSM Help
• osmconvert
• osm2pgsql
• tile expiration

[Overv]

Yes, but not right now. I'm first focusing on optimizing the performance of tile rendering given a base dataset.

[hugokoopmans]

I would like to extend your code by adding an "append" option, my usecase is to add multiple countries to my tileserver one by one, on an onrequest basis. As I run into issues when importing a new country the data already there seems gone right?

[hugokoopmans]

Thx @Istador for your reply, so I tried the --append option like so:

docker  exec -i -t tileserver /bin/bash
root@ff7da36cc169:/home/renderer/src/openstreetmap-carto# sudo -u renderer osm2pgsql -d gis --append --slim -G --hstore --tag-transform-script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua -C 2048 --number-processes ${THREADS:-4} -S /home/renderer/src/openstreetmap-carto/openstreetmap-carto.style /data.osm.pbf
osm2pgsql version 0.96.0 (64 bit id space)

Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Using projection SRS 3857 (Spherical Mercator)
Setting up table: planet_osm_point
Setting up table: planet_osm_line
Setting up table: planet_osm_polygon
Setting up table: planet_osm_roads
Allocating memory for dense node cache
Allocating dense node cache in one big chunk
Allocating memory for sparse node cache
Sharing dense sparse
Node-cache: cache=2048MB, maxblocks=32768*65536, allocation method=11
Mid: pgsql, cache=2048
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels

Reading in file: /data.osm.pbf
Using PBF parser.
Processing: Node(4757k 1.5k/s) Way(589k 0.76k/s) Relation(7060 294.17/s)  parse time: 4068s
Node stats: total(4757018), max(6304475868) in 3273s
Way stats: total(589564), max(673215891) in 771s
Relation stats: total(7283), max(9354576) in 24s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua

Going over pending ways...
	512039 ways are pending

Using 8 helper-processes
Finished processing 512039 ways in 133 s

512039 Pending ways took 133s at a rate of 3849.92/s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads

Going over pending relations...
565537 relations are pending

Using 8 helper-processes
Finished processing 565537 relations in 8 s

565537 Pending relations took 8s at a rate of 70692.12/s
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Completed planet_osm_point
Completed planet_osm_polygon
Completed planet_osm_roads
node cache: stored: 4757018(100.00%), storage efficiency: 50.79% (dense blocks: 99, sparse nodes: 4277554), hit rate: 99.99%
Completed planet_osm_line
Stopping table: planet_osm_nodes
Stopping table: planet_osm_ways
Stopping table: planet_osm_rels
Stopped table: planet_osm_ways in 0s
Stopped table: planet_osm_rels in 0s
Stopped table: planet_osm_nodes in 0s

Osm2pgsql took 4210s overall

No error but also no aditional tiles ...?

Can i just replace the --create option for the --append option or is there more to it?

[hugokoopmans]

When i try to merge two Dutch provinces, Drenthe and Flevoland I do this:

osmconvert drenthe-latest.osm.pbf --out-o5m | osmconvert - flevoland-latest.osm.pbf --out-o5m -o=merged.osm.pbf

This give me a merged file which seems very big given the original files:

-rwx------ 1 hugo hugo   46898630 Feb 27 01:48 drenthe-latest.osm.pbf*
-rwx------ 1 hugo hugo   25770367 Feb 27 01:48 flevoland-latest.osm.pbf*
-rw------- 1 hugo hugo  157635250 Feb 28 12:10 merged.osm.pbf

Then when importing this file it complains about blobsize ...?

Attaching to tileserver
tileserver    |  * Starting PostgreSQL 10 database server
tileserver    |    ...done.
tileserver    | CREATE EXTENSION
tileserver    | CREATE EXTENSION
tileserver    | ALTER TABLE
tileserver    | ALTER TABLE
tileserver    | osm2pgsql version 0.96.0 (64 bit id space)
tileserver    | 
tileserver    | Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
tileserver    | Using projection SRS 3857 (Spherical Mercator)
tileserver    | Setting up table: planet_osm_point
tileserver    | Setting up table: planet_osm_line
tileserver    | Setting up table: planet_osm_polygon
tileserver    | Setting up table: planet_osm_roads
tileserver    | Allocating memory for dense node cache
tileserver    | Allocating dense node cache in one big chunk
tileserver    | Allocating memory for sparse node cache
tileserver    | Sharing dense sparse
tileserver    | Node-cache: cache=2048MB, maxblocks=32768*65536, allocation method=11
tileserver    | Mid: pgsql, cache=2048
tileserver    | Setting up table: planet_osm_nodes
tileserver    | Setting up table: planet_osm_ways
tileserver    | Setting up table: planet_osm_rels
tileserver    | 
tileserver    | Reading in file: /data.osm.pbf
tileserver    | Using PBF parser.
tileserver    | node cache: stored: 0(-nan%), storage efficiency: -nan% (dense blocks: 0, sparse nodes: 0), hit rate: -nan%
tileserver    | Osm2pgsql failed due to ERROR: PBF error: invalid BlobHeader size (> max_blob_header_size)
tileserver exited with code 0

So what am I doing wrong this time?

[Istador]

Regarding --append:

I assume that /data.osm.pbf inside the container is a new file and not the one used during the initial import?

Can you confirm that the tiles for the region weren't already rendered before the --append operation?
If the rendered tiles already exist before the --append, they need to be deleted, re-rendered or expired (see tile expiration).

---

Regarding merge:

For just two instead of three files the command is:

osmconvert a.osm.pbf --out-o5m | osmconvert - b.osm.pbf -o=merged.osm.pbf

You executed it like this:

osmconvert a.osm.pbf --out-o5m | osmconvert - b.osm.pbf --out-o5m -o=merged.osm.pbf

resulting in a file with an o5m format instead of PBF. Because of the file extension osm2pgsql tries to import the o5m file as an PBF and fails.

The --out-o5m in this command is only used as an intermediate format, not as the output format.

[hugokoopmans]

Thx, osmconvert now works as expected

[stevo01]

The "Minutely Mapnik concept" and switch2osm (chapter update) describes a solution for updates.

A short lock into docker container shows that script "openstreetmap-tiles-update-expire" is available for update procedure (/home/renderer/src/mod_tile/openstreetmap-tiles-update-expire).
I think just the osmosis (>=0.44.1) application is missing in docker container to support the updates.

[stevo01]

The "Minutely Mapnik concept" and switch2osm (chapter update) describes a solution for updates.

A short lock into docker container shows that script "openstreetmap-tiles-update-expire" is available for update procedure (/home/renderer/src/mod_tile/openstreetmap-tiles-update-expire).
I think just the osmosis (>=0.44.1) application is missing in docker container to support the updates.

First test shows that the openstreetmap-tiles-update-expire scripts needs adaptation for usage in docker container.

note: I found another more detailed description of update procedure with recommented update script template here: https://ircama.github.io/osm-carto-tutorials/updating-data/

[Overv]

This is now implemented thanks to @stevo01.