[OSM-dev] Osmosis and Postgresql

[Graham Jones]

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:


   - 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.
   - MySQL - Can handle big datasets, but does not have geographic
   estensions.
   - Sqlite - I have seen a discussion about this and I think there are
   doubts over how well it works for big datasets?
   - GT.M - I know nothing about this, but the xapi servers seem to use it?

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.

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!

Regards


Graham.


2009/6/21 Brett Henderson <brett at bretth.com>

> Graham Jones wrote:
>
>> Hi Folks,
>> I have spent a couple of evenings trying to persuade osmosis to import a
>> planet dump into postgresql.
>>
>> It was quite a lot harder than I expected because of the need to set up
>> the database schema, postgis etc. first (I am not used to postgresql, which
>> was a lot of the problem).
>>
>> I have had a go at drafting a procedure for setting this up for the first
>> time at:
>>
>> http://wiki.openstreetmap.org/wiki/Talk:Osmosis#Procedure_to_import_data_into_PostGreSQL
>>
>> Would someone that know more about this than me mind having a look and
>> pointing out my mistakes please?  Once it is right I'll add it as a separate
>> wiki page rather than hiding it in the 'talk' bit.
>>
> Hi Graham,
>
> Thanks for taking the effort to document this.  The Osmosis documentation
> is lacking in a number of areas, this is one of them :-)  I see you've
> already added a link from the Osmosis detailed usage page which is great.
>
> If you get a chance, check out the --write-pgsql-dump task which is a much
> faster (but less convenient) way of importing very large data sets.  If you
> or somebody else has some time to document that procedure it would be very
> helpful.
>
> Cheers,
> Brett
>
>


-- 
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/20090621/f6dee024/attachment.html>