[OSM-dev] CSV tool for 0.5; target: plain SQL
Stefan de Konink
skinkie at xs4all.nl
Tue Oct 16 06:39:41 BST 2007
-----BEGIN PGP SIGNED MESSAGE-----
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
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.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.7 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
-----END PGP SIGNATURE-----
More information about the dev