[OSM-dev] CSV tool for 0.5; target: plain SQL

[Stefan de Konink]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Hi Brett,

Brett Henderson schreef:
> Stefan de Konink wrote:
>> But it looks not yet compatible to Api 0.5. I would like to import the
>> planet.osm in another database that only supports 'plain SQL'. Without
>> fancy PostGIS or MySQL syntax. Currently I have used a sed regular
>> expression to fish out nodes from the planet.osm file, but I would like
>> to use a more decent way of converting. XQuery is my last resort...
> 
> I'm not too sure what you mean by "plain SQL".  Whatever SQL you produce 
> will need to be targeted at a database schema and the only schema I'm 
> aware of for holding all OSM types is the current MySQL schema.  What 
> schema are you targeting?

Currently my idea was to create a table of nodes, where the XML
attributes of the nodes are  in the same table. And the tag children are
joined with a foreign key constraint on id to that table.

As a short example:

create table test (bla int); [works]
create table test (bla int(11)); [doesn't work]


> Having said that, I'm not aware of any tool that will generate an SQL 
> script regardless of whether it's tied to a particular database engine 
> or not.

I guess anything that goes from XML to 'INSERT' will do for now, just as
example. The following command works, but still I need to join the
entire file first to be able to process 'over line scanning'. So
<node(.*)</node>.

 cat planet-071003.osm | grep "<node " | sed "s/<node id=\"\([0-9]*\)\"
lat=\"\([0-9.-]*\)\" lon=\"\([0-9.-]*\)\" .*/INSERT INTO current_nodes
(id, latitude, longitude) values \(\\1, \\2, \\3\);/g" > nodes.sql


> If I wanted to produce a SQL script I would write a new task for 
> osmosis.  Using the existing MySqlWriter class as an example, it should 
> be fairly simple to modify it to write SQL statements to a file instead 
> of applying direct to a database.  That class is way more complicated 
> than you need because it utilises a number of buffers and a bunch of 
> logic to allow many rows to be inserted at once for performance reasons 
> and does special table locking, etc but all of that could be ripped out 
> in this case.  Osmosis would be very fast for this, reading a planet 
> file and producing a file at the other end is its bread and butter.

My Java is a bit rusty, but maybe it would be a nice feature request
that any database could be specified and was accessed through JDBC.



Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.7 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHFE6dYH1+F2Rqwn0RCoY4AJ91bfAgmqfiIRF4jai1bRHf4T6BsQCcCafD
kQveVOFiB16tgHNDfV+Z6MU=
=xaTI
-----END PGP SIGNATURE-----