<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 class="h5">
<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">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>