Hi Kirill, Is the fix you've provided in defect 3650 still valid or is a more elaborate fix required? I have two concerns with the fix as it stands:
1. If I'm reading the SQL query correctly it uses the array_length function which was only introduced in PostgreSQL 8.4. Currently the code is compatible with PostgreSQL 8.3. Is there any way to make it PostgreSQL 8.3 compatible? 2. I'm hesitant to make too many changes in this space without some proof that diff processing performance isn't impacted. Have you taken performance measurements before and after the change? Brett On Sat, Apr 9, 2011 at 9:57 PM, Kirill Bestoujev <[email protected]>wrote: > I do look at them - (select n.geom as geom, count(1). > > > 2011/4/9 Jochen Topf <[email protected]> > >> 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 <[email protected]> >> > To: Jochen Topf <[email protected]> >> > Cc: [email protected] >> > 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 <[email protected]> >> > >> > > 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 <[email protected]> >> > > > To: [email protected] >> > > > 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 <[email protected]> >> > > > >> > > > > 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 <[email protected]> >> > > > > >> > > > >> 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 <[email protected]> >> > > > >> > To: Igor Podolskiy <[email protected]> >> > > > >> > Cc: osmosis-dev <[email protected]> >> > > > >> > 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 <[email protected]> >> > > > >> > >> > > > >> > > 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 >> > > > >> > [email protected] >> > > > >> > http://lists.openstreetmap.org/listinfo/osmosis-dev >> > > > >> >> > > > >> >> > > > >> -- >> > > > >> Jochen Topf [email protected] >> http://www.remote.org/jochen/+49-721-388298 >> > > > >> >> > > > >> >> > > > > >> > > >> > > > _______________________________________________ >> > > > osmosis-dev mailing list >> > > > [email protected] >> > > > http://lists.openstreetmap.org/listinfo/osmosis-dev >> > > >> > > >> > > -- >> > > Jochen Topf [email protected] >> > > http://www.remote.org/jochen/+49-721-388298 >> > > >> > > >> >> -- >> Jochen Topf [email protected] http://www.remote.org/jochen/ +49-721-388298 >> >> > > _______________________________________________ > osmosis-dev mailing list > [email protected] > http://lists.openstreetmap.org/listinfo/osmosis-dev > >
_______________________________________________ osmosis-dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/osmosis-dev
