[osmosis-dev] Problems importing osm files to API bd

[Brett Henderson]

Hi Egoitz,

Apologies for the slow reply, but better late than never :-)

Comments below.

On Wed, Feb 9, 2011 at 9:54 PM, Egoitz Ormaetxea <egoitz.ormaetxea at gmail.com
> wrote:

> I've installed Rails port using the guide from
> http://wiki.openstreetmap.org/wiki/Rails and
> http://wiki.openstreetmap.org/wiki/Rails_on_Ubuntu
> Also installed osmosis using the guide from
> http://wiki.openstretmap.org/wiki/Osmosis/Installation
>
> All installed ok on an Ubuntu 10.04 server running over a DELL PowerEdge
> R710 with 450Gb for the Postgres DB partition and 12Gb of RAM.
>
> I downloaded the planet osm file form
> http://planet.openstreetmap.org/planet-latest.osm.bz2 ( to be honest is
> the planet-latest for 27 October 2010, at the moment I'm downloading the new
> current latest ) and also downloaded some osm files for different countries
> from http://downloads.cloudmade.com
>
> To extract the information of a zone using the planet osm file I use this
> sentence :
>
> bzcat planet-101027.osm.bz2 | osmosis --read-xml file=/dev/stdin
> enableDateParsing=no --bounding-box top=43.5 left=-3.5 bottom=42.0
> right=-1.0 --write-xml file=- | bzip2 > extracted.osm.bz2
>
> It takes a long time to complete but all seems to be ok.
>
> To populate an empty API DB from the osm files I use this sentence :
>
> bzcat file.osm.bz2 | osmosis --read-xml-0.6 file=- --write-apidb-0.6
> populateCurrentTables=yes host="localhost" database="openstreetmap"
> user="openstreetmap" password="xxxxx" validateSchemaVersion=no
>
> I'm getting the following types of errors ( depending on the osm file used
> ) :
>
> Using Ecuador osm file form Cloudmade at Feb 8, 2011 :
> SEVERE: Thread for task 1-read-xml-0.6 failed
> org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to insert
> user with id 222199 into the database.
> Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value
> violates unique constraint "users_display_name_idx"
>

I haven't seen this before, but I suspect I know what is causing it.  I'm
guessing that there are two entities (nodes, ways or relations) that have
different user ids but the same display name in the file.  I haven't seen it
before, but it's possible that a user has been renamed, then subsequently a
separate user renamed to user the same name as the first user's original
name.  That might cause the same username to appear with different user ids.

If you download a fresh planet all usernames will be aligned, and if you
subsequently load that planet into a database and keep the database
up-to-date with daily/hourly/minutely diffs the users will also be kept
correctly named because users are stored in a separate users table
eliminating duplication.  It's only a problem when applying diffs to XML
files because only updated entities will have their usernames updated, any
existing entities will continue to have the names from the input XML file.


>
> Using Ghana osm file form Cloudmade at Feb 8, 2011 :
> SEVERE: Thread for task 1-read-xml-0.6 failed
> org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to load
> current way nodes.
> Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on
> table "current_way_nodes" violates foreign key constraint
> "current_way_nodes_node_id_fkey"
> Detail: Key (node_id)=(24152848) is not present in table "current_nodes"
>
> Using the osm file result of the extract sentence I've writed earlier :
> SEVERE: Thread for task 1-read-xml-0.6 failed
> org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to load
> current way nodes.
> Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on
> table "current_way_nodes" violates foreign key constraint
> "current_way_nodes_node_id_fkey"
> Detail: Key (node_id)=(490767853) is not present in table "current_nodes"
>

These are usually caused by inconsistencies in the XML file you've
downloaded.  In both cases ways are referring to nodes that don't exist in
the file.  Without knowing the complete history of the files you've
downloaded it's hard to know why this might be.

In the past, I've simply dropped the current_way_nodes_node_id_fkey
constraint to make it work, but that could potentially cause other issues.


>
> Using Spain osm file from Cloudmade at Feb 8, 2011 :
> All ok. I can use JOSM to access ( and even change ) the data from the API
> DB.
>
> The API BD is always new ( dropdb : createdb : rake db:migrate : rake test
> ) before the population senteces.
>
> What I'm doing wrong ?
>

You don't appear to be doing anything wrong.  You're just running into data
consistency issues that are hard to avoid with a dataset the size of OSM.

Brett
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/osmosis-dev/attachments/20110218/23562894/attachment.html>