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

Thu Feb 24 08:16:46 GMT 2011

2011/2/18 Brett Henderson <brett at bretth.com>

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

Yes much better XD

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

Thanks I'll extract Ecuador .osm file directly from the latest planet file.

>
>
>>
>> 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.
>>
>
>
Lets use an example to explain the problem :

Using a osm file downloaded from Cloudmade that works correctly ( importing
to APIDB and editing with JOSM ), if I extract a bounding box with this
sentence ... :

bzcat spain.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

and try to populate de APIDB with the extracted file ... :

bzcat extracted.osm.bz2 | osmosis --read-xml-0.6 file=- populateCurrent=yes
host="localhost" database="xxx" user="xxx" paasword="xxx"
validateSchemaVersion=no

Ends with error, ( Detail: Key (node_id)=(490767853) is not present in table
"current_nodes" ) that node is out of the extract bounding box and it
thosen't appear at extracted.osm.bz2 but it appears at spain.osm.bz2 ( the
node is part of a way that starts at the bounding box but ends outside the
bounding box ).

So tried the drop constraint solution :
psql -d openstreetmap -c "ALTER TABLE current_way_nodes DROP CONSTRAINT
current_way_node_id_fkey CASCADE;"

It solved the populating problems ( no errors ), but using JOSM to see the
APIBD information causes error if I try to view any way related with the
ways that created the previous importing error ( it works if I view a zone
with no ways related with the "outside of the bounding box" ):
Log of the API server :

Processing ApiController#map (for 192.168.3.111 at XXX) [GET]
Parameters: {"bbox"=>"-3.0308533,43.3311716,-3.0116272,43.345155"}
Node Load (5.5ms) SELECT * FROM "current_nodes" WHERE
("current_nodes"."visible" = 't' ) AND (( tile BETWEEN 1878255232 AND
1878255251 OR tile BETWEEN 1878255256 AND 1878255259 ) AND latitude BETWEEN
433311716 AND 433451550 AND longitude BETWEEN -30308533 AND -30116272) LIMIT
50001
NodeTag Load (12130.7ms) SELECT "current_node_tags".* FROM
"current_node_tags" WHERE ("current_node_tags".id IN (3085055...<lot of
numbers>..))
WayNode Load (4.8ms) SELECT * FROM "current_way_nodes" WHERE
("current_way_nodes"."node_id" IN (<lot of numbers>))
Way Load (1.5ms) SELECT * FROM "current_ways" WHERE ("current_ways"."id" IN
(<lot of numbers>))
WayNode Load (4.0ms) SELECT "current_way_nodes".* FROM "current_way_nodes"
WHERE ("current_way_nodes".id IN (<lot of numbers>))
WayTag Load (1.7ms) SELECT "current_way_tags".* FROM "current_way_tags"
WHERE ("current_way_tags".id IN (<lot of numbers>))
Node Load (7.2ms) SELECT * FROM "current_nodes" WHERE ("current_nodes"."id"
IN (<lot of numbers>))
NodeTag Load (11976ms) SELECT "current_node_tags".* FROM "current_node_tags"
WHERE ("current_node_tags".id IN (<lot of numbers>))

Completed in 24544ms (View: 1, DB: 24133) | 404 Not Found [
http://192.168.2.173/api/0.6/map?bbox=-3.0308533,43.3311716,-3.0116272,43.345155
]

JOSM error ( I don´t have the JOSM in English so this is a "homemade
translation"  of the error message ) :
The OSM server "http://192.168.2.173:3000/api/0.6/" doesn´t know about an
object you tried to read, update or delete. Maybe the object dosen´t exist
at the server or you are using a non valid URL to access to it. Please
confirm the server URL "http://192.168.2.173:3000/api/0.6/"

PD: I downloaded the new osm file for Spain from Cloudmade, It gives me the
same importing error, the previous file ( Feb 8 ) worked with no error. I
must have done some noob/strange/silly error because no one is complaining
about the files from Cloudmade.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/osmosis-dev/attachments/20110224/47def1c5/attachment.html>