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

[Florian Lohoff]

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.

Flo
-- 
Florian Lohoff                  flo at rfc822.org             +49-171-2280134
	Those who would give up a little freedom to get a little 
          security shall soon have neither - Benjamin Franklin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20080908/ab27522b/attachment.pgp>