My familarity with databases might equal my knowledge of french language, thus the subject ;-) . More sincerely, I’m currently playing with some C++ code to stuff an OSM XML file into an sqlite database (which caused less posts on my blog, BTW). Firstly osm data in an sqlite database has been “requested” on the mailing lists more than once, and secondly querying such a database file will make the former tool, osmpoicreator, obsolete.
I am using Qt 4.3, namely its QXmlStreamReader and QSqlDatabase classes. While I’m not done with the code yet, I already can see that it will be way too slow to process huge osm files later. Reading (and processing the elements of) the XML file is fast, but as soon as it comes to writing to the database, execution speed decreases significantally. More precisely, it slows down as soon as QSqlQuery.exec() gets into the game.
It is likely that my database design is poor, though I created several indices. I hope to get a little help from my osm friends during the OePNV-Workshop next weekend. I know they are familiar with databases, so resistance is futile :) .
Make sure not to use the auto-transactions generated by sqlite for each and every insert. Start transactions explicitely…
»Wer lesen kann, ist klar im Vorteil«:
http://www.sqlite.org/lang_transaction.html
What an excellent hint. Execution speed has decreased from (felt) 5 seconds to fractions of a second! I owe you a lunch :) .
hey, I was wondering if you could provide your app to convert OSM to sqlite? I’m quite new to it all and just started using MaemoMapper, so I have no clue how to do all of this!
The code is not done yet, but currently I have stopped working on it due to some issues with the database design and, err, some holidays :) . The code will later be published via the openstreetmap SVN, of course.