Re: [osmosis-dev] planet import
Hi Brian, Yes, you should definitely use the built-in box builder functionality. I've never tried to use the database to do it on a complete planet. I have no idea how long it will take. Brett On Sun, Apr 17, 2011 at 4:34 AM, Brian DeRocher br...@derocher.org wrote: On Saturday 09 April 2011 22:27:31 you wrote: I started a full planet import on the 29th, 11 days ago. I'm trying to get an idea how long this will take. I just want to know if this will take about 20 days or more like 40 days. I think i found my answer, thanks to Toby: enableBboxBuilder=yes: This tells the write-pgsql-dump task to calculate a bounding box for each way. This is stored in a geometry type column in postgres that can be used in spatial queries. This can be done in the database but you may not live to see it finish if you are processing the entire planet. Seriously. http://weait.com/content/build-your-own-openstreetmap-server Also i was using Osmosis 0.34 from Debian 6. I'll upgrade to the stable or devel release, or try svn. For the record this import is on day 17.5 and it's currently running this query for 15 days. UPDATE ways SET bbox = ( SELECT Envelope(Collect(geom)) FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id WHERE way_nodes.way_id = ways.id ) Brian -- Brian DeRocher http://brian.derocher.org ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
Re: [osmosis-dev] planet import
On Saturday 09 April 2011 22:27:31 you wrote: I started a full planet import on the 29th, 11 days ago. I'm trying to get an idea how long this will take. I just want to know if this will take about 20 days or more like 40 days. I think i found my answer, thanks to Toby: enableBboxBuilder=yes: This tells the write-pgsql-dump task to calculate a bounding box for each way. This is stored in a geometry type column in postgres that can be used in spatial queries. This can be done in the database but you may not live to see it finish if you are processing the entire planet. Seriously. http://weait.com/content/build-your-own-openstreetmap-server Also i was using Osmosis 0.34 from Debian 6. I'll upgrade to the stable or devel release, or try svn. For the record this import is on day 17.5 and it's currently running this query for 15 days. UPDATE ways SET bbox = ( SELECT Envelope(Collect(geom)) FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id WHERE way_nodes.way_id = ways.id ) Brian -- Brian DeRocher http://brian.derocher.org signature.asc Description: This is a digitally signed message part. ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
[osmosis-dev] planet import
Hey everyone, I started a full planet import on the 29th, 11 days ago. I'm trying to get an idea how long this will take. I just want to know if this will take about 20 days or more like 40 days. Here's my setup: 2 dual core Opterons, cpu is not the bottleneck 8 GM ram, htop reports this RES memory usage postgres 1082M UPDATE java osmosis 91928 (15 processes/threads?) Areca RAID 5 1T with 3 disks /var is 552 GB, 444 GB used (87%) 80GB available This usage has gone up and down from 84% to 91% a few times per day. The import added about 300GB. Debian 6.0 PostgreSQL 8.4 is probably not tuned well for this hardware, and it's not tuned well for large imports. work_mem1MB maintenance_work_mem16MB checkpoint_segments 3 fsync on (i have a BBU and may set this to off in the future) shared_buffers 24MB The xlog is on the RAID 5 array too. I've modified osmosis to connect to port 5433. Did i miss something? Can i specify that on the command line? I ran: $ bzcat planet-110316.osm.bz2 | src/osmosis-0.34+ds1/bin/osmosis --read-xml file=- --write-pgsql host=localhost user=osm password=Shut up, Ted. Here's the log so far. Mar 29, 2011 11:11:43 PM org.openstreetmap.osmosis.core.Osmosis run INFO: Osmosis Version 0.34 log4j:WARN No appenders could be found for logger (org.java.plugin.ObjectFactory). log4j:WARN Please initialize the log4j system properly. Mar 29, 2011 11:11:44 PM org.openstreetmap.osmosis.core.Osmosis run INFO: Preparing pipeline. Mar 29, 2011 11:11:44 PM org.openstreetmap.osmosis.core.Osmosis run INFO: Launching pipeline execution. Mar 29, 2011 11:11:44 PM org.openstreetmap.osmosis.core.Osmosis run INFO: Pipeline executing, waiting for completion. Sadly i did not configure logging correctly. According to pg_stat is currently running this statement, so it looks like it's mostly done. UPDATE ways SET bbox = (SELECT Envelope(Collect(geom)) FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id WHERE way_nodes.way_id = ways.id) Looks like a correlated subquery to me. Probably performing a nested loop. I've read in the mailing list that adding the bbox and linestring columns will make the import much longer. So does that mean 10 days or 100 days? I checked \d ways and i see idx_ways_bbox gist (bbox) and idx_ways_linestring gist (linestring). So either those indexes were created after UPDATE ways set bbox... or i see the database before the transaction started. I don't know if this is in a transaction or not. I can't find the BEGIN in the code. I do see setAutoCommit() and this appears to be called with false. Any tips? Thanks, Brian -- Brian DeRocher http://brian.derocher.org ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
Re: [osmosis-dev] Planet import
On Tue, Sep 28, 2010 at 10:25 AM, Nakor nakor@gmail.com wrote: On 09/24/2010 09:52 AM, Nakor wrote: Brett, I should have read the whole documentation first. I am using --write-pgsql . I'll let it continue (as I am now over 200 Gb) and see how it goes. Next time I need to import I will probably just import the US vs the whole planet. Thanks for your input it gives a rough idea. Thanks, N. It finished in 7 days and 17hours. The DB is 260Gb. Note that some speed loss is probably due to the fact that the only disk where I had space to store the DB is encrypted. Okay, cool. Which version of the schema are you using? Is it the version 5 schema that comes with Osmosis 0.36, or the version 6 schema that comes with Osmosis 0.37-SNAPSHOT? The version 6 schema takes much longer to import if you have the optional bbox or linestring columns on the ways table, but the query performance is much improved due to table CLUSTERing. Brett ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
Re: [osmosis-dev] Planet import
On 09/27/2010 08:42 PM, Brett Henderson wrote: On Tue, Sep 28, 2010 at 10:25 AM, Nakor nakor@gmail.com mailto:nakor@gmail.com wrote: On 09/24/2010 09:52 AM, Nakor wrote: Brett, I should have read the whole documentation first. I am using --write-pgsql . I'll let it continue (as I am now over 200 Gb) and see how it goes. Next time I need to import I will probably just import the US vs the whole planet. Thanks for your input it gives a rough idea. Thanks, N. It finished in 7 days and 17hours. The DB is 260Gb. Note that some speed loss is probably due to the fact that the only disk where I had space to store the DB is encrypted. Okay, cool. Which version of the schema are you using? Is it the version 5 schema that comes with Osmosis 0.36, or the version 6 schema that comes with Osmosis 0.37-SNAPSHOT? The version 6 schema takes much longer to import if you have the optional bbox or linestring columns on the ways table, but the query performance is much improved due to table CLUSTERing. Brett I am using 0.36 and have the bbox column activated. BTW is there a way to migrate an existing DB from a schema version to another or is the only route re-importing the whole base? Also what is the benefit of the linestring column? N. ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
[osmosis-dev] planet import time
Hello, how much time does an import of the whole OSM planet into a PostgreSQL/PostGIS database, including bbox and linestrings, require on modern hardware? Osmosis seems to get stuck at computing bounding boxes, i.e. this query: UPDATE ways SET bbox = (SELECT Envelope(Collect(geom)) FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id WHERE way_nodes.way_id = ways.id) How long will this query take? Is there a way to monitor its progress? Why isn't the content of the column just copied in instead of recomputing everything from scratch? Kind regards, Jens -- Dipl. Inf. Jens Lehmann Department of Computer Science, University of Leipzig Homepage: http://www.jens-lehmann.org GPG Key: http://jens-lehmann.org/jens_lehmann.asc ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
Re: [osmosis-dev] planet import time
Hello, Frederik Ramm schrieb: Jens, Jens Lehmann wrote: UPDATE ways SET bbox = (SELECT Envelope(Collect(geom)) FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id WHERE way_nodes.way_id = ways.id) How long will this query take? Is there a way to monitor its progress? Why isn't the content of the column just copied in instead of recomputing everything from scratch? Well you have to compute it somewhere because it is not in the planet. A solution would be to include it in the planet or provide it as a separate file, which can be loaded into the database. But you can use the osmosis --wp option enableBboxBuilder to have the boox built in Java and then copied into PostGIS rather than having it built in PostGIS. Doing it in Java is faster but I don't know by how much. See http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage#--write-pgsql_.28--wp.29 Thanks for this hint (and it is somewhat surprising that PostGIS is slower than a Java based solution). Can the long running bbox query be aborted/killed without resulting in a corrupt database or do I have to start the import from scratch for testing the Java based solution? I would still appreciate any hint on how long it actually takes to compute the bounding boxes. If it takes longer than a week on a 16 GB dual core machine, then I could stop the process immediately. Kind regards, Jens -- Dipl. Inf. Jens Lehmann Department of Computer Science, University of Leipzig Homepage: http://www.jens-lehmann.org GPG Key: http://jens-lehmann.org/jens_lehmann.asc ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
Re: [osmosis-dev] planet import time
Hi, Jens Lehmann wrote: A solution would be to include it in the planet or provide it as a separate file, which can be loaded into the database. Well it isn't even in our central database so it would have to be computed at the time we dump the planet file which already is an expensive enough operation ;-) Can the long running bbox query be aborted/killed without resulting in a corrupt database or do I have to start the import from scratch for testing the Java based solution? I'm not sure if Osmosis uses one transaction for the whole import or several. Sure you can kill and you will not have a corrupt database but not an usable one either I believe! Bye Frederik ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
