[OSM-dev] Is there a way to use simple schema without hstore

Andreas Kalsch andreaskalsch at gmx.de
Sat Nov 20 13:54:35 GMT 2010


thank you, Brett, for me this is the perfect setup. I hope that others will find it useful as well. 
The names are OK. Two problems:

1) I read XML or PBF, dump it to CSV and then read the dump, but now my feature tables are blank 
because of these errors:

ERROR:  extra data after last expected column
CONTEXT:  COPY nodes, line 1: "125799    5    45445    2010-09-30 21:23:30+0200    5922698        
ERROR:  extra data after last expected column
CONTEXT:  COPY ways, line 1: "3999478    17    4473    1970-01-07 21:53:09+0100    4957195    
ERROR:  extra data after last expected column
CONTEXT:  COPY relations, line 1: "2952    23    29975    1970-01-15 10:56:44+0100    5014762    

I use COPY instead of \copy like this:

COPY nodes FROM 'path/to/nodes.txt';

I don't know why this happens. The number and type of columns of the simple schema table and the CSV 
do fit.

Reading straight into database works (but is much slower).

2) --write-pgsimp-changeresults in this error log:

Nov 20, 2010 2:49:20 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version SNAPSHOT-r24310
Nov 20, 2010 2:49:20 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Nov 20, 2010 2:49:20 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Nov 20, 2010 2:49:20 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Nov 20, 2010 2:49:23 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager 
SEVERE: Thread for task 1-read-replication-interval failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to insert action with type=NODE, 
action=MODIFY and id=996097244.
     at org.openstreetmap.osmosis.pgsimple.v0_6.impl.ActionDao.addAction(ActionDao.java:80)
     at org.openstreetmap.osmosis.pgsimple.v0_6.impl.EntityDao.modifyEntity(EntityDao.java:263)
     at org.openstreetmap.osmosis.pgsimple.v0_6.impl.NodeDao.modifyEntity(NodeDao.java:74)
     at org.openstreetmap.osmosis.pgsimple.v0_6.impl.ChangeWriter.write(ChangeWriter.java:123)
     at org.openstreetmap.osmosis.core.container.v0_6.NodeContainer.process(NodeContainer.java:58)
     at org.openstreetmap.osmosis.core.sort.v0_6.ChangeSorter.complete(ChangeSorter.java:64)
     at java.lang.Thread.run(Thread.java:619)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint 
     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:321)
     at org.openstreetmap.osmosis.pgsimple.v0_6.impl.ActionDao.addAction(ActionDao.java:77)
     ... 11 more
Nov 20, 2010 2:49:23 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
     at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
     at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
     at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:329)
     at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:239)
     at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409)
     at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:352)
     at org.codehaus.classworlds.Launcher.main(Launcher.java:31)

Is it always guaranteed that a change for datatype/id is unique?

Am 20.11.10 07:27, schrieb Brett Henderson:
> On Sat, Nov 20, 2010 at 12:09 PM, Brett Henderson <brett at bretth.com <mailto:brett at bretth.com>> wrote:
>     Hmm, I've given all of this a bit more thought.  Perhaps there is a need for a "simple" schema
>     that is easy for people to populate and utilise.  I'm quite happy with hstore, but it's not as
>     simple for those familiar with basic SQL.
>     The original reason I created the so-called simple schema was to support improved bounding box
>     functionality because I couldn't do it via flat files efficiently.  It was called "simple"
>     because I was also working on a full history schema that I never found time to complete.  My
>     intent has always been to optimise for accurate bounding box query performance and not
>     simplicity so the name is something of a misnomer.
>     Anyway, perhaps I should re-instate the old tasks and run them alongside the new ones.  I'll
>     have to re-think the naming of these tasks and schemas.  Perhaps "simple" and "snapshot" or
>     something ...
>     But I don't know when I'll get to do this.  I'm very time poor at the moment.
> Okay, that wasn't too painful.  I found an hour or two to today so I've created new (actually old) 
> tasks and documented them here:
> http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage_0.39#PostGIS_Tasks_.28Simple_Schema.29
> It's available in SVN for now.  These "new" tasks are just the old tasks retrieved out of SVN 
> history and renamed.  Note that these tasks are effectively at a dead end because any new 
> development is likely to proceed on the new hstore schema.  They should continue to work for the 
> foreseeable future.
> I'll probably cause further confusion with schema names.  But I'm now calling the new style hstore 
> schema the "pgsnapshot" schema, and the older tag table style schema the "pgsimple" schema.  I'm 
> open to suggestions, but any change will need to be reflected on the wiki and in the codebase.
> Brett

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20101120/f8f5441d/attachment-0001.html>

More information about the dev mailing list