[OSM-dev] Osmosis and Postgresql

[80n]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20090622/6a9036a5/attachment.html>