<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">With next line I mean next ' line geom'
. I need to verify if endPoint of line 1 is the same as start
point of line 2. My problem is when I have a lot of lines with
'draw order wrong'. See ss atached, i need to reverse line 2, 3
and 4.<br>
<br>
Do you undestand me now? Or not :-)?<br>
<br>
My query:<br>
<br>
SELECT CASE<br>
WHEN ST_DWithin(ST_EndPoint(r1.geom),ST_EndPoint(r2.geom),1)
THEN 1<br>
WHEN
ST_DWithin(ST_EndPoint(r2.geom),ST_StartPoint(r1.geom),1) THEN 1<br>
ELSE 0 <br>
END AS reverse,<br>
r1.seq AS r1,<br>
r2.seq AS r2,<br>
r3.seq AS r3<br>
FROM path r1, path2 r2, path2 r3<br>
WHERE r1.seq = r2.seq + 1 AND r2.seq = r3.seq + 1 AND r1.seq
<> 0 AND r2.seq <> 0<br>
<br>
<br>
Em 25-02-2014 13:50, Rémi Cura escreveu:<br>
</div>
<blockquote
cite="mid:CAJvUf_twk68f78k9-8bJjCWA9nk9LmdRF+ZZ-ZohrMB06u4f1g@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>
<div>I still don't understand what you are trying to
do,<br>
if you want to access the next row of a querry, you
need to order the row .<br>
</div>
<div>So what does "next line" mean? How is this
information stored?<br>
</div>
<div><br>
It is possible to access other rows using windows
function : <a moz-do-not-send="true"
href="http://www.postgresql.org/docs/9.3/static/functions-window.html">http://www.postgresql.org/docs/9.3/static/functions-window.html</a><br>
<br>
</div>
However I'm not certain you really need it.<br>
<br>
</div>
Could you provide a simple test case and an explanation
of what you are trying to do?<br>
<br>
</div>
(somehting like<br>
</div>
create table my_test;<br>
</div>
insert into my_test some_geometry_you_have_trouble_with;<br>
<br>
</div>
<div>--describe the kind of result you want...<br>
</div>
<div><br>
</div>
--The problem is ..., I want to do ..., I tried ...<br>
<div>
<div>
<div>
<div>)<br>
<br>
</div>
<div>Cheers,<br>
<br>
</div>
<div>Rémi-C<br>
</div>
<div>
<div>
<div>
<div><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">2014-02-25 12:59 GMT+01:00 Pedro Costa
<span dir="ltr"><<a moz-do-not-send="true"
href="mailto:pedrocostaarma@sapo.pt" target="_blank">pedrocostaarma@sapo.pt</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
<div>I was trying CASE before but i doesn't know how to
access 'next line start point'.<br>
Example:<br>
<br>
<br>
SELECT <br>
CASE<br>
WHEN (ST_Intersects(ST_EndPoint(geom),(SELECT
ST_StartPoint(geom) FROM lines WHERE seq = seq+1)))
<--- I need to get next line geom<br>
THEN 1<br>
ELSE 0 <br>
END AS reverse<br>
FROM lines<br>
<br>
Do you understand my problem?<br>
<br>
Em 25-02-2014 11:42, Rémi Cura escreveu:<br>
</div>
<div>
<div class="h5">
<blockquote type="cite">
<div dir="ltr">
<div>
<div>You don't need a stored procedure,<br>
</div>
you can simply use pure sql with the CASE WHEN
... THEN .. WHEN .. THEN .. .. END.<br>
<br>
</div>
doc : <a moz-do-not-send="true"
href="http://www.postgresql.org/docs/9.3/static/functions-conditional.html"
target="_blank">http://www.postgresql.org/docs/9.3/static/functions-conditional.html</a><br>
<div><br>
If you still need plpgsql : <a
moz-do-not-send="true"
href="http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING"
target="_blank">http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING</a><br>
<br>
Cheers,<br>
Rémi-C<br>
</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">2014-02-25 12:17
GMT+01:00 Pedro Costa <span dir="ltr"><<a
moz-do-not-send="true"
href="mailto:pedrocostaarma@sapo.pt"
target="_blank">pedrocostaarma@sapo.pt</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0
0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
<div>thanks Remi.<br>
I'm tying to make a stored procedure to
use st_reverse when draw order is wrong.<br>
Now, my difficult is in writing the loop.
<br>
I can't find something like that to guide
me...<br>
<br>
<br>
<br>
Em 25-02-2014 11:11, Rémi Cura escreveu:<br>
</div>
<div>
<div>
<blockquote type="cite">
<div dir="ltr">
<div>
<div>
<div>You can use <br>
<a moz-do-not-send="true"
href="http://postgis.refractions.net/docs/ST_Reverse.html"
target="_blank">http://postgis.refractions.net/docs/ST_Reverse.html</a><br>
<br>
</div>
Or simply ORDER BY DESC for your
path<br>
<br>
</div>
Cheers,<br>
<br>
</div>
Rémi-C<br>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">2014-02-25
11:49 GMT+01:00 Pedro Costa <span
dir="ltr"><<a
moz-do-not-send="true"
href="mailto:pedrocostaarma@sapo.pt"
target="_blank">pedrocostaarma@sapo.pt</a>></span>:<br>
<blockquote class="gmail_quote"
style="margin:0 0 0
.8ex;border-left:1px #ccc
solid;padding-left:1ex">
<div text="#000000"
bgcolor="#FFFFFF">
<div>Hi Rémi,<br>
<br>
Thanks for your awnser.<br>
My problem is that the draw
order isn't equal in all
lines so the path of
st_dumppoints sometimes
return ascend order and
another times descend. I
need to make a loop to make
a reverse...<br>
<br>
Em 25-02-2014 08:33, Rémi
Cura escreveu:<br>
</div>
<div>
<div>
<blockquote type="cite">
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>Hey,<br>
</div>
I don't
understand
your problem.<br>
<br>
</div>
If you have
multilinestring,
you need to
break it to
linestring with
an id per
multilinestring.<br>
</div>
If you have
linestring, you
just need to keep
an id for each
line and an id per
point (given in
path).<br>
<br>
</div>
Then you have
several option to
generate a ordered
set of point (use
the ORDER BY and the
row_number()
postgres function).<br>
<br>
</div>
If you give some
detailled example
maybe we could help
better.<br>
<br>
Cheers,<br>
Rémi-C<br>
</div>
<div class="gmail_extra"><br>
<br>
<div
class="gmail_quote">2014-02-24
19:27 GMT+01:00
Stephen Woodbridge <span
dir="ltr"><<a
moz-do-not-send="true"
href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>></span>:<br>
<blockquote
class="gmail_quote"
style="margin:0 0
0
.8ex;border-left:1px
#ccc
solid;padding-left:1ex">
<div>
<div>On
2/24/2014
11:33 AM,
Pedro Costa
wrote:<br>
<blockquote
class="gmail_quote"
style="margin:0
0 0
.8ex;border-left:1px
#ccc
solid;padding-left:1ex">
Hi guys,<br>
<br>
I'm trying to
use lines from
postgis to
google maps
android.<br>
To do that,
I'm converting
the lines to
points with
St_DumpPoints
and,<br>
in gmaps, I
make the
lines. My
problem is
that I cannot
create a
correct<br>
sequence to
order the
points in
android and so
i get wrong
lines (see ss<br>
atached). I'm
already try to
use
st_dumpPoints
path column
and generate<br>
a serial but
doesn't
result.<br>
<br>
Anybody knows
a solution to
resolve that?<br>
</blockquote>
<br>
</div>
</div>
You probably need
to write function
that re-orients
you lines before
you dump them to
points. The
algorithm is like
this:<br>
<br>
1. for the first
edge, if the start
point match the
the start or end
of the 2nd edge,
if it does then
st_reverse() the
first edge.<br>
<br>
2. for the rest of
the edges, if the
edge point of the
current edge
matches the end
point of the last
edge, then
st_reverse() the
current edge.<br>
<br>
You might be able
to st_union all
the edges into one
large edge and
that should create
a new reorder
edge.<br>
<br>
No you can dump
them and the order
will be correct.<br>
<br>
-Steve<br>
_______________________________________________<br>
postgis-users
mailing list<br>
<a
moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a
moz-do-not-send="true"
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
postgis-users mailing list
<a moz-do-not-send="true" href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a moz-do-not-send="true" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org"
target="_blank">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
postgis-users mailing list
<a moz-do-not-send="true" href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a moz-do-not-send="true" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org"
target="_blank">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
postgis-users mailing list
<a moz-do-not-send="true" href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a moz-do-not-send="true" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</body>
</html>