<div dir="ltr"><div>Cool,<br></div>glad it works :-)<br><br>Cheers,<br>Rémi-C<br></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-02-26 13:08 GMT+01:00 Pedro Costa <span dir="ltr"><<a 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 guys,<br>
Remi, I'm already tried the second trick and doesn't work because
when I break the lines I get the original draw order.<br>
But the second trick help me a lot because I do not know 'lag'.
After play with that function I have success:<br>
<br>
<a href="http://pastebin.com/eC4PGq3Q" target="_blank">http://pastebin.com/eC4PGq3Q</a><br>
<br>
I have no sure if can appear another combination/sequence of order
draw that can cause a false reverse but, for now, I have no
problems...<br>
<br>
<br>
<br>
<br>
Em 25-02-2014 14:46, Rémi Cura escreveu:<br>
</div><div><div class="h5">
<blockquote type="cite">
<div dir="ltr">
<div>
<div>
<div>OK,<br>
</div>
first you may try this trick , which will be way faster to
write :<br>
</div>
<div><br>
_get all start point of lines and all end points (without
duplicates)<br>
</div>
_merge all the lines into one (I'm guessing they are all
connected, and don't form loop)<br>
_cut the merged line with the points .<br>
<br>
</div>
<div>The trick is that the merge function will take care of the
order.<br>
<br>
<br>
</div>
<div>Else, you can do it with another trick : (trick2, probably
faster)<br>
</div>
<div>considering you have a table with (id, line) , where id
allow to order the line like in your drawing.<br>
</div>
<div>create a table <br>
(id1,line, ST_Reverse(line) ) , (each line, you have original
line and reversed line).<br>
</div>
<div>Use windows function to get the previoust row (ie the next
line) (according to id ASC order) (window function "lag") <br>
</div>
<div>and a case statement : if the startpoint of your current
row is the same as the ed point of the previous row, nothing,
else , get ST_Reverse of current row<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>--example with trick1<br>
</div>
<div>WITH the_geom AS (<br>
SELECT *<br>
FROM ST_GeomFromText('MULTILINESTRING((0 0
,5 4, 10 10),(99 99 , 54 96 ,10 10))') AS geom<br>
)<br>
,f_points AS (<br>
SELECT ST_StartPoint((ST_Dump(geom)).geom)
AS point<br>
FROM the_geom<br>
UNION<br>
SELECT ST_EndPoint((ST_Dump(geom)).geom)
AS point<br>
FROM the_geom<br>
),<br>
u_f_points AS (<br>
SELECT ST_Multi(point)AS points<br>
FROM f_points<br>
)<br>
,merged_line AS (<br>
SELECT geom, st_astext(geom),
ST_LineMerge(geom) AS merged_l<br>
FROM the_geom<br>
)<br>
,cut_line AS (<br>
SELECT rc_Split_multi(merged_l,points, 0)<br>
FROM u_f_points,merged_line<br>
)<br>
SELECT *<br>
FROM cut_line</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">2014-02-25 15:02 GMT+01:00 Pedro Costa
<span dir="ltr"><<a 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>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>
<div>
<div>
<blockquote 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 href="http://www.postgresql.org/docs/9.3/static/functions-window.html" target="_blank">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 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>
<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 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 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 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 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 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 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a 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 href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a 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>