2

I successfully built a PostgreSQL server on an Ubuntu VM and populated it with the latest Geofabrik .osm.pbf complete dataset for Nepal. Now, I'd like to append to that dataset all of the historic edit data. I'd like to keep every version of every node, rel, and way, not just the latest, most up-to-date version.

My understanding is that OSM2PGSQL removes deprecated data when it appends the new data from a diff. Is this correct? If so, how can I go about storing a full history of edits, and not just the most recent ones?

When I try building an initial database with a full-history file like those found here, I get a crash with a duplicate key error, both on macOS and on Ubuntu.

Processing: Node(2760k 32.1k/s) Way(0k 0.00k/s) Relation(0 0.00/s)COPY_END for COPY planet_osm_ways FROM STDIN; failed: ERROR: duplicate key value violates unique constraint "planet_osm_ways_pkey" DETAIL: Key (id)=(4748383) already exists. CONTEXT: COPY planet_osm_ways, line 2

Error occurred, cleaning up

I tried following the OSM-history tutorial found here, but the dependencies used are deprecated on the latest Ubuntu and 1) make it so you have to uninstall curl (!!) and 2) meant I would have to dig into some dependency stuff which is way about my pay grade in terms of my experience with Ubuntu.

To be clear, I just want to store the date stamp and author of every previous edit to any existing node, relation, or way. I don't care what the actual edit was, I don't need to see it on a map. I just want to know when it happened and who did it.

  • So, summing up, all you need is a database with object type/id, version number, userid/username and timestamp. Why do you work with osm2pgsql then? That's a tool which is primarily used to prepare databases for rendering purposes. I would recommend to write a small C++ program based on libosmium instead, extract only relevant details and directly insert the data in a database table(s). I don't remember seeing a readymade solution for it, i.e. you're on your own. – mmd Oct 21 '16 at 10:33
  • You're right on both fronts. It was a bad approach. – Max von Hippel Oct 21 '16 at 10:48
  • See my answer, sorry if it's lacking in detail but there are >100 lines of code written today involved in the steps outlined so I think it's better to just explain the overarching structure of what I did. Also per your comment, C or C++ would have been better than what I did, because Perl is such a memory hog I ended up losing a bunch of time over splitting my file into smaller files. Thanks. – Max von Hippel Oct 21 '16 at 10:52
1

Here is what I ended up doing:

  1. I turned my OSH file into XML
  2. I wrote a Perl script to translate the resulting XML into JSON
  3. I wrote a BASH script to take the XML file (>200MB) and cut it into a bunch of smaller ones with correct nested formatting, etc.
  4. I used by Bash script to cut my XML into a ton of very small files.
  5. I parsed each smaller file with my Perl script into JSON.
  6. I haven't done it yet, but I will next populate a PostgreSQL database with those JSON files. Since they are keyed to "id:datestamp", I know I won't get any duplicate key errors, so everything should work fine.

UPDATE: My code worked, but it was unbelievably slow to parse XML in Perl. I am instead working on a solution in either Python or Ruby to parse pure PBF.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.