Home

The osmhistorydb-ch Wiki

This is the repository of the osmhistorydb-ch which aims at delivering a database of all OSM elements of Switzerland including all versions (history) of the elements. It's implemented using a PostgreSQL database of OSM data in slightly enhanced OSM API schema (Nodes, Ways, Relations plus Users) and it's updated periodically (e.g. hourly).

The main use case of osmhistorydb-ch is another project, called "Project of the Month", which visualised statistics of selected objects within the timeframe of related actions. Other uses cases are to experiment as database backbone to

• Monitoring similar to OSMCha
• 'History renderers' (like osm-history-renderer (currently unmaintained)
• 'Permanent ID' services
• Data analysis e.g. of data quality or statistics, like PotM CH.

General:

• Ubuntu
• CLI tool "OSM-PostgreSQL-Experiments" (ope): https://github.com/osmcode/osm-postgresql-experiments , z.B. src/ope -H 000.osc.gz obj=o%o.I.v.d.c.t.i.T.x.y.N.M.
• CLI tool "osm_pg_db_clipper" (Python, License GPL3): remove 1. nodes outside (CH) boundary (... NOT st_contains(ch.geom, nodes.geom)), 2. remove ways by missing node_id, 3. remove rels by missing nwr members.

Questions:

• Was passiert mit Ways und Relations, deren Nodes, bzw. Members nur teilweise fehlen (also von der CH-Grenze "angeschnitten" sind)? 1. CH-Grenze puffern und dann Elemente ganz weglassen; 2. An der CH-Grenze abschneiden; 3. Referenzen auf nicht vorhandene Objekte lassen, also Way hat immer noch eine Liste auf alle Nodes, aber nicht alle Nodes sind in der DB (das ist vor allem relevant für Relations).
• Was passiert mit Ways, wenn nur Node-Tags (Koordinaten) verändert werden?

Useful infos:

• OSM History Files: https://download.geofabrik.de/europe/switzerland.html
• Boundary of Switzerland (exact, without buffer) in .poly format: https://planet.osm.ch/switzerland-exact.poly
• (Calculate/map replicate sequences of diff files from timestamp: https://replicate-sequences.osm.mazdermind.de )

Tool Chain (run it finally with Switzerland, not Liechtenstein):

• Initialize: src/ope -H liechtentein.osh nodes=n%I.v.d.c.t.i.T.x.y. ways=w%I.v.d.c.t.i.T.N. relations=r%I.v.d.c.t.i.T.M. users=u%i.u.
• Find initial minutely seq. no. nnn. manually with timestamp (or automagically using pyosmium)
• Repeat
  • Download minutely change file using wget (or pyosmium getchange ...)
  • Create nnn.pgsql using src/ope -H nnn.osc.gz nodes=n%I.v.d.c.t.i.T.Gp ways=w%I.v.d.c.t.i.T.N. relations=r%I.v.d.c.t.i.T.M. users=u%i.u.
  • psql -U user -d osmhistorydb -f users.sql
  • psql -U user -d osmhistorydb -f nodes.sql
  • psql -U user -d osmhistorydb -f ways.sql
  • psql -U user -d osmhistorydb -f relations.sql
  • osm_pg_db_clipper.py osmhistorydb --boundary liechtenstein-exact.poly
• tbc.