Osm2pgsql

Well-known "OSM data to PostgreSQL/PostGIS database converter and loader" (Autor: jburgess777(at)googlemail.com). We use osm2pgsql mainly to feed EOSMDBOne which in serves as a database for the PostGIS-Terminal, OpenPOIMap und weitere (z.B. "AR for OSM").

See also:

• OSM-Technisches (Deutsch)
• PostGIS-Terminal, OpenPOIMap
• osmosis, osmium and imposm (and osm2gis).

About

osm2pgsql is mainly written for rendering data with data. So it only imports tags which are going to be useful for rendering. These tags are defined in the file 'default.style' (svn, trac). It also manipulates data for mapnik (i.e. placing nodes in the centre of gravity of polygons).

• osm2pgsql Homepage
• osm2pgsql usage from Volkerschatz

Alternatives

Besides osm2pgsql there's also osmosis, osmium and imposm. osm2pgsql and imposm are geared towards creating useful geometry tables, whereas osmosis and osmium more geared towards truthfully representing a full OSM data set. They all have their advantages and disadvantages. osm2pgsql is the standard choice for most people making maps, with imposm gaining ground. Major difference between them is that osm2pgsql can do updates whereas imposm cannot, that means, it requires you to drop and reimport. imposm probably creates a richer table structure that is easier to understand. And there's also osmconvert together with osmfilter, which are fast (because of C++) in filtering PDF files.

Be advised that any of the tools mentioned will take between one and several days to import a world-wide dataset even on a reasonably fast machine.

Installation

• Install PostgreSQL and PostGIS and create a postgres user with the same name as your system username (the utility explained next connects always with the default user name).
• Create a database and add hstore (and intarray) support.
• Get osm2pgsql
• Get http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/default.style since this is currently left out in the osm2pgsql delivery.
• Download OSM dump - See Planet.osm for mirrors and partial extracts.
• Download OpenJUMP from http://www.openjump.org. Nightly build is recommended.
• run osm2pgsql like this:

> osm2pgsql -d <pg_database> --hstore <osmdumpname.osm>
or
> osm2pgsql -c -d liechtenstein -U postgres -W -H localhost -P 5432 -s -k -x -p osm -S default.style ..\liechtenstein.osm.bz2

• Everything will be automatic after that and the result is ready-to-use PostGIS tables '_point', '_line' and '_polygon' containing OSM data.

Output Database

Original-Dokumentation der Schemata von OSM und Osm2pgsql:

• osm2pgsql schema: Osmpgsql database description (default.style)
• Original OSM schema: pgsnapshot_schema_0.6
• Artikel zum Laden von OSM Daten, "Loading OpenStreetMap data into PostGIS: An Almost Idiot's Guide": [1]

Overview with notes on the Osm2pgsql output schema (default.style):

• Table group 'OSM original'
  • osm_nodes:
  • osm_ways:
  • osm_rels: The member attribute is an array with IDs, where all node members come first, then all way members, then all relation members, and way_off is the index of the first way member and rel_off the index of the first relation member.
• Table group 'Osm2pgsql':
  • osm_point: with point geometry
  • osm_line: with polyline geometry
  • osm_polygon: with polygon geometry (one of the unique features of Osm2pgsql!)
  • osm_roads: a special table with polyline geometry

Style File

Style File (Default.style):

 -x ...
 node,way osm_version int linear 
 oder
 node,way osm_version int4 linear

Tipps & Tricks

Append mode

If you want to add a new key but avoid re-importing the full database, you can add the key to osm2pgsql's style file, somehow generate an .osm file that contains only the objects that have this tag, wrap this .osm file into a "<osmChange><modify>...</modify></osmChange>" instead of "<osm>...</osm>" and throw it at osm2pgsql in append mode.

Differential Update

The problem is that; despite the *osm2pgsql -b* option, the bounding box is ignored and the full diff file is added to the database;

Possible solution:

1. Make a copy of the extract.
2. Apply the update to the copy (using osmosis)
3. Clip the copy using the Vietnam clip bounds file (using osmosis?)
4. Make a diff between the original and the copy (using osmosis?)
5. Apply the diff to the PostGIS database (osm2pgsql)
6. Finally make the copy the new original for next update