Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On Fri, May 15, 2009 at 10:58 AM, Tomas Kolda ko...@web2net.cz wrote: Did you try my version of import? I have no responses, so maybe not :). I did not tried complete planet, but indexes was created quite fast. 30seconds on 150MB osm file. I think that you should make a chart (xml size-time to create index) and than you can see complexity of creating indexes in sqlite based on OSM size. I think that it will be O(n log(n)), but maybe not. Handling disk isn't the strong point of SQLite, creating your SQLite DBs on machines with lots of memory will make things useable. You can always move them to low memory machines later. Storing the DB on a ram disk while creating will speed things up considerably. ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
Did you try my version of import? I have no responses, so maybe not :). I did not tried complete planet, but indexes was created quite fast. 30seconds on 150MB osm file. I think that you should make a chart (xml size-time to create index) and than you can see complexity of creating indexes in sqlite based on OSM size. I think that it will be O(n log(n)), but maybe not. If I will have time, I can try to convert planet using my tool. T Kelly Jones napsal(a): Thanks to everyone who replied. OK, loading all the nodes for OSM doesn't take much time, but INDEXing the fields takes forever (days). The first time I tried this, I pre-created the indexes before loading the data. That's why the load took so long. This time, I loaded the data first (fairly quick) and then created the indexes. It's now been several days and the indexes are still being created. Thoughts? ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
Hi, I will post something, but now I'm not at home. So maybe today night. But Dane wrote me about sqlite support in mapnik, so you can try that: Are you aware we have a postgres - sqlite converter in mapnik? https://trac.mapnik.org/browser/trunk/utils/pgsql2sqlite It requires the postgis and sqlite plugins: https://trac.mapnik.org/browser/trunk/plugins/input Just build mapnik with: $ python scons/scons.py INPUT_PLUGINS=all PGSQL2SQLITE=True I have been hand converting the XML, a scripted approach would be cool. Dane - https://trac.mapnik.org/browser/trunk/utils/pgsql2sqliteI don't thing that it is usable for me, because I do not like to convert something many times to get the result. But I do not know about it (I played with it half year ago, so maybe it did not exists yet). Tomas Christoph Eckert napsal(a): Hi, There is no reason to be sqlite slow. How much are you are using transactions on your local db? What are you using for import? I'm importing Czech republic (50Mb bziped) in one or two minutes. So for 5.4GB it takes 108 minutes (two hours). But I'm importig to modified osm schema (something like mapnik schema in postgress). It is because of query speed. sounds cool. Is the Script that does the conversion from osm.xml into sqlite available somewhere? Thanks best regards, ce ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On Tue, 28 Apr 2009 14:30:33 +, Ævar Arnfjörð Bjarmason ava...@gmail.com wrote: From what I can see at http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Embedded-Server.html#Embedded-Server the embedded-mode is still a server, just for embedded systems. I was talking about having the database as a library be a part of the program. Nothing to install or to start separately. You could always make your program embed/start the database server regardless of whether it's a library or a server. It would just mean e.g. shipping the postgresql binaries with your program and doing some magic in your program that ensures that the server is started when the program is. And determining what platform's binary to run in the first place (Windows? Linux? Solaris?, Windows Mobile?, ...) including the version of the libc. I thought about what is possible here for a while now and I guess the effort and download-size are not worth it. Marcus ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On Wed, Apr 29, 2009 at 8:41 AM, marcus.wolsc...@googlemail.com wrote: On Tue, 28 Apr 2009 14:30:33 +, Ævar Arnfjörð Bjarmason ava...@gmail.com wrote: From what I can see at http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Embedded-Server.html#Embedded-Server the embedded-mode is still a server, just for embedded systems. I was talking about having the database as a library be a part of the program. Nothing to install or to start separately. You could always make your program embed/start the database server regardless of whether it's a library or a server. It would just mean e.g. shipping the postgresql binaries with your program and doing some magic in your program that ensures that the server is started when the program is. And determining what platform's binary to run in the first place (Windows? Linux? Solaris?, Windows Mobile?, ...) including the version of the libc. I thought about what is possible here for a while now and I guess the effort and download-size are not worth it. You'd build postrgresql with the compilation flags you'd use for your program: Problem solved. Any embedded database like sqlite would have to talk to libc too, and so (presumably) would your main program. ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On Wed, 29 Apr 2009 10:18:55 +, Ævar Arnfjörð Bjarmason ava...@gmail.com wrote: You'd build postrgresql with the compilation flags you'd use for your program: Problem solved. My program is not compiled. It's java. Any embedded database like sqlite would have to talk to libc too, and so (presumably) would your main program. Marcus ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On 28 Apr 2009, at 00:16, Kelly Jones wrote: I've seen many posts saying that SQLite2 can't handle OpenStreetMap's large planet.osm data file: http://planet.openstreetmap.org/planet-090421.osm.bz2 which is 5.4G bzip2 compressed, about 150G uncompressed. Can SQLite3 handle this? Has anyone tried? I tried to do this myself, but I'm on a slow machine and it's taking too long (several days so far). If someone's done this, can I get a copy? SQLite isn't designed for huge databases like OpenStreetMap. You could get away with a city or small region, but more than that, you will get the slowness that you are seeing. http://www.sqlite.org/faq.html#q19 and some other FAQs on that page. You are much better using Postgres, or even MySQL. Shaun ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On Tue, 28 Apr 2009 08:02:30 +0100, Shaun McDonald SQLite isn't designed for huge databases like OpenStreetMap. You could get away with a city or small region, but more than that, you will get the slowness that you are seeing. http://www.sqlite.org/faq.html#q19 and some other FAQs on that page. You are much better using Postgres, or even MySQL. Is there ANY embedded database out there that can handle the planet? Anything that does not require the (unskilled) user of a program to install a local database-server first? Marcus ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
marcus.wolsc...@googlemail.com wrote: On Tue, 28 Apr 2009 08:02:30 +0100, Shaun McDonald SQLite isn't designed for huge databases like OpenStreetMap. You could get away with a city or small region, but more than that, you will get the slowness that you are seeing. http://www.sqlite.org/faq.html#q19 and some other FAQs on that page. You are much better using Postgres, or even MySQL. Is there ANY embedded database out there that can handle the planet? Anything that does not require the (unskilled) user of a program to install a local database-server first? MonetDB can be running in embedded mode ;) Stefan ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
There is no reason to be sqlite slow. How much are you are using transactions on your local db? What are you using for import? I'm importing Czech republic (50Mb bziped) in one or two minutes. So for 5.4GB it takes 108 minutes (two hours). But I'm importig to modified osm schema (something like mapnik schema in postgress). It is because of query speed. I'm playing with sqlite and Mapnik. Sqlite knows RTree spatial index and with small modification to mapnik (sqlite datasource) it runs ok. If you use 8x8 tile per render, it is quite fast. RTree in sqlite is fast, but there is overhead, when you use one bound rect per entity and also with mapnik (it queries many times per one render for same area). But if you do not need speed it works. Tomas Shaun McDonald napsal(a): On 28 Apr 2009, at 00:16, Kelly Jones wrote: I've seen many posts saying that SQLite2 can't handle OpenStreetMap's large planet.osm data file: http://planet.openstreetmap.org/planet-090421.osm.bz2 which is 5.4G bzip2 compressed, about 150G uncompressed. Can SQLite3 handle this? Has anyone tried? I tried to do this myself, but I'm on a slow machine and it's taking too long (several days so far). If someone's done this, can I get a copy? SQLite isn't designed for huge databases like OpenStreetMap. You could get away with a city or small region, but more than that, you will get the slowness that you are seeing. http://www.sqlite.org/faq.html#q19 and some other FAQs on that page. You are much better using Postgres, or even MySQL. Shaun ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On Tue, Apr 28, 2009 at 8:26 AM, Stefan de Konink ste...@konink.de wrote: marcus.wolsc...@googlemail.com wrote: On Tue, 28 Apr 2009 08:02:30 +0100, Shaun McDonald SQLite isn't designed for huge databases like OpenStreetMap. You could get away with a city or small region, but more than that, you will get the slowness that you are seeing. http://www.sqlite.org/faq.html#q19 and some other FAQs on that page. You are much better using Postgres, or even MySQL. Is there ANY embedded database out there that can handle the planet? Anything that does not require the (unskilled) user of a program to install a local database-server first? MonetDB can be running in embedded mode ;) seriously, you should get paid for the amount you promote MonetDB ;-) cheers, matt ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On Tue, 28 Apr 2009 09:26:21 +0200, Stefan de Konink ste...@konink.de wrote: marcus.wolsc...@googlemail.com wrote: On Tue, 28 Apr 2009 08:02:30 +0100, Shaun McDonald SQLite isn't designed for huge databases like OpenStreetMap. You could get away with a city or small region, but more than that, you will get the slowness that you are seeing. http://www.sqlite.org/faq.html#q19 and some other FAQs on that page. You are much better using Postgres, or even MySQL. Is there ANY embedded database out there that can handle the planet? Anything that does not require the (unskilled) user of a program to install a local database-server first? MonetDB can be running in embedded mode ;) From what I can see at http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Embedded-Server.html#Embedded-Server the embedded-mode is still a server, just for embedded systems. I was talking about having the database as a library be a part of the program. Nothing to install or to start separately. Marcus ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On Tue, Apr 28, 2009 at 12:29 PM, marcus.wolsc...@googlemail.com wrote: On Tue, 28 Apr 2009 09:26:21 +0200, Stefan de Konink ste...@konink.de wrote: marcus.wolsc...@googlemail.com wrote: On Tue, 28 Apr 2009 08:02:30 +0100, Shaun McDonald SQLite isn't designed for huge databases like OpenStreetMap. You could get away with a city or small region, but more than that, you will get the slowness that you are seeing. http://www.sqlite.org/faq.html#q19 and some other FAQs on that page. You are much better using Postgres, or even MySQL. Is there ANY embedded database out there that can handle the planet? Anything that does not require the (unskilled) user of a program to install a local database-server first? MonetDB can be running in embedded mode ;) From what I can see at http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Embedded-Server.html#Embedded-Server the embedded-mode is still a server, just for embedded systems. I was talking about having the database as a library be a part of the program. Nothing to install or to start separately. You could always make your program embed/start the database server regardless of whether it's a library or a server. It would just mean e.g. shipping the postgresql binaries with your program and doing some magic in your program that ensures that the server is started when the program is. ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
Hi, There is no reason to be sqlite slow. How much are you are using transactions on your local db? What are you using for import? I'm importing Czech republic (50Mb bziped) in one or two minutes. So for 5.4GB it takes 108 minutes (two hours). But I'm importig to modified osm schema (something like mapnik schema in postgress). It is because of query speed. sounds cool. Is the Script that does the conversion from osm.xml into sqlite available somewhere? Thanks best regards, ce ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can SQLite3 handle OSM 150G data file?
On 09-04-28 09:14:31 CEST, marcus.wolsc...@googlemail.com wrote: Is there ANY embedded database out there that can handle the planet? Anything that does not require the (unskilled) user of a program to install a local database-server first? has anybody tried embedded innodb? i saw mention of it in the news a few days ago. rj ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
