[OSM-dev] osmosis pgsql schema / way.bbox removal/option

[Brett Henderson]

On Tue, Sep 9, 2008 at 4:01 AM, Florian Lohoff <flo at rfc822.org> wrote:

> On Mon, Sep 08, 2008 at 08:05:56AM -0500, Ian Dees wrote:
> > Subject: Re: [OSM-dev] osmosis pgsql schema / way.bbox removal/option
> > On Mon, Sep 8, 2008 at 7:29 AM, Jochen Topf <jochen at remote.org> wrote:
> >
> > >
> > > I use the --write-pgsql-dump which creates files which can then be
> > > imported using COPY. If you use it that way, Osmosis doesn't do any
> > > extra postprocessing like the bbox thing.
> > >
> >
> > It's important to use the spatial keys for at least the points, as this
> > significantly improves query times. If you don't, Postgres will have to
> go
> > through all 260M rows in the database to return meaningful data.
>
> I have those running and they work like a charm. I am right playing
> around with implementing all the tricky things in the database with temp
> tables to be able to really stream all or most of the xml instead of
> pulling the data together in perl. Concerning the simple scheme. Its
> also missing an index on the way_nodes table for the way_id but thats
> just peanuts.
>
> > On Mon, Sep 08, 2008 at 11:58:18AM +0200, Florian Lohoff wrote:
> > > i was trying to use the postgresql simple schema from osmosis to import
> > > a planet and after 5 days i aborted the import. 4 of the 5 days the
> > > postgresql was busy building the bboxes for the ways.
> >
> > Florian, my version of ROMA was doing this, too. I ended up stopping the
> > import after 6 days of indexing. It would make things run so much faster
> > (and allow us to retrieve all the ways in a bounding box, even the ones
> that
> > don't have nodes in the bounding box), but it's not worth the extra
> planet
> > import time I think. At least when I'm paying USD$0.10/hour for it...
>
> Okay - dropping the bbox on the ways which are useless for us you can do
> the import in 1200 minutes - at least on my hardware.
>

As Jochen mentioned, the --write-pgsql-dump task and subsequent import using
COPY statements should be faster.  I've looked at getting osmosis to stream
data directly into the database but unfortunately jdbc drivers don't expose
that kind of functionality.

I'll take a look at this bbox thing.  Perhaps I can get osmosis to query the
database to see if the bounding box column exists and only populate it if it
does ...

Right now I'm playing with code to manually populate the bbox column during
an import using a memory mapped file containing node lat/lon values but if
nobody needs the column I'm probably wasting my time.

I intend to make all of these changes in the API 0.6 version though, not
sure if that's an issue.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20080909/61d6154f/attachment.html>