Re: [osmosis-dev] one node ways
Sure, I know this. But if I create an indes with, for example, a st_within condition, than osmosis falls already while replicating diffs... That's why I want to solve the problem in osmosis itself, not in the db. Yesterday on Russian irc channel we found a solution to add to NodeDao.java linesting update sql-command the following line: and (select count(1) from (select n.geom as geom, count(1) as node_count from way_nodes wn join nodes n on n.id = wn.node_id where wn.way_id = w.idgroup by 1) n_group) 1 This solves the problem with ways having only duplicate nodes in them. But the recompiled version of osmosis falls on the following command: org.springframework.jdbc. UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE ways w SET linestring = ( SELECT MakeLine(c.geom) AS way_line FROM ( SELECT n.geom AS geom FROM nodes n INNER JOIN way_nodes wn ON n.id = wn.node_id WHERE (wn.way_id = w.id) and array_length(nodes, 1) 1 ORDER BY wn.sequence_id ) c ) WHERE w.id = ?]; SQL state [XX000]; error code [0]; ERROR: Exception in LWGEOM2GEOS; nested exception is org.postgresql.util.PSQLException: ERROR: Exception in LWGEOM2GEOS the added code - and array_length(nodes, 1) 1 - which I thought was solving the problem does not work OR there is an other problem - not one node ways. Kirill 2011/4/9 Jochen Topf joc...@remote.org Hi! This has worked for me: I use the ST_isvalid() function in Postgis to find out if a geometry is valid. If not, I either delete the way or set the geometry to NULL and then do all further processing only on ways with geometry != NULL. Jochen On Fri, Apr 08, 2011 at 08:55:43PM +0400, Kirill Bestoujev wrote: Date: Fri, 8 Apr 2011 20:55:43 +0400 From: Kirill Bestoujev bestou...@gmail.com To: Igor Podolskiy igor.podols...@vwi-stuttgart.de Cc: osmosis-dev osmosis-dev@openstreetmap.org Subject: Re: [osmosis-dev] one node ways Igor, the context is simple - we have a mirror of the planet, we update if with replication with minute diffs. You may have a look at way 35966868 to see the problem by yourself. After creation of geom by osmosis it looks like LINESTRING(2.2321042 48.7767191,2.2321042 48.7767191) which is not good. K. 2011/4/8 Igor Podolskiy igor.podols...@vwi-stuttgart.de Hi Kirill, When a one node way is present in the database and osmosis creates a linestring geometry for it the resulting geometry is something like LINESTRING(139.386972 37.095865) When we process this linestring with any postgis function it gives us an error - the linestring must contain 0 or 1 nodes. I think I understand the problem but I'm having a hard time understanding the context :) Could you maybe provide a simple example and/or a step-by-step guide to reproduce this? Like the osmosis command line you're using, the kind of data you're trying to put in the database - that would be very helpful. Regards Igor ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev -- Jochen Topf joc...@remote.org http://www.remote.org/jochen/ +49-721-388298 ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
Re: [osmosis-dev] one node ways
And in works! and (select count(1) from (select n.geom as geom, count(1) as node_count from way_nodes wn join nodes n on n.id = wn.node_id where wn.way_id = w.idgroup by 1) n_group) 1 added to the request below works fine! I will look at it for a couple of days to make sure it works in all cases, but looks like it is a solution. What is the process of merging it into osmosis distro? The pg_snapshot and pg_simple load scripts should also be updated! Kirill 2011/4/9 Kirill Bestoujev bestou...@gmail.com Sure, I know this. But if I create an indes with, for example, a st_within condition, than osmosis falls already while replicating diffs... That's why I want to solve the problem in osmosis itself, not in the db. Yesterday on Russian irc channel we found a solution to add to NodeDao.java linesting update sql-command the following line: and (select count(1) from (select n.geom as geom, count(1) as node_count from way_nodes wn join nodes n on n.id = wn.node_id where wn.way_id = w.idgroup by 1) n_group) 1 This solves the problem with ways having only duplicate nodes in them. But the recompiled version of osmosis falls on the following command: org.springframework.jdbc. UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE ways w SET linestring = ( SELECT MakeLine(c.geom) AS way_line FROM ( SELECT n.geom AS geom FROM nodes n INNER JOIN way_nodes wn ON n.id = wn.node_id WHERE (wn.way_id = w.id) and array_length(nodes, 1) 1 ORDER BY wn.sequence_id ) c ) WHERE w.id = ?]; SQL state [XX000]; error code [0]; ERROR: Exception in LWGEOM2GEOS; nested exception is org.postgresql.util.PSQLException: ERROR: Exception in LWGEOM2GEOS the added code - and array_length(nodes, 1) 1 - which I thought was solving the problem does not work OR there is an other problem - not one node ways. Kirill 2011/4/9 Jochen Topf joc...@remote.org Hi! This has worked for me: I use the ST_isvalid() function in Postgis to find out if a geometry is valid. If not, I either delete the way or set the geometry to NULL and then do all further processing only on ways with geometry != NULL. Jochen On Fri, Apr 08, 2011 at 08:55:43PM +0400, Kirill Bestoujev wrote: Date: Fri, 8 Apr 2011 20:55:43 +0400 From: Kirill Bestoujev bestou...@gmail.com To: Igor Podolskiy igor.podols...@vwi-stuttgart.de Cc: osmosis-dev osmosis-dev@openstreetmap.org Subject: Re: [osmosis-dev] one node ways Igor, the context is simple - we have a mirror of the planet, we update if with replication with minute diffs. You may have a look at way 35966868 to see the problem by yourself. After creation of geom by osmosis it looks like LINESTRING(2.2321042 48.7767191,2.2321042 48.7767191) which is not good. K. 2011/4/8 Igor Podolskiy igor.podols...@vwi-stuttgart.de Hi Kirill, When a one node way is present in the database and osmosis creates a linestring geometry for it the resulting geometry is something like LINESTRING(139.386972 37.095865) When we process this linestring with any postgis function it gives us an error - the linestring must contain 0 or 1 nodes. I think I understand the problem but I'm having a hard time understanding the context :) Could you maybe provide a simple example and/or a step-by-step guide to reproduce this? Like the osmosis command line you're using, the kind of data you're trying to put in the database - that would be very helpful. Regards Igor ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev -- Jochen Topf joc...@remote.org http://www.remote.org/jochen/ +49-721-388298 ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
Re: [osmosis-dev] one node ways
You can have several nodes on the same position which could also lead to a broken geometry but will not be caught be looking just at the nodes. Jochen On Sat, Apr 09, 2011 at 02:22:58PM +0400, Kirill Bestoujev wrote: Date: Sat, 9 Apr 2011 14:22:58 +0400 From: Kirill Bestoujev bestou...@gmail.com To: osmosis-dev@openstreetmap.org Subject: Re: [osmosis-dev] one node ways And in works! and (select count(1) from (select n.geom as geom, count(1) as node_count from way_nodes wn join nodes n on n.id = wn.node_id where wn.way_id = w.idgroup by 1) n_group) 1 added to the request below works fine! I will look at it for a couple of days to make sure it works in all cases, but looks like it is a solution. What is the process of merging it into osmosis distro? The pg_snapshot and pg_simple load scripts should also be updated! Kirill 2011/4/9 Kirill Bestoujev bestou...@gmail.com Sure, I know this. But if I create an indes with, for example, a st_within condition, than osmosis falls already while replicating diffs... That's why I want to solve the problem in osmosis itself, not in the db. Yesterday on Russian irc channel we found a solution to add to NodeDao.java linesting update sql-command the following line: and (select count(1) from (select n.geom as geom, count(1) as node_count from way_nodes wn join nodes n on n.id = wn.node_id where wn.way_id = w.idgroup by 1) n_group) 1 This solves the problem with ways having only duplicate nodes in them. But the recompiled version of osmosis falls on the following command: org.springframework.jdbc. UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE ways w SET linestring = ( SELECT MakeLine(c.geom) AS way_line FROM ( SELECT n.geom AS geom FROM nodes n INNER JOIN way_nodes wn ON n.id = wn.node_id WHERE (wn.way_id = w.id) and array_length(nodes, 1) 1 ORDER BY wn.sequence_id ) c ) WHERE w.id = ?]; SQL state [XX000]; error code [0]; ERROR: Exception in LWGEOM2GEOS; nested exception is org.postgresql.util.PSQLException: ERROR: Exception in LWGEOM2GEOS the added code - and array_length(nodes, 1) 1 - which I thought was solving the problem does not work OR there is an other problem - not one node ways. Kirill 2011/4/9 Jochen Topf joc...@remote.org Hi! This has worked for me: I use the ST_isvalid() function in Postgis to find out if a geometry is valid. If not, I either delete the way or set the geometry to NULL and then do all further processing only on ways with geometry != NULL. Jochen On Fri, Apr 08, 2011 at 08:55:43PM +0400, Kirill Bestoujev wrote: Date: Fri, 8 Apr 2011 20:55:43 +0400 From: Kirill Bestoujev bestou...@gmail.com To: Igor Podolskiy igor.podols...@vwi-stuttgart.de Cc: osmosis-dev osmosis-dev@openstreetmap.org Subject: Re: [osmosis-dev] one node ways Igor, the context is simple - we have a mirror of the planet, we update if with replication with minute diffs. You may have a look at way 35966868 to see the problem by yourself. After creation of geom by osmosis it looks like LINESTRING(2.2321042 48.7767191,2.2321042 48.7767191) which is not good. K. 2011/4/8 Igor Podolskiy igor.podols...@vwi-stuttgart.de Hi Kirill, When a one node way is present in the database and osmosis creates a linestring geometry for it the resulting geometry is something like LINESTRING(139.386972 37.095865) When we process this linestring with any postgis function it gives us an error - the linestring must contain 0 or 1 nodes. I think I understand the problem but I'm having a hard time understanding the context :) Could you maybe provide a simple example and/or a step-by-step guide to reproduce this? Like the osmosis command line you're using, the kind of data you're trying to put in the database - that would be very helpful. Regards Igor ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev -- Jochen Topf joc...@remote.org http://www.remote.org/jochen/ +49-721-388298 ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev -- Jochen Topf joc...@remote.org http://www.remote.org/jochen/ +49-721-388298 ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
Re: [osmosis-dev] one node ways
Anyway, I'm now replicating 18 hours of diffs to the planet (with a st_within index on it - so any invalid geoms will be cought), for the moment 2 hours replicated fine. I will look at the process (all the logs are saved) - if everything goes will till Monday we can be sure that the problem is solved. Tipically we get 100-150 invalid geoms per day. K. 2011/4/9 Jochen Topf joc...@remote.org You can have several nodes on the same position which could also lead to a broken geometry but will not be caught be looking just at the nodes. Jochen On Sat, Apr 09, 2011 at 02:22:58PM +0400, Kirill Bestoujev wrote: Date: Sat, 9 Apr 2011 14:22:58 +0400 From: Kirill Bestoujev bestou...@gmail.com To: osmosis-dev@openstreetmap.org Subject: Re: [osmosis-dev] one node ways And in works! and (select count(1) from (select n.geom as geom, count(1) as node_count from way_nodes wn join nodes n on n.id = wn.node_id where wn.way_id = w.idgroup by 1) n_group) 1 added to the request below works fine! I will look at it for a couple of days to make sure it works in all cases, but looks like it is a solution. What is the process of merging it into osmosis distro? The pg_snapshot and pg_simple load scripts should also be updated! Kirill 2011/4/9 Kirill Bestoujev bestou...@gmail.com Sure, I know this. But if I create an indes with, for example, a st_within condition, than osmosis falls already while replicating diffs... That's why I want to solve the problem in osmosis itself, not in the db. Yesterday on Russian irc channel we found a solution to add to NodeDao.java linesting update sql-command the following line: and (select count(1) from (select n.geom as geom, count(1) as node_count from way_nodes wn join nodes n on n.id = wn.node_id where wn.way_id = w.idgroup by 1) n_group) 1 This solves the problem with ways having only duplicate nodes in them. But the recompiled version of osmosis falls on the following command: org.springframework.jdbc. UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE ways w SET linestring = ( SELECT MakeLine(c.geom) AS way_line FROM ( SELECT n.geom AS geom FROM nodes n INNER JOIN way_nodes wn ON n.id = wn.node_id WHERE (wn.way_id = w.id) and array_length(nodes, 1) 1 ORDER BY wn.sequence_id ) c ) WHERE w.id = ?]; SQL state [XX000]; error code [0]; ERROR: Exception in LWGEOM2GEOS; nested exception is org.postgresql.util.PSQLException: ERROR: Exception in LWGEOM2GEOS the added code - and array_length(nodes, 1) 1 - which I thought was solving the problem does not work OR there is an other problem - not one node ways. Kirill 2011/4/9 Jochen Topf joc...@remote.org Hi! This has worked for me: I use the ST_isvalid() function in Postgis to find out if a geometry is valid. If not, I either delete the way or set the geometry to NULL and then do all further processing only on ways with geometry != NULL. Jochen On Fri, Apr 08, 2011 at 08:55:43PM +0400, Kirill Bestoujev wrote: Date: Fri, 8 Apr 2011 20:55:43 +0400 From: Kirill Bestoujev bestou...@gmail.com To: Igor Podolskiy igor.podols...@vwi-stuttgart.de Cc: osmosis-dev osmosis-dev@openstreetmap.org Subject: Re: [osmosis-dev] one node ways Igor, the context is simple - we have a mirror of the planet, we update if with replication with minute diffs. You may have a look at way 35966868 to see the problem by yourself. After creation of geom by osmosis it looks like LINESTRING(2.2321042 48.7767191,2.2321042 48.7767191) which is not good. K. 2011/4/8 Igor Podolskiy igor.podols...@vwi-stuttgart.de Hi Kirill, When a one node way is present in the database and osmosis creates a linestring geometry for it the resulting geometry is something like LINESTRING(139.386972 37.095865) When we process this linestring with any postgis function it gives us an error - the linestring must contain 0 or 1 nodes. I think I understand the problem but I'm having a hard time understanding the context :) Could you maybe provide a simple example and/or a step-by-step guide to reproduce this? Like the osmosis command line you're using, the kind of data you're trying to put in the database - that would be very helpful. Regards Igor ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev -- Jochen Topf joc...@remote.org http://www.remote.org/jochen/+49-721-388298 ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org
Re: [osmosis-dev] one node ways
I do look at them - (select n.geom as geom, count(1). 2011/4/9 Jochen Topf joc...@remote.org Sorry, that was a bit unclear. You can't just look at the node ids, you have to look at the geometries of those nodes, too. Jochen On Sat, Apr 09, 2011 at 02:36:36PM +0400, Kirill Bestoujev wrote: Date: Sat, 9 Apr 2011 14:36:36 +0400 From: Kirill Bestoujev bestou...@gmail.com To: Jochen Topf joc...@remote.org Cc: osmosis-dev@openstreetmap.org Subject: Re: [osmosis-dev] one node ways Why? Where do I have to look if not at the nodes??? K. 2011/4/9 Jochen Topf joc...@remote.org You can have several nodes on the same position which could also lead to a broken geometry but will not be caught be looking just at the nodes. Jochen On Sat, Apr 09, 2011 at 02:22:58PM +0400, Kirill Bestoujev wrote: Date: Sat, 9 Apr 2011 14:22:58 +0400 From: Kirill Bestoujev bestou...@gmail.com To: osmosis-dev@openstreetmap.org Subject: Re: [osmosis-dev] one node ways And in works! and (select count(1) from (select n.geom as geom, count(1) as node_count from way_nodes wn join nodes n on n.id = wn.node_id where wn.way_id = w.idgroup by 1) n_group) 1 added to the request below works fine! I will look at it for a couple of days to make sure it works in all cases, but looks like it is a solution. What is the process of merging it into osmosis distro? The pg_snapshot and pg_simple load scripts should also be updated! Kirill 2011/4/9 Kirill Bestoujev bestou...@gmail.com Sure, I know this. But if I create an indes with, for example, a st_within condition, than osmosis falls already while replicating diffs... That's why I want to solve the problem in osmosis itself, not in the db. Yesterday on Russian irc channel we found a solution to add to NodeDao.java linesting update sql-command the following line: and (select count(1) from (select n.geom as geom, count(1) as node_count from way_nodes wn join nodes n on n.id = wn.node_id where wn.way_id = w.idgroup by 1) n_group) 1 This solves the problem with ways having only duplicate nodes in them. But the recompiled version of osmosis falls on the following command: org.springframework.jdbc. UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE ways w SET linestring = ( SELECT MakeLine(c.geom) AS way_line FROM ( SELECT n.geom AS geom FROM nodes n INNER JOIN way_nodes wn ON n.id = wn.node_id WHERE (wn.way_id = w.id) and array_length(nodes, 1) 1 ORDER BY wn.sequence_id ) c ) WHERE w.id = ?]; SQL state [XX000]; error code [0]; ERROR: Exception in LWGEOM2GEOS; nested exception is org.postgresql.util.PSQLException: ERROR: Exception in LWGEOM2GEOS the added code - and array_length(nodes, 1) 1 - which I thought was solving the problem does not work OR there is an other problem - not one node ways. Kirill 2011/4/9 Jochen Topf joc...@remote.org Hi! This has worked for me: I use the ST_isvalid() function in Postgis to find out if a geometry is valid. If not, I either delete the way or set the geometry to NULL and then do all further processing only on ways with geometry != NULL. Jochen On Fri, Apr 08, 2011 at 08:55:43PM +0400, Kirill Bestoujev wrote: Date: Fri, 8 Apr 2011 20:55:43 +0400 From: Kirill Bestoujev bestou...@gmail.com To: Igor Podolskiy igor.podols...@vwi-stuttgart.de Cc: osmosis-dev osmosis-dev@openstreetmap.org Subject: Re: [osmosis-dev] one node ways Igor, the context is simple - we have a mirror of the planet, we update if with replication with minute diffs. You may have a look at way 35966868 to see the problem by yourself. After creation of geom by osmosis it looks like LINESTRING(2.2321042 48.7767191,2.2321042 48.7767191) which is not good. K. 2011/4/8 Igor Podolskiy igor.podols...@vwi-stuttgart.de Hi Kirill, When a one node way is present in the database and osmosis creates a linestring geometry for it the resulting geometry is something like LINESTRING(139.386972 37.095865) When we process this linestring with any postgis function it gives us an error - the linestring must contain 0 or 1 nodes. I think I understand the problem but I'm having a hard time understanding the context :) Could you maybe provide a simple example and/or a step-by-step guide to reproduce this? Like the osmosis command line you're using, the kind of data you're trying to put in the database -
[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
