Osm: Difference between revisions
m (cosmetic changes) |
m (cosmetic changes) |
||
| Line 6: | Line 6: | ||
==== How to import planet.osm.pbf ==== | ==== How to import planet.osm.pbf ==== | ||
The import process may overuse memory and | The import process may overuse memory and crash the server. To mitigate this, the following was used (though only as long as nodes+ways+rels were processed until <code>2022-04-05 08:35</code>, not at the database operations)<syntaxhighlight lang="shell"> | ||
$ while true;do while [ "$(free --giga|grep Mem|xargs|cut -d' ' -f7)" -gt 2 ];do printf '\033[2K\r%s' "$(free --giga|grep Mem|xargs|cut -d' ' -f7)";sleep 1;done;sudo kill 835;free -h;sleep 5;free -h;done | |||
< | </syntaxhighlight>It seemed that limiting CPU usage helped mitigate RAM overuse: | ||
It seemed that limiting CPU usage helped mitigate RAM overuse : | |||
<syntaxhighlight lang="shell-session"> | <syntaxhighlight lang="shell-session"> | ||
$ cpulimit -fl 400 -- osm2pgsql --create --multi-geometry --slim --hstore --style openstreetmap-with-more.style --tag-transform-script openstreetmap-adjusted-for-cyclosm.lua -d gis -C 14000 --number-processes 4 --flat-nodes /mnt/maps/planet.bin.nodes /mnt/maps/planet-220328.osm.pbf | $ cpulimit -fl 400 -- osm2pgsql --create --multi-geometry --slim --hstore --style openstreetmap-with-more.style --tag-transform-script openstreetmap-adjusted-for-cyclosm.lua -d gis -C 14000 --number-processes 4 --flat-nodes /mnt/maps/planet.bin.nodes /mnt/maps/planet-220328.osm.pbf | ||
Process 835 detected | Process 835 detected | ||
2022-04-04 12:16:47 osm2pgsql version 1.5.2 (1.5.2-15-g25a1e9d1) | 2022-04-04 12:16:47 osm2pgsql version 1.5.2 (1.5.2-15-g25a1e9d1) | ||
| Line 55: | Line 52: | ||
Layers /plain*/ and /cyclosm/ differ on their assumptions of the database schema. These assumptions are specified at import time to osm2pgsql. The details about which columns are extracted from <code>planet.osm.pbf</code> and how they are converted, are specified by <code>--style</code> and <code>--tag-transform-script</code>, the current files are: <code>[osm2pgsql] --style openstreetmap-with-more.style --tag-transform-script openstreetmap-adjusted-for-cyclosm.lua</code>. The <code>.style</code> file specifies which columns to take into the database. To unite both layers, it changes some columns from the /plain*/ layers' <code>.style</code> to apply not only to <code>nodes</code> but <code>nodes,ways</code> and adds a few new columns specific to <code>/cyclosm/</code>. There was one conflict: the <code>layer</code> column, regarding its type. The cyclosm <code>mapnik.xml</code> stylesheet only mentions the column four times and assumes it is <code>text</code>, whereas /plain*/ takes it as <code>int4</code>. Cyclosm converts layer to integer anyway, so only a few manual edits in its stylesheet and <code>views.sql</code> (the SQL indexes builder) were necessary: | Layers /plain*/ and /cyclosm/ differ on their assumptions of the database schema. These assumptions are specified at import time to osm2pgsql. The details about which columns are extracted from <code>planet.osm.pbf</code> and how they are converted, are specified by <code>--style</code> and <code>--tag-transform-script</code>, the current files are: <code>[osm2pgsql] --style openstreetmap-with-more.style --tag-transform-script openstreetmap-adjusted-for-cyclosm.lua</code>. The <code>.style</code> file specifies which columns to take into the database. To unite both layers, it changes some columns from the /plain*/ layers' <code>.style</code> to apply not only to <code>nodes</code> but <code>nodes,ways</code> and adds a few new columns specific to <code>/cyclosm/</code>. There was one conflict: the <code>layer</code> column, regarding its type. The cyclosm <code>mapnik.xml</code> stylesheet only mentions the column four times and assumes it is <code>text</code>, whereas /plain*/ takes it as <code>int4</code>. Cyclosm converts layer to integer anyway, so only a few manual edits in its stylesheet and <code>views.sql</code> (the SQL indexes builder) were necessary: | ||
(stylesheet.xml) <code>CASE WHEN layer~E'^\\d+$' THEN layer::integer ELSE 0 END</code> | (cyclosm stylesheet.xml) <code>CASE WHEN layer~E'^\\d+$' THEN layer::integer ELSE 0 END</code> | ||
replaced by <code>CASE WHEN layer IS NOT NULL THEN layer ELSE 0 END</code> and | replaced by <code>CASE WHEN layer IS NOT NULL THEN layer ELSE 0 END</code> and | ||
Revision as of 16:37, 6 April 2022
LXC container
Cartography pipeline
postgresql
Data storage, mostly in planet_osm_* tables, but some smaller tables for e.g. coastlines in dbname=gis
How to import planet.osm.pbf
The import process may overuse memory and crash the server. To mitigate this, the following was used (though only as long as nodes+ways+rels were processed until 2022-04-05 08:35, not at the database operations)
$ while true;do while [ "$(free --giga|grep Mem|xargs|cut -d' ' -f7)" -gt 2 ];do printf '\033[2K\r%s' "$(free --giga|grep Mem|xargs|cut -d' ' -f7)";sleep 1;done;sudo kill 835;free -h;sleep 5;free -h;doneIt seemed that limiting CPU usage helped mitigate RAM overuse:
$ cpulimit -fl 400 -- osm2pgsql --create --multi-geometry --slim --hstore --style openstreetmap-with-more.style --tag-transform-script openstreetmap-adjusted-for-cyclosm.lua -d gis -C 14000 --number-processes 4 --flat-nodes /mnt/maps/planet.bin.nodes /mnt/maps/planet-220328.osm.pbf
Process 835 detected
2022-04-04 12:16:47 osm2pgsql version 1.5.2 (1.5.2-15-g25a1e9d1)
2022-04-04 12:16:47 Database version: 12.10 (Ubuntu 12.10-1.pgdg20.04+1)
2022-04-04 12:16:47 PostGIS version: 3.2
2022-04-04 12:16:47 Setting up table 'planet_osm_point'
2022-04-04 12:16:47 Setting up table 'planet_osm_line'
2022-04-04 12:16:47 Setting up table 'planet_osm_polygon'
2022-04-04 12:16:48 Setting up table 'planet_osm_roads'
Processing: Node(1640k 546.7k/s) Way(0k 0.00k/s) Relation(0 0.0/s)
Processing: Node(1002480k 1842.8k/s) Way(0k 0.00k/s) Relation(0 0.0/s)
Processing: Node(4575560k 1902.5k/s) Way(0k 0.00k/s) Relation(0 0.0/s)
Processing: Node(7587737k 1861.6k/s) Way(91455k 10.28k/s) Relation(0 0.0/s)
Processing: Node(7587737k 1861.6k/s) Way(262832k 13.34k/s) Relation(0 0.0/s)
Processing: Node(7587737k 1861.6k/s) Way(847173k 18.33k/s) Relation(1786720 216.9/s)
2022-04-05 08:35:27 Reading input files done in 73119s (20h 18m 39s).
2022-04-05 08:35:27 Processed 7587737772 nodes in 4076s (1h 7m 56s) - 1862k/s
2022-04-05 08:35:27 Processed 847173340 ways in 46225s (12h 50m 25s) - 18k/s
2022-04-05 08:35:27 Processed 9773277 relations in 22818s (6h 20m 18s) - 428/s
2022-04-05 08:35:28 Clustering table 'planet_osm_point' by geometry...
2022-04-05 08:35:28 Clustering table 'planet_osm_polygon' by geometry...
2022-04-05 08:35:28 Clustering table 'planet_osm_line' by geometry...
2022-04-05 08:35:28 Clustering table 'planet_osm_roads' by geometry...
2022-04-05 10:22:51 Creating geometry index on table 'planet_osm_roads'...
2022-04-05 10:50:28 Creating osm_id index on table 'planet_osm_roads'...
2022-04-05 10:56:22 Creating geometry index on table 'planet_osm_point'...
2022-04-05 10:58:21 Analyzing table 'planet_osm_roads'...
2022-04-05 10:58:34 Done postprocessing on table 'planet_osm_nodes' in 0s
2022-04-05 10:58:34 Building index on table 'planet_osm_ways'
2022-04-05 13:38:32 Creating osm_id index on table 'planet_osm_point'...
2022-04-05 13:55:29 Analyzing table 'planet_osm_point'...
2022-04-05 13:55:45 Building index on table 'planet_osm_rels'
2022-04-05 15:38:19 Creating geometry index on table 'planet_osm_line'...
2022-04-05 21:40:06 Creating osm_id index on table 'planet_osm_line'...
2022-04-05 22:12:14 Analyzing table 'planet_osm_line'...
2022-04-06 00:58:09 Creating geometry index on table 'planet_osm_polygon'...Reconciliation of database schemas for rendering of two layer types
Layers /plain*/ and /cyclosm/ differ on their assumptions of the database schema. These assumptions are specified at import time to osm2pgsql. The details about which columns are extracted from planet.osm.pbf and how they are converted, are specified by --style and --tag-transform-script, the current files are: [osm2pgsql] --style openstreetmap-with-more.style --tag-transform-script openstreetmap-adjusted-for-cyclosm.lua. The .style file specifies which columns to take into the database. To unite both layers, it changes some columns from the /plain*/ layers' .style to apply not only to nodes but nodes,ways and adds a few new columns specific to /cyclosm/. There was one conflict: the layer column, regarding its type. The cyclosm mapnik.xml stylesheet only mentions the column four times and assumes it is text, whereas /plain*/ takes it as int4. Cyclosm converts layer to integer anyway, so only a few manual edits in its stylesheet and views.sql (the SQL indexes builder) were necessary:
(cyclosm stylesheet.xml) CASE WHEN layer~E'^\\d+$' THEN layer::integer ELSE 0 END
replaced by CASE WHEN layer IS NOT NULL THEN layer ELSE 0 END and
(views.sql) CASE WHEN layer~E'^\\d+$' THEN 100*layer::integer+199 ELSE 199 END
replaced by CASE WHEN layer IS NOT NULL THEN 100*layer+199 ELSE 199 END.
renderd
Rendering daemon
Layer /plain/
Standard OSM map layer. The stylesheet is generated by cartocss and located at ~/src/openstreetmap-carto/mapnik_plain.xml
Layer /plain_overlay/
Modified /plain/ layer to change colours of aerial elements to transparent to make laying over a different base layer, satellite imagery for example. The Stylesheet is generated by $ python3 add_more_to_overlay.py [mapnik_plain.xml], where the python script only replaces a few of the <PolygonSymbolizer> fill colour attributes by fill="transparent". This works well, also supported by the fact that the rendered raster tiles are in .png format which supports transparent colour.
Layer /cyclosm/
Cycling-optimized map layer, the database schema is widened to contain all necessary data for both /plain*/ and /cyclosm/, see Reconciliation_of_database_schemas_for_rendering_of_two_layer_types. The stylesheet is generated in the working directory ~/src where the node module kosmtik is available, though only executable with a statically installed lts-version nodejs in /root/(which has been made other-readable as a consequence) :
$ export PATH="/root/node-v16.14.1-linux-x64/bin:$PATH"
$ node_modules/kosmtik/index.js export cyclosm-cartocss-style/project.mml --output cyclosm-cartocss-style/mapnik.xmlapache2
Requests rendering daemon if tiles not present in cache and serves them on port 80
Container rights
- IP
10.0.3.10 - Internet access for periodically downloading updates (changefiles), restricted from inside with no default route
- read-write to
/mnt/maps/(planet.bin.nodes)for database imports and as temporary storage (/mnt/maps/tmp) of changefiles - read-write to
/var/lib/postgresql/to keep the database on a separate zfs dataset
OpenStreetmap container that contains a postgresql database of the planet as cartography, the renderd daemon and an apache2 webserver to serve rendered vector tiles.
