<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Brett<br>
<br>
There are two fixes. Both of them are working for more than a week
on my server with no errors and no new invalid geoms in the db.<br>
<br>
regarding the array_length - I was not aware of that, as I'm using
8.4 on all my machines. I will have a look how to change that.<br>
<br>
The performance issue - no, I made no tests, I can try to test it on
an other server, where I did not change osmosis to the modified
version. <br>
<br>
kirill<br>
<br>
On 18.04.2011 15:25, Brett Henderson wrote:
<blockquote
cite="mid:BANLkTikycsPOUvtMvDLr-tBAPg8R1nGcpA@mail.gmail.com"
type="cite">Hi Kirill,<br>
<br>
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:<br>
<ol>
<li>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?<br>
</li>
<li>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?<br>
</li>
</ol>
Brett<br>
<br>
<br>
<div class="gmail_quote">On Sat, Apr 9, 2011 at 9:57 PM, Kirill
Bestoujev <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:bestoujev@gmail.com">bestoujev@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt
0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left: 1ex;">
I do look at them - (select n.geom as geom, count(1).
<div>
<div class="h5"><br>
<br>
<div class="gmail_quote">2011/4/9 Jochen Topf <span
dir="ltr"><<a moz-do-not-send="true"
href="mailto:jochen@remote.org" target="_blank">jochen@remote.org</a>></span><br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt
0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left: 1ex;">
Sorry, that was a bit unclear. You can't just look at
the node ids, you have<br>
to look at the geometries of those nodes, too.<br>
<br>
Jochen<br>
<br>
On Sat, Apr 09, 2011 at 02:36:36PM +0400, Kirill
Bestoujev wrote:<br>
> Date: Sat, 9 Apr 2011 14:36:36 +0400<br>
<div>> From: Kirill Bestoujev <<a
moz-do-not-send="true"
href="mailto:bestoujev@gmail.com" target="_blank">bestoujev@gmail.com</a>><br>
</div>
> To: Jochen Topf <<a moz-do-not-send="true"
href="mailto:jochen@remote.org" target="_blank">jochen@remote.org</a>><br>
> Cc: <a moz-do-not-send="true"
href="mailto:osmosis-dev@openstreetmap.org"
target="_blank">osmosis-dev@openstreetmap.org</a><br>
<div>
<div>> Subject: Re: [osmosis-dev] one node ways<br>
><br>
> Why? Where do I have to look if not at the
nodes???<br>
><br>
> K.<br>
><br>
> 2011/4/9 Jochen Topf <<a
moz-do-not-send="true"
href="mailto:jochen@remote.org" target="_blank">jochen@remote.org</a>><br>
><br>
> > You can have several nodes on the same
position which could also lead to a<br>
> > broken geometry but will not be caught
be looking just at the nodes.<br>
> ><br>
> > Jochen<br>
> ><br>
> > On Sat, Apr 09, 2011 at 02:22:58PM
+0400, Kirill Bestoujev wrote:<br>
> > > Date: Sat, 9 Apr 2011 14:22:58
+0400<br>
> > > From: Kirill Bestoujev <<a
moz-do-not-send="true"
href="mailto:bestoujev@gmail.com"
target="_blank">bestoujev@gmail.com</a>><br>
> > > To: <a moz-do-not-send="true"
href="mailto:osmosis-dev@openstreetmap.org"
target="_blank">osmosis-dev@openstreetmap.org</a><br>
> > > Subject: Re: [osmosis-dev] one node
ways<br>
> > ><br>
> > > And in works!<br>
> > ><br>
> > > and (select count(1) from (select
n.geom as geom, count(1) as node_count<br>
> > > from way_nodes wn join nodes n on <a
moz-do-not-send="true" href="http://n.id"
target="_blank">n.id</a> = wn.node_id where
wn.way_id =<br>
> > > w.idgroup by 1) n_group) > 1<br>
> > ><br>
> > > added to the request below works
fine!<br>
> > ><br>
> > > I will look at it for a couple of
days to make sure it works in all<br>
> > cases,<br>
> > > but looks like it is a solution.<br>
> > ><br>
> > > What is the process of merging it
into osmosis distro?<br>
> > ><br>
> > > The pg_snapshot and pg_simple load
scripts should also be updated!<br>
> > ><br>
> > > Kirill<br>
> > ><br>
> > > 2011/4/9 Kirill Bestoujev <<a
moz-do-not-send="true"
href="mailto:bestoujev@gmail.com"
target="_blank">bestoujev@gmail.com</a>><br>
> > ><br>
> > > > Sure, I know this. But if I
create an indes with, for example, a<br>
> > st_within<br>
> > > > condition, than osmosis falls
already while replicating diffs...<br>
> > > ><br>
> > > > That's why I want to solve the
problem in osmosis itself, not in the<br>
> > db.<br>
> > > ><br>
> > > > Yesterday on Russian irc
channel we found a solution to add to<br>
> > NodeDao.java<br>
> > > > linesting update sql-command
the following line:<br>
> > > ><br>
> > > > and (select count(1) from
(select n.geom as geom, count(1) as<br>
> > node_count<br>
> > > > from way_nodes wn join nodes n
on <a moz-do-not-send="true" href="http://n.id"
target="_blank">n.id</a> = wn.node_id where
wn.way_id =<br>
> > w.idgroup by 1) n_group) > 1<br>
> > > ><br>
> > > > This solves the problem with
ways having only duplicate nodes in them.<br>
> > > ><br>
> > > > But the recompiled version of
osmosis falls on the following command:<br>
> > > ><br>
> > > > org.springframework.jdbc.<br>
> > > > UncategorizedSQLException:
PreparedStatementCallback; uncategorized<br>
> > > > SQLException for SQL [UPDATE
ways w SET linestring = ( SELECT<br>
> > > > MakeLine(c.geom) AS way_line
FROM ( SELECT n.geom AS geom FROM nodes n<br>
> > INNER<br>
> > > > JOIN way_nodes wn ON <a
moz-do-not-send="true" href="http://n.id"
target="_blank">n.id</a> = wn.node_id WHERE
(wn.way_id = <a moz-do-not-send="true"
href="http://w.id" target="_blank">w.id</a>) and<br>
> > > > array_length(nodes, 1) > 1
ORDER BY wn.sequence_id ) c ) WHERE <a
moz-do-not-send="true" href="http://w.id"
target="_blank">w.id</a> =<br>
> > ?];<br>
> > > > SQL state [XX000]; error code
[0]; ERROR: Exception in LWGEOM2GEOS;<br>
> > nested<br>
> > > > exception is
org.postgresql.util.PSQLException: ERROR:
Exception in<br>
> > > > LWGEOM2GEOS<br>
> > > ><br>
> > > > the added code - and
array_length(nodes, 1) > 1 - which I thought
was<br>
> > > > solving the problem does not
work OR there is an other problem - not<br>
> > one<br>
> > > > node ways.<br>
> > > ><br>
> > > > Kirill<br>
> > > ><br>
> > > ><br>
> > > > 2011/4/9 Jochen Topf <<a
moz-do-not-send="true"
href="mailto:jochen@remote.org" target="_blank">jochen@remote.org</a>><br>
> > > ><br>
> > > >> Hi!<br>
> > > >><br>
> > > >> This has worked for me: I
use the ST_isvalid() function in Postgis to<br>
> > find<br>
> > > >> out<br>
> > > >> if a geometry is valid. If
not, I either delete the way or set the<br>
> > > >> geometry to<br>
> > > >> NULL and then do all
further processing only on ways with geometry !=<br>
> > > >> NULL.<br>
> > > >><br>
> > > >> Jochen<br>
> > > >><br>
> > > >> On Fri, Apr 08, 2011 at
08:55:43PM +0400, Kirill Bestoujev wrote:<br>
> > > >> > Date: Fri, 8 Apr 2011
20:55:43 +0400<br>
> > > >> > From: Kirill
Bestoujev <<a moz-do-not-send="true"
href="mailto:bestoujev@gmail.com"
target="_blank">bestoujev@gmail.com</a>><br>
> > > >> > To: Igor Podolskiy
<<a moz-do-not-send="true"
href="mailto:igor.podolskiy@vwi-stuttgart.de"
target="_blank">igor.podolskiy@vwi-stuttgart.de</a>><br>
> > > >> > Cc: osmosis-dev <<a
moz-do-not-send="true"
href="mailto:osmosis-dev@openstreetmap.org"
target="_blank">osmosis-dev@openstreetmap.org</a>><br>
> > > >> > Subject: Re:
[osmosis-dev] one node ways<br>
> > > >> ><br>
> > > >> > Igor,<br>
> > > >> ><br>
> > > >> > the context is simple
- we have a mirror of the planet, we update if<br>
> > > >> with<br>
> > > >> > replication with
minute diffs.<br>
> > > >> ><br>
> > > >> > You may have a look
at way 35966868 to see the problem by yourself.<br>
> > > >> After<br>
> > > >> > creation of geom by
osmosis it looks like LINESTRING(2.2321042<br>
> > > >> > 48.7767191,2.2321042
48.7767191) which is not good.<br>
> > > >> ><br>
> > > >> ><br>
> > > >> ><br>
> > > >> > K.<br>
> > > >> ><br>
> > > >> > 2011/4/8 Igor
Podolskiy <<a moz-do-not-send="true"
href="mailto:igor.podolskiy@vwi-stuttgart.de"
target="_blank">igor.podolskiy@vwi-stuttgart.de</a>><br>
> > > >> ><br>
> > > >> > > Hi Kirill,<br>
> > > >> > ><br>
> > > >> > ><br>
> > > >> > > When a one node
way is present in the database and osmosis<br>
> > creates a<br>
> > > >> > >> linestring
geometry for it the resulting geometry is
something<br>
> > like<br>
> > > >> > >>
LINESTRING(139.386972 37.095865)<br>
> > > >> > >><br>
> > > >> > >> When we
process this linestring with any postgis function
it<br>
> > gives us<br>
> > > >> > >> an error -
the linestring must contain 0 or > 1 nodes.<br>
> > > >> > >><br>
> > > >> > ><br>
> > > >> > > I think I
understand the problem but I'm having a hard time<br>
> > > >> understanding<br>
> > > >> > > the context :)
Could you maybe provide a simple example and/or a<br>
> > > >> > > step-by-step
guide to reproduce this? Like the osmosis command<br>
> > line<br>
> > > >> you're<br>
> > > >> > > using, the kind
of data you're trying to put in the database -<br>
> > that<br>
> > > >> would be<br>
> > > >> > > very helpful.<br>
> > > >> > ><br>
> > > >> > > Regards<br>
> > > >> > > Igor<br>
> > > >> > ><br>
> > > >><br>
> > > >> >
_______________________________________________<br>
> > > >> > osmosis-dev mailing
list<br>
> > > >> > <a
moz-do-not-send="true"
href="mailto:osmosis-dev@openstreetmap.org"
target="_blank">osmosis-dev@openstreetmap.org</a><br>
> > > >> > <a
moz-do-not-send="true"
href="http://lists.openstreetmap.org/listinfo/osmosis-dev"
target="_blank">http://lists.openstreetmap.org/listinfo/osmosis-dev</a><br>
> > > >><br>
> > > >><br>
> > > >> --<br>
> > > >> Jochen Topf <a
moz-do-not-send="true"
href="mailto:jochen@remote.org" target="_blank">jochen@remote.org</a>
<a moz-do-not-send="true"
href="http://www.remote.org/jochen/+49-721-388298"
target="_blank">http://www.remote.org/jochen/+49-721-388298</a><br>
> > > >><br>
> > > >><br>
> > > ><br>
> ><br>
> > >
_______________________________________________<br>
> > > osmosis-dev mailing list<br>
> > > <a moz-do-not-send="true"
href="mailto:osmosis-dev@openstreetmap.org"
target="_blank">osmosis-dev@openstreetmap.org</a><br>
> > > <a moz-do-not-send="true"
href="http://lists.openstreetmap.org/listinfo/osmosis-dev"
target="_blank">http://lists.openstreetmap.org/listinfo/osmosis-dev</a><br>
> ><br>
> ><br>
> > --<br>
> > Jochen Topf <a moz-do-not-send="true"
href="mailto:jochen@remote.org" target="_blank">jochen@remote.org</a>
<a moz-do-not-send="true"
href="http://www.remote.org/jochen/"
target="_blank">http://www.remote.org/jochen/</a>
+49-721-388298<br>
> ><br>
> ><br>
<br>
</div>
</div>
--<br>
<div>
<div>Jochen Topf <a moz-do-not-send="true"
href="mailto:jochen@remote.org" target="_blank">jochen@remote.org</a>
<a moz-do-not-send="true"
href="http://www.remote.org/jochen/"
target="_blank">http://www.remote.org/jochen/</a>
+49-721-388298<br>
<br>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
<br>
_______________________________________________<br>
osmosis-dev mailing list<br>
<a moz-do-not-send="true"
href="mailto:osmosis-dev@openstreetmap.org">osmosis-dev@openstreetmap.org</a><br>
<a moz-do-not-send="true"
href="http://lists.openstreetmap.org/listinfo/osmosis-dev"
target="_blank">http://lists.openstreetmap.org/listinfo/osmosis-dev</a><br>
<br>
</blockquote>
</div>
<br>
</blockquote>
<br>
</body>
</html>