[OSM-dev] Osmosis and Postgresql

Jeffrey Warren warren at mit.edu
Tue Jun 30 21:03:35 BST 2009


Agreed, it's quite confusing. The page you reference seems hard to maintain
since it's basically an image. Perhaps a sub-page for each schema would be
nice? At least mentioning the simple-schema, the rails-port schema (the one
in the migrations) and the one brett hosts, those three would be quite nice
to list with some notes on each and a link to download them. The simple
schema comes with Osmosis and excludes history, i think. I have notes on
getting the migrations to run so if you stub out the pages I'll put what
notes i have in them.
Jeff

On Tue, Jun 30, 2009 at 1:00 PM, Graham Jones <grahamjones139 at googlemail.com
> wrote:

> I don't think I want a different one, I was just struggling to understand
> what the differences are between all these different databases that people
> use so I can choose one, and thought it would be worth documenting it once I
> had worked it out!
>
> Graham
>
> 2009/6/30 Jeffrey Warren <warren at mit.edu>
>
> I'd like to, but how does the schema you want differ from the schemas Brett
>> Henderson hosts?
>> And along those lines, should we remove the foreign key constraints from
>> the schema, Brett, if they're not necessary and they cause Osmosis imports
>> to fail?
>>
>> Jeff
>>
>> On Tue, Jun 23, 2009 at 4:48 PM, Graham Jones <
>> grahamjones139 at googlemail.com> wrote:
>>
>>> 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
>>>
>>
>>
>
>
> --
> 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/20090630/32be7835/attachment.html>


More information about the dev mailing list