Skip to content
Stefan edited this page Dec 30, 2020 · 18 revisions

The osmhistorydb-ch Wiki

This is the repository of the osmhistorydb-ch which aims at delivering a PostgreSQL database of OSM data in slightly enhanced OSM API schema which includes the whole history of all OSM elements.

The main use case is that this database serves another webapp, called "Project of the Month", which counts changes to certain objects within the timeframe of related actions. Other uses cases are to experiment as database backbone to

  • 'History renderers'
  • 'Permanent ID' services
  • Data analysis e.g. of data quality

General:

  • Debian
  • 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.

Frage: Was soll mit Ways und Relations passieren, 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).

Notes:

Tool Chain (finally to be run 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.
  • 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.x.y ways=w%I.v.d.c.t.i.T.N. relations=r%I.v.d.c.t.i.T.M.
    • psql -U user -d osmhistorydb -f users.pgsql
    • psql -U user -d osmhistorydb -f nodes.pgsql
    • psql -U user -d osmhistorydb -f ways.pgsql
    • psql -U user -d osmhistorydb -f relations.pgsql
    • osm_pg_db_clipper.py osmhistorydb -boundary liechtentein-exact.poly
  • tbc.
Clone this wiki locally