<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Jonatan, I am glad I could help. Have success with the tweaking!<br>
Regards, Birgit<br>
<br>
<div class="moz-cite-prefix">Am 26.09.2016 um 14:24 schrieb Jonatan
Malaver:<br>
</div>
<blockquote
cite="mid:CAN82OvO+SyX_EqDv=ikdiCxUEjZJCUzcNg=NVDquyymLmL=dCQ@mail.gmail.com"
type="cite">
<div dir="ltr">Hi Birgit, thank you so much!! your code put me on
the right track. I'm still tweaking it a bit since your code
assumes only 1 line is attached to the endpoint. However, the
endpoint of one line could break into 2 new starpoints.
<div>thanks,</div>
<div>jon</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr">On Tue, Sep 20, 2016 at 5:38 AM Birgit Laggner
<<a moz-do-not-send="true"
href="mailto:birgit.laggner@thuenen.de">birgit.laggner@thuenen.de</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000"> Hi Jonatan,<br>
<br>
based on the anonymous code block Leknín came up with, I
tried to adapt it to your problem:<br>
<br>
DO $$<br>
DECLARE c_line record; c_gid integer; c_geom geometry; line
record; gid integer; geom geometry; i integer; n integer;<br>
BEGIN<br>
<br>
EXECUTE 'SELECT count(*) FROM electric_line' INTO n;<br>
<br>
--initialize start<br>
EXECUTE 'SELECT gid, geom FROM electric_line WHERE gid =
3312' INTO c_line;<br>
<br>
c_gid := c_line.gid;<br>
c_geom := c_line.geom;<br>
i := 1;<br>
<br>
-- loop through lines following the flow direction<br>
LOOP<br>
EXECUTE 'SELECT gid, geom FROM electric_line<br>
WHERE
ST_DWithin(ST_EndPoint($1,ST_StartPoint(geom),0.01) OR<br>
ST_DWithin(ST_EndPoint($1),ST_EndPoint(geom),0.01)' INTO
line USING c_geom;<br>
<br>
gid := line.gid;<br>
geom := line.geom;<br>
<br>
--compare end point parent line with start point child
line and reverse line if necessary<br>
IF NOT ST_DWithin(ST_EndPoint(c_geom),
ST_StartPoint(geom),0.01) THEN<br>
EXECUTE<br>
'UPDATE electric_line SET geom =
ST_Reverse(geom) WHERE gid = '||line.gid;<br>
END IF;<br>
<br>
--take child line as parent line for next loop<br>
c_gid := gid;<br>
c_geom := geom;<br>
i := i + 1;<br>
<br>
EXIT WHEN i = n;<br>
<br>
END LOOP;<br>
<br>
END $$;<br>
<br>
<br>
Basically, you would start with your starting point for the
flow. Then you search for the next matching line and check
if the direction is ok. Otherwise you reverse the line. Then
you go into your next search loop and so on. The loop should
exit when all lines have been through the loop. I couldn't
test the code but hope you get the idea at least.<br>
<br>
Regards,<br>
<br>
Birgit</div>
<div bgcolor="#FFFFFF" text="#000000"><br>
<br>
<br>
<div>Am 14.09.2016 um 17:13 schrieb Jonatan Malaver:<br>
</div>
<blockquote type="cite">
<div dir="ltr">Hello again, I do not have parent line id.
All I have is a starting point from where the direction
should reference.</div>
<br>
<div class="gmail_quote">
<div dir="ltr">On Wed, Sep 14, 2016 at 9:09 AM Leknín
Řepánek <<a moz-do-not-send="true"
href="mailto:godzilalalala@gmail.com"
target="_blank">godzilalalala@gmail.com</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">On
Wed, Sep 14, 2016 at 12:09:23PM +0000, Jonatan Malaver
wrote:<br>
> the reason being is that I do a network analysis
by running the following<br>
> function:<br>
> WITH RECURSIVE flow(gid, geom) AS (<br>
> SELECT e.gid, e.geom FROM electric_line e,
transformers t WHERE ST_Distance<br>
> (t.geom,ST_StartPoint(e.geom)) <= 0.01 AND
t.gid=$1<br>
> UNION ALL<br>
> SELECT n.gid, n.geom<br>
> FROM electric_line n, flow f<br>
> WHERE
ST_Distance(ST_EndPoint(f.geom),ST_StartPoint(n.geom))
<= 0.01<br>
> )<br>
> The problem I have is that some of the lines
direction are in reversed. I'm<br>
> trying to correct them with referenced to the
first line. Otherwise, I will end<br>
> up changing hundreds of lines manually.<br>
Manually? No. There are many of possible ways how do
this by query. For<br>
example if you have in every line id of "parent line"
you can use<br>
anonymous block of code by something like this.<br>
DO $$<br>
DECLARE line record;<br>
BEGIN<br>
FOR line in SELECT lines from lines ORDER BY id LOOP<br>
IF NOT ST_Equal(ST_startpoint(line.geom) ,
(SELECT<br>
ST_EndPoint(geom) FROM lines WHERE id =
line.parent_line_id))<br>
THEN<br>
UPDATE lines SET geom =
ST_Reverse(geom) WHERE id =<br>
<a moz-do-not-send="true"
href="http://line.id" rel="noreferrer"
target="_blank">line.id</a>;<br>
<br>
END LOOP;<br>
<br>
END<br>
<br>
$$;<br>
<br>
<br>
><br>
> On Tue, Sep 13, 2016 at 11:12 AM James Keener
<<a moz-do-not-send="true"
href="mailto:jim@jimkeener.com" target="_blank">jim@jimkeener.com</a>>
wrote:<br>
><br>
> Depends on what you mean by direction. If you
want to grab the start and<br>
> end points (st_startpoint and st_endpoint)
and check their x and y (st_x<br>
> and st_y) for some condition (both less at
the end?) Then update the record<br>
> with the value of st_reverse.<br>
><br>
> I guess my other question is why it matters.<br>
><br>
> Jim<br>
><br>
> On September 13, 2016 8:31:07 AM EDT, Jonatan
Malaver <<br>
> <a moz-do-not-send="true"
href="mailto:jon.malaver@shrewsburyma.gov"
target="_blank">jon.malaver@shrewsburyma.gov</a>>
wrote:<br>
><br>
> Hello,<br>
><br>
> I'm trying to come up with a query
that would check the direction of<br>
> a line. If the end point is not the start
point of the next line to<br>
> update the line by reversing that line.
Can anyone give me pointers on<br>
> how to do it?<br>
><br>
> Thanks,<br>
> Jon<br>
><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/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
><br>
><br>
> --<br>
> Sent from my Android device with K-9 Mail.
Please excuse my brevity.<br>
><br>
> --<br>
><br>
> Thanks,<br>
><br>
><br>
> Jonatan Malaver<br>
><br>
> Assistant Engineer of Electrical and Cable
Operations<br>
><br>
> Shrewsbury Electric & Cable Operations<br>
><br>
> 100 Maple Avenue<br>
><br>
> Shrewsbury, MA 01545<br>
><br>
> Office: (508) 841-8610<br>
><br>
> Fax: (508) 842-9267<br>
><br>
> <a moz-do-not-send="true"
href="mailto:jon.malaver@shrewsburyma.gov"
target="_blank">jon.malaver@shrewsburyma.gov</a><br>
><br>
<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/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
<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/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote>
</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/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</div>
_______________________________________________<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/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote>
</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/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</body>
</html>