[OSM-dev] Osmosis and Postgresql

Graham Jones grahamjones139 at googlemail.com
Tue Jun 23 21:48:09 BST 2009


Jeffrey,
I can't help with your error, I'm afraid, but if you have an up to date
description of the database schema I would be happy to add it ot the summary
page I am writing.
I think that http://wiki.openstreetmap.org/wiki/Database/Model is supposed
to be a description of the main API DB schema, but it is labelled as out of
date, so maybe this would be a good place for your description to be stored?

Regards


Graham.

2009/6/22 Jeffrey Warren <warren at mit.edu>

> Hi, I'm compiling a very long description of a Postgres/Rails port schema
> import, though I'm writing directly to the db. I'd be happy to share/post my
> notes on the process as I managed to get around a number of undocumented
> problems.
> I'm still not done right now (i've been pinging the list here occasionally)
> and my latest problem is that I'm getting several errors like the following:
>
>
> ERROR:  insert or update on table "current_way_nodes" violates foreign key
> constraint "current_way_nodes_node_id_fkey"
>
> DETAIL:  Key (node_id)=(17704640) is not present in table "current_nodes".
>
>
> ********** Error **********
>
>
> ERROR: insert or update on table "current_way_nodes" violates foreign key
> constraint "current_way_nodes_node_id_fkey"
>
> SQL state: 23503
>
> Detail: Key (node_id)=(17704640) is not present in table "current_nodes".
>
>
> These are current_way_nodes which were not deleted from the current_ table
> when their matching current_nodes were deleted. I'm now trying to delete
> them manually since this only occurs where nodes are visible=false, but it's
> confusing when this causes the foreign key constraint creation to fail:
>
>
> ALTER TABLE ONLY current_way_nodes
>
>     ADD CONSTRAINT current_way_nodes_node_id_fkey FOREIGN KEY (node_id)
> REFERENCES current_nodes(id);
>
>
> Has anyone else encountered this? Is there a better way to resolve it?
>
> I tried following how the rails port actually deletes from current_nodes,
> but was unable to find it; is this done outside rails code? Can someone
> point to a line number where this is done so I can try to find out why the
> dependent current_way_node record wasn't deleted also? Hopefully this will
> eliminate this problem for future submitted changesets.
>
> Jeff
>
> ======== history of a node with leftover current_way_node ==========
>
> <?xml version="1.0" encoding="UTF-8"?>
> <osm version="0.6" generator="OpenStreetMap server">
>   <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="127793"
> user="southglos" uid="3937" visible="true" timestamp="2006-10-06T11:08:17Z"
> version="1">
>     <tag k="created_by" v="JOSM"/>
>   </node>
>   <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639"
> user="Strange but untrue" uid="57932" visible="true"
> timestamp="2009-04-16T22:28:59Z" version="2"/>
>   <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639"
> user="Strange but untrue" uid="57932" visible="true"
> timestamp="2009-04-16T22:31:23Z" version="3"/>
>   <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639"
> user="Strange but untrue" uid="57932" visible="true"
> timestamp="2009-04-16T22:32:07Z" version="4"/>
>   <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639"
> user="Strange but untrue" uid="57932" visible="true"
> timestamp="2009-04-16T22:32:19Z" version="5"/>
>   <node id="17704640" lat="51.5465346" lon="-2.4116768" changeset="601639"
> user="Strange but untrue" uid="57932" visible="false"
> timestamp="2009-04-16T22:32:21Z" version="6"/>
> </osm>
>
>
>
> On Mon, Jun 22, 2009 at 4:09 PM, 80n <80n80n at gmail.com> wrote:
>
>> On Mon, Jun 22, 2009 at 2:29 AM, Brett Henderson <brett at bretth.com>wrote:
>>
>>> Graham Jones wrote:
>>> > Brett,
>>> >
>>> > I'll have a look at --write-pgsql-dump and add some words about that
>>> > too.  Then I'll have a go at MySQL...
>>> >
>>> > The other thing I would like to add is a write-up on choosing a
>>> > database to use, because it is not obvious to me which would be the
>>> > best, but I suspect this has been looked at a lot before now.  I think
>>> > the choices are:
>>> I'm not aware of a page summarising this, so it sounds very useful.
>>> Lots of people ask about it.
>>> >
>>> >     * PostgreSQL/PostGIS - Can handle big datasets, and has geographic
>>> >       extensions.  What I do not know is whether these extensions are
>>> >       fast or not, or if for simple things like selecting for a
>>> >       bounding box it would be quicker to just select on lat/lon
>>> >       directly.   Mapnik uses this database, but the schema is
>>> >       optimised for rendering.
>>> >
>>> There's actually 3 PostgreSQL schemas which causes much confusion:
>>> 1. The API database.  This is the database behind the main OSM
>>> ruby-based API.  This is supported by the osmosis "apidb" tasks.  The
>>> only db capable of holding full history.
>>> 2. The Mapnik database.  Used by the mapnik renderer.  Loaded using
>>> osm2pgsql.  Not supported by osmosis.  Uses PostGIS extensions.  Holds a
>>> filtered subset of data appropriate for rendering.
>>> 3. The pgsql "simple" schema.  Badly named, but this is a schema I
>>> created for holding OSM snapshots (ie. doesn't maintain history).  It
>>> does support replication for keeping it up to date.  Uses PostGIS
>>> extensions.  Used by ROMA servers.
>>>
>>> All three schemas above can support minutely updates, 1 and 3 by
>>> osmosis, 2 by osm2pgsql.
>>> >
>>> >    *
>>> >
>>> >
>>> >     * MySQL - Can handle big datasets, but does not have geographic
>>> >       estensions.
>>> >
>>> This is the same schema as the PostgreSQL API schema, but implemented in
>>> MySQL.  Provides identical features, but no longer the preferred
>>> database of choice.  It's being maintained in the short term, but I
>>> suspect it will get dropped over time if PostgreSQL continues to work
>>> well.
>>> >
>>> >     * Sqlite - I have seen a discussion about this and I think there
>>> >       are doubts over how well it works for big datasets?
>>> >
>>> No idea on this one, but yes I'd be dubious about its use for large
>>> databases.  Not supported by osmosis.
>>>
>>> On this note, I've also messed about with the Berkeley DB Java Edition
>>> which didn't work well on large datasets.  I've long since deleted the
>>> osmosis tasks that used it.
>>> >
>>> >     * GT.M - I know nothing about this, but the xapi servers seem to
>>> >       use it?
>>> >
>>> I don't know anything about this either.
>>>
>>
>> GT.M is a high performance schemaless datastore.  A bit like CouchDB.
>>
>> Probably not what you are looking for.
>>
>> 80n
>>
>>
>>
>>> >
>>> >    *
>>> >
>>> >
>>> > I would also like to include some example SQL queries to deal with
>>> > selecting nodes / ways etc. in a given bounding box, or searching for
>>> > points of interest.   These are not too difficult to do, but I suspect
>>> > that some ways are more efficient than others, so it would be nice to
>>> > include 'optimum' examples.
>>> Sounds reasonable.  You may wish to check out the osmosis "pgsql"
>>> queries in this space, it uses true geo-spatial queries if the optional
>>> way "linestring" and "bbox" columns are available.
>>> >
>>> > Do you know if this has already been done and written up somewhere?
>>> > If not I will set up a template and ask for help filling it in!
>>> I haven't seen it before (not that I've looked ...).  Sounds great to me!
>>>
>>> A couple of other db mechanisms are available, details are on the wiki.
>>> TRAPI - Stores data using a tile-based mechanism.
>>> http://wiki.openstreetmap.org/wiki/TRAPI
>>> osmbin - A compact custom storage mechanism.  Supported by osmosis via a
>>> plugin.
>>>
>>> http://wiki.openstreetmap.org/wiki/Osmosis/DetailedUsage#--write-osmbin-0.6
>>>
>>> Brett
>>>
>>>
>>> _______________________________________________
>>> dev mailing list
>>> dev at openstreetmap.org
>>> http://lists.openstreetmap.org/listinfo/dev
>>>
>>
>>
>> _______________________________________________
>> dev mailing list
>> dev at openstreetmap.org
>> http://lists.openstreetmap.org/listinfo/dev
>>
>>
>
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/dev
>
>


-- 
Dr. Graham Jones
Hartlepool, UK
email: grahamjones139 at gmail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20090623/7a408249/attachment.html>


More information about the dev mailing list