Diff for "PostGIS" - AD Research Wiki

Differences between revisions 3 and 4

For testing purposes we set up up a PostGIS server containing planet.osm.

PostGIS Server

The server runs on atlantis, a pgAdmin frontend is running at http://atlantis.informatik.privat/pgadmin4/. The OSM data is in database osm.

Converting OSM to PostGIS

$/!\$ Edit conflict - other version:

To import planet.osm, we use osm2pgsql. Several things have to considered to import planet.osm without running out of memory or time.

The following command is used to import :

$/!\$ Edit conflict - your version:

To import planet.osm, we use osm2pgsql. Several things have to considered to import planet.osm without running out of memory or time.

The following command is used to import planet.osm:

$/!\$ End of edit conflict

osm2pgsql -c -d osm -C 60000 --slim --flat-nodes /local/data/postgresql/flatnodes /local/data/brosip/planet-latest.osm.pbf

-C 60000 sets the node cache to 60000 MB (don't use more, atlantis has 95 GB of RAM, and osm2pgsql will use additional RAM for other stuff). Lower values will increasingly impact the performance to a point where the import will take several months (this is the case with default parameters).

-c means that the database tables are created if missing

specifies the database to use

--slim will take much longer, but externalizes some stuff to the disk and to the database itself. Without {{--slim}, you will quickly run out of RAM.

--flat-nodes is a file to which parts of the node cache are externalized in --slim mode. This file has to local (e.g. /local/data), the performance will be abysmal over NFS.

After much experimentation, Patrick achieved throughputs of around 5M nodes per second and 82k ways per second.

-  ⇤ ← Revision 3 as of 2020-11-18 14:38:50 → 
  Size: 1499
  Editor: Patrick Brosi
  Comment:
+   ← Revision 4 as of 2020-11-18 14:40:01 → ⇥
  Size: 1899
  Editor: Patrick Brosi
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 9:
+---- /!\ '''Edit conflict - other version:''' ----
-Line 12:
+Line 14:
+---- /!\ '''Edit conflict - your version:''' ----
To import {{{planet.osm}}}, we use {{{osm2pgsql}}}. Several things have to considered to import {{{planet.osm}}} without running out of memory or time.

The following command is used to import {{{planet.osm}}}:

---- /!\ '''End of edit conflict''' ----
-Line 17:
+Line 26:
-{{-C 60000}} sets the node cache to 60000 MB (don't use more, atlantis has 95 GB of RAM, and {{osm2pgsql}} will use additional RAM for other stuff). Lower values will increasingly impact the performance to a point where the import will take several months (this is the case with default parameters).
+{{{-C 60000}}} sets the node cache to 60000 MB (don't use more, atlantis has 95 GB of RAM, and {{{osm2pgsql}}} will use additional RAM for other stuff). Lower values will increasingly impact the performance to a point where the import will take several months (this is the case with default parameters).
-Line 19:
+Line 28:
-{{-c}} means that the database tables are created if missing
+{{{-c}}} means that the database tables are created if missing
-Line 21:
+Line 30:
-{{-d]} specifies the database to use
+{{{-d]}} specifies the database to use
-Line 23:
+Line 32:
-{{--slim}} will take much longer, but externalizes some stuff to the disk and to the database itself. Without {{--slim}, you will quickly run out of RAM.
+{{{--slim}}} will take much longer, but externalizes some stuff to the disk and to the database itself. Without {{--slim}, you will quickly run out of RAM.
-Line 25:
+Line 34:
-{{--flat-nodes}} is a file to which parts of the node cache are externalized. This file has to local (e.g. ``/local/data``), the performance will be abysmal over NFS.
+{{{--flat-nodes}}} is a file to which parts of the node cache are externalized in {{{--slim}}} mode. This file has to local (e.g. {{{/local/data}}}), the performance will be abysmal over NFS.