help with a query

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

help with a query

Jonatan Malaver
Hello,

   I'm trying to come up with a query that would check the direction of a line. If the end point is not the start point of the next line to update the line by reversing that line. Can anyone give me pointers on how to do it?

Thanks,
Jon

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: help with a query

James Keener
Depends on what you mean by direction. If you want to grab the start and end points (st_startpoint and st_endpoint) and check their x and y (st_x and st_y) for some condition (both less at the end?) Then update the record with the value of st_reverse.

I guess my other question is why it matters.

Jim

On September 13, 2016 8:31:07 AM EDT, Jonatan Malaver <[hidden email]> wrote:
Hello,

   I'm trying to come up with a query that would check the direction of a line. If the end point is not the start point of the next line to update the line by reversing that line. Can anyone give me pointers on how to do it?

Thanks,
Jon



postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: help with a query

Jan Michálek
In reply to this post by Jonatan Malaver
ST_EndPoint, ST_StartPoint, ST_Reverse.

On Tue, Sep 13, 2016 at 12:31:07PM +0000, Jonatan Malaver wrote:
> Hello,
>
>    I'm trying to come up with a query that would check the direction of a line.
> If the end point is not the start point of the next line to update the line by
> reversing that line. Can anyone give me pointers on how to do it?
>
> Thanks,
> Jon

> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: help with a query

Jonatan Malaver
This is the query I have so far:
WITH RECURSIVE flow(gid, geom) AS (
    SELECT e.gid, e.geom FROM electric_line e WHERE e.gid = 3312
  UNION ALL
    SELECT n.gid, n.geom
    FROM electric_line n, flow f
    WHERE ST_DWithin(ST_EndPoint(f.geom),ST_StartPoint(n.geom),0.01)
  )
UPDATE electric_line SET geom = ST_Reverse(electric_line.geom) FROM flow WHERE electric_line.gid = flow.gid;

Is it possible to add an If statement that would only update if the EndPoint is not StartPoint of the next the line?

On Tue, Sep 13, 2016 at 8:37 AM Leknín Řepánek <[hidden email]> wrote:
ST_EndPoint, ST_StartPoint, ST_Reverse.

On Tue, Sep 13, 2016 at 12:31:07PM +0000, Jonatan Malaver wrote:
> Hello,
>
>    I'm trying to come up with a query that would check the direction of a line.
> If the end point is not the start point of the next line to update the line by
> reversing that line. Can anyone give me pointers on how to do it?
>
> Thanks,
> Jon

> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: help with a query

Jonatan Malaver
In reply to this post by James Keener
the reason being is that I do a network analysis by running the following function:
WITH RECURSIVE flow(gid, geom) AS (
    SELECT e.gid, e.geom FROM electric_line e, transformers t WHERE ST_Distance(t.geom,ST_StartPoint(e.geom)) <= 0.01 AND t.gid=$1
  UNION ALL
    SELECT n.gid, n.geom
    FROM electric_line n, flow f
    WHERE ST_Distance(ST_EndPoint(f.geom),ST_StartPoint(n.geom)) <= 0.01
  )
The problem I have is that some of the lines direction are in reversed. I'm trying to correct them with referenced to the first line. Otherwise, I will end up changing hundreds of lines manually.

On Tue, Sep 13, 2016 at 11:12 AM James Keener <[hidden email]> wrote:
Depends on what you mean by direction. If you want to grab the start and end points (st_startpoint and st_endpoint) and check their x and y (st_x and st_y) for some condition (both less at the end?) Then update the record with the value of st_reverse.

I guess my other question is why it matters.

Jim

On September 13, 2016 8:31:07 AM EDT, Jonatan Malaver <[hidden email]> wrote:
Hello,

   I'm trying to come up with a query that would check the direction of a line. If the end point is not the start point of the next line to update the line by reversing that line. Can anyone give me pointers on how to do it?

Thanks,
Jon


--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
--

Thanks,


Jonatan Malaver

Assistant Engineer of Electrical and Cable Operations

Shrewsbury Electric & Cable Operations

100 Maple Avenue

Shrewsbury, MA 01545

Office: (508) 841-8610

Fax: (508) 842-9267

[hidden email]


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: help with a query

Jan Michálek
On Wed, Sep 14, 2016 at 12:09:23PM +0000, Jonatan Malaver wrote:

> the reason being is that I do a network analysis by running the following
> function:
> WITH RECURSIVE flow(gid, geom) AS (
>     SELECT e.gid, e.geom FROM electric_line e, transformers t WHERE ST_Distance
> (t.geom,ST_StartPoint(e.geom)) <= 0.01 AND t.gid=$1
>   UNION ALL
>     SELECT n.gid, n.geom
>     FROM electric_line n, flow f
>     WHERE ST_Distance(ST_EndPoint(f.geom),ST_StartPoint(n.geom)) <= 0.01
>   )
> The problem I have is that some of the lines direction are in reversed. I'm
> trying to correct them with referenced to the first line. Otherwise, I will end
> up changing hundreds of lines manually.
Manually? No. There are many of possible ways how do this by query. For
example if you have in every line id of "parent line" you can use
anonymous block of code by something like this.
DO $$
DECLARE line record;
BEGIN
FOR line in SELECT lines from lines ORDER BY id LOOP
        IF NOT ST_Equal(ST_startpoint(line.geom) , (SELECT
        ST_EndPoint(geom) FROM lines WHERE id = line.parent_line_id))
        THEN
                UPDATE lines SET geom = ST_Reverse(geom) WHERE id =
                line.id;

END LOOP;

END

$$;


>
> On Tue, Sep 13, 2016 at 11:12 AM James Keener <[hidden email]> wrote:
>
>     Depends on what you mean by direction. If you want to grab the start and
>     end points (st_startpoint and st_endpoint) and check their x and y (st_x
>     and st_y) for some condition (both less at the end?) Then update the record
>     with the value of st_reverse.
>
>     I guess my other question is why it matters.
>
>     Jim
>
>     On September 13, 2016 8:31:07 AM EDT, Jonatan Malaver <
>     [hidden email]> wrote:
>
>         Hello,
>
>            I'm trying to come up with a query that would check the direction of
>         a line. If the end point is not the start point of the next line to
>         update the line by reversing that line. Can anyone give me pointers on
>         how to do it?
>
>         Thanks,
>         Jon
>
>
>         postgis-users mailing list
>         [hidden email]
>         http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>     --
>     Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
> --
>
> Thanks,
>
>
> Jonatan Malaver
>
> Assistant Engineer of Electrical and Cable Operations
>
> Shrewsbury Electric & Cable Operations
>
> 100 Maple Avenue
>
> Shrewsbury, MA 01545
>
> Office: (508) 841-8610
>
> Fax: (508) 842-9267
>
> [hidden email]
>

> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: help with a query

Jonatan Malaver
Hello again, I do not have parent line id. All I have is a starting point from where the direction should reference.

On Wed, Sep 14, 2016 at 9:09 AM Leknín Řepánek <[hidden email]> wrote:
On Wed, Sep 14, 2016 at 12:09:23PM +0000, Jonatan Malaver wrote:
> the reason being is that I do a network analysis by running the following
> function:
> WITH RECURSIVE flow(gid, geom) AS (
>     SELECT e.gid, e.geom FROM electric_line e, transformers t WHERE ST_Distance
> (t.geom,ST_StartPoint(e.geom)) <= 0.01 AND t.gid=$1
>   UNION ALL
>     SELECT n.gid, n.geom
>     FROM electric_line n, flow f
>     WHERE ST_Distance(ST_EndPoint(f.geom),ST_StartPoint(n.geom)) <= 0.01
>   )
> The problem I have is that some of the lines direction are in reversed. I'm
> trying to correct them with referenced to the first line. Otherwise, I will end
> up changing hundreds of lines manually.
Manually? No. There are many of possible ways how do this by query. For
example if you have in every line id of "parent line" you can use
anonymous block of code by something like this.
DO $$
DECLARE line record;
BEGIN
FOR line in SELECT lines from lines ORDER BY id LOOP
        IF NOT ST_Equal(ST_startpoint(line.geom) , (SELECT
        ST_EndPoint(geom) FROM lines WHERE id = line.parent_line_id))
        THEN
                UPDATE lines SET geom = ST_Reverse(geom) WHERE id =
                line.id;

END LOOP;

END

$$;


>
> On Tue, Sep 13, 2016 at 11:12 AM James Keener <[hidden email]> wrote:
>
>     Depends on what you mean by direction. If you want to grab the start and
>     end points (st_startpoint and st_endpoint) and check their x and y (st_x
>     and st_y) for some condition (both less at the end?) Then update the record
>     with the value of st_reverse.
>
>     I guess my other question is why it matters.
>
>     Jim
>
>     On September 13, 2016 8:31:07 AM EDT, Jonatan Malaver <
>     [hidden email]> wrote:
>
>         Hello,
>
>            I'm trying to come up with a query that would check the direction of
>         a line. If the end point is not the start point of the next line to
>         update the line by reversing that line. Can anyone give me pointers on
>         how to do it?
>
>         Thanks,
>         Jon
>
>
>         postgis-users mailing list
>         [hidden email]
>         http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>     --
>     Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
> --
>
> Thanks,
>
>
> Jonatan Malaver
>
> Assistant Engineer of Electrical and Cable Operations
>
> Shrewsbury Electric & Cable Operations
>
> 100 Maple Avenue
>
> Shrewsbury, MA 01545
>
> Office: (508) 841-8610
>
> Fax: (508) 842-9267
>
> [hidden email]
>

> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: help with a query

Birgit Laggner-2
Hi Jonatan,

based on the anonymous code block Leknín came up with, I tried to adapt it to your problem:

DO $$
DECLARE c_line record; c_gid integer; c_geom geometry; line record; gid integer; geom geometry; i integer; n integer;
BEGIN

EXECUTE 'SELECT count(*) FROM electric_line' INTO n;

--initialize start
EXECUTE 'SELECT gid, geom FROM electric_line WHERE gid = 3312' INTO c_line;

c_gid := c_line.gid;
c_geom := c_line.geom;
i := 1;

-- loop through lines following the flow direction
LOOP
    EXECUTE 'SELECT gid, geom FROM electric_line
             WHERE ST_DWithin(ST_EndPoint($1,ST_StartPoint(geom),0.01) OR
                   ST_DWithin(ST_EndPoint($1),ST_EndPoint(geom),0.01)' INTO line USING c_geom;

    gid := line.gid;
    geom := line.geom;
   
    --compare end point parent line with start point child line and reverse line if necessary
        IF NOT ST_DWithin(ST_EndPoint(c_geom), ST_StartPoint(geom),0.01) THEN
        EXECUTE
                'UPDATE electric_line SET geom = ST_Reverse(geom) WHERE gid = '||line.gid;
        END IF;

    --take child line as parent line for next loop
        c_gid := gid;
        c_geom := geom;
        i := i + 1;

    EXIT WHEN i = n;

END LOOP;

END $$;


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.

Regards,

Birgit



    
Am 14.09.2016 um 17:13 schrieb Jonatan Malaver:
Hello again, I do not have parent line id. All I have is a starting point from where the direction should reference.

On Wed, Sep 14, 2016 at 9:09 AM Leknín Řepánek <[hidden email]> wrote:
On Wed, Sep 14, 2016 at 12:09:23PM +0000, Jonatan Malaver wrote:
> the reason being is that I do a network analysis by running the following
> function:
> WITH RECURSIVE flow(gid, geom) AS (
>     SELECT e.gid, e.geom FROM electric_line e, transformers t WHERE ST_Distance
> (t.geom,ST_StartPoint(e.geom)) <= 0.01 AND t.gid=$1
>   UNION ALL
>     SELECT n.gid, n.geom
>     FROM electric_line n, flow f
>     WHERE ST_Distance(ST_EndPoint(f.geom),ST_StartPoint(n.geom)) <= 0.01
>   )
> The problem I have is that some of the lines direction are in reversed. I'm
> trying to correct them with referenced to the first line. Otherwise, I will end
> up changing hundreds of lines manually.
Manually? No. There are many of possible ways how do this by query. For
example if you have in every line id of "parent line" you can use
anonymous block of code by something like this.
DO $$
DECLARE line record;
BEGIN
FOR line in SELECT lines from lines ORDER BY id LOOP
        IF NOT ST_Equal(ST_startpoint(line.geom) , (SELECT
        ST_EndPoint(geom) FROM lines WHERE id = line.parent_line_id))
        THEN
                UPDATE lines SET geom = ST_Reverse(geom) WHERE id =
                line.id;

END LOOP;

END

$$;


>
> On Tue, Sep 13, 2016 at 11:12 AM James Keener <[hidden email]> wrote:
>
>     Depends on what you mean by direction. If you want to grab the start and
>     end points (st_startpoint and st_endpoint) and check their x and y (st_x
>     and st_y) for some condition (both less at the end?) Then update the record
>     with the value of st_reverse.
>
>     I guess my other question is why it matters.
>
>     Jim
>
>     On September 13, 2016 8:31:07 AM EDT, Jonatan Malaver <
>     [hidden email]> wrote:
>
>         Hello,
>
>            I'm trying to come up with a query that would check the direction of
>         a line. If the end point is not the start point of the next line to
>         update the line by reversing that line. Can anyone give me pointers on
>         how to do it?
>
>         Thanks,
>         Jon
>
>
>         postgis-users mailing list
>         [hidden email]
>         http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>     --
>     Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
> --
>
> Thanks,
>
>
> Jonatan Malaver
>
> Assistant Engineer of Electrical and Cable Operations
>
> Shrewsbury Electric & Cable Operations
>
> 100 Maple Avenue
>
> Shrewsbury, MA 01545
>
> Office: (508) 841-8610
>
> Fax: (508) 842-9267
>
> [hidden email]
>

> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: help with a query

Jonatan Malaver
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.
thanks,
jon

On Tue, Sep 20, 2016 at 5:38 AM Birgit Laggner <[hidden email]> wrote:
Hi Jonatan,

based on the anonymous code block Leknín came up with, I tried to adapt it to your problem:

DO $$
DECLARE c_line record; c_gid integer; c_geom geometry; line record; gid integer; geom geometry; i integer; n integer;
BEGIN

EXECUTE 'SELECT count(*) FROM electric_line' INTO n;

--initialize start
EXECUTE 'SELECT gid, geom FROM electric_line WHERE gid = 3312' INTO c_line;

c_gid := c_line.gid;
c_geom := c_line.geom;
i := 1;

-- loop through lines following the flow direction
LOOP
    EXECUTE 'SELECT gid, geom FROM electric_line
             WHERE ST_DWithin(ST_EndPoint($1,ST_StartPoint(geom),0.01) OR
                   ST_DWithin(ST_EndPoint($1),ST_EndPoint(geom),0.01)' INTO line USING c_geom;

    gid := line.gid;
    geom := line.geom;
   
    --compare end point parent line with start point child line and reverse line if necessary
        IF NOT ST_DWithin(ST_EndPoint(c_geom), ST_StartPoint(geom),0.01) THEN
        EXECUTE
                'UPDATE electric_line SET geom = ST_Reverse(geom) WHERE gid = '||line.gid;
        END IF;

    --take child line as parent line for next loop
        c_gid := gid;
        c_geom := geom;
        i := i + 1;

    EXIT WHEN i = n;

END LOOP;

END $$;


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.

Regards,

Birgit




    
Am 14.09.2016 um 17:13 schrieb Jonatan Malaver:
Hello again, I do not have parent line id. All I have is a starting point from where the direction should reference.

On Wed, Sep 14, 2016 at 9:09 AM Leknín Řepánek <[hidden email]> wrote:
On Wed, Sep 14, 2016 at 12:09:23PM +0000, Jonatan Malaver wrote:
> the reason being is that I do a network analysis by running the following
> function:
> WITH RECURSIVE flow(gid, geom) AS (
>     SELECT e.gid, e.geom FROM electric_line e, transformers t WHERE ST_Distance
> (t.geom,ST_StartPoint(e.geom)) <= 0.01 AND t.gid=$1
>   UNION ALL
>     SELECT n.gid, n.geom
>     FROM electric_line n, flow f
>     WHERE ST_Distance(ST_EndPoint(f.geom),ST_StartPoint(n.geom)) <= 0.01
>   )
> The problem I have is that some of the lines direction are in reversed. I'm
> trying to correct them with referenced to the first line. Otherwise, I will end
> up changing hundreds of lines manually.
Manually? No. There are many of possible ways how do this by query. For
example if you have in every line id of "parent line" you can use
anonymous block of code by something like this.
DO $$
DECLARE line record;
BEGIN
FOR line in SELECT lines from lines ORDER BY id LOOP
        IF NOT ST_Equal(ST_startpoint(line.geom) , (SELECT
        ST_EndPoint(geom) FROM lines WHERE id = line.parent_line_id))
        THEN
                UPDATE lines SET geom = ST_Reverse(geom) WHERE id =
                line.id;

END LOOP;

END

$$;


>
> On Tue, Sep 13, 2016 at 11:12 AM James Keener <[hidden email]> wrote:
>
>     Depends on what you mean by direction. If you want to grab the start and
>     end points (st_startpoint and st_endpoint) and check their x and y (st_x
>     and st_y) for some condition (both less at the end?) Then update the record
>     with the value of st_reverse.
>
>     I guess my other question is why it matters.
>
>     Jim
>
>     On September 13, 2016 8:31:07 AM EDT, Jonatan Malaver <
>     [hidden email]> wrote:
>
>         Hello,
>
>            I'm trying to come up with a query that would check the direction of
>         a line. If the end point is not the start point of the next line to
>         update the line by reversing that line. Can anyone give me pointers on
>         how to do it?
>
>         Thanks,
>         Jon
>
>
>         postgis-users mailing list
>         [hidden email]
>         http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>     --
>     Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
> --
>
> Thanks,
>
>
> Jonatan Malaver
>
> Assistant Engineer of Electrical and Cable Operations
>
> Shrewsbury Electric & Cable Operations
>
> 100 Maple Avenue
>
> Shrewsbury, MA 01545
>
> Office: (508) 841-8610
>
> Fax: (508) 842-9267
>
> [hidden email]
>

> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: help with a query

Birgit Laggner-2
Hi Jonatan, I am glad I could help. Have success with the tweaking!
Regards, Birgit

Am 26.09.2016 um 14:24 schrieb Jonatan Malaver:
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.
thanks,
jon

On Tue, Sep 20, 2016 at 5:38 AM Birgit Laggner <[hidden email]> wrote:
Hi Jonatan,

based on the anonymous code block Leknín came up with, I tried to adapt it to your problem:

DO $$
DECLARE c_line record; c_gid integer; c_geom geometry; line record; gid integer; geom geometry; i integer; n integer;
BEGIN

EXECUTE 'SELECT count(*) FROM electric_line' INTO n;

--initialize start
EXECUTE 'SELECT gid, geom FROM electric_line WHERE gid = 3312' INTO c_line;

c_gid := c_line.gid;
c_geom := c_line.geom;
i := 1;

-- loop through lines following the flow direction
LOOP
    EXECUTE 'SELECT gid, geom FROM electric_line
             WHERE ST_DWithin(ST_EndPoint($1,ST_StartPoint(geom),0.01) OR
                   ST_DWithin(ST_EndPoint($1),ST_EndPoint(geom),0.01)' INTO line USING c_geom;

    gid := line.gid;
    geom := line.geom;
   
    --compare end point parent line with start point child line and reverse line if necessary
        IF NOT ST_DWithin(ST_EndPoint(c_geom), ST_StartPoint(geom),0.01) THEN
        EXECUTE
                'UPDATE electric_line SET geom = ST_Reverse(geom) WHERE gid = '||line.gid;
        END IF;

    --take child line as parent line for next loop
        c_gid := gid;
        c_geom := geom;
        i := i + 1;

    EXIT WHEN i = n;

END LOOP;

END $$;


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.

Regards,

Birgit



Am 14.09.2016 um 17:13 schrieb Jonatan Malaver:
Hello again, I do not have parent line id. All I have is a starting point from where the direction should reference.

On Wed, Sep 14, 2016 at 9:09 AM Leknín Řepánek <[hidden email]> wrote:
On Wed, Sep 14, 2016 at 12:09:23PM +0000, Jonatan Malaver wrote:
> the reason being is that I do a network analysis by running the following
> function:
> WITH RECURSIVE flow(gid, geom) AS (
>     SELECT e.gid, e.geom FROM electric_line e, transformers t WHERE ST_Distance
> (t.geom,ST_StartPoint(e.geom)) <= 0.01 AND t.gid=$1
>   UNION ALL
>     SELECT n.gid, n.geom
>     FROM electric_line n, flow f
>     WHERE ST_Distance(ST_EndPoint(f.geom),ST_StartPoint(n.geom)) <= 0.01
>   )
> The problem I have is that some of the lines direction are in reversed. I'm
> trying to correct them with referenced to the first line. Otherwise, I will end
> up changing hundreds of lines manually.
Manually? No. There are many of possible ways how do this by query. For
example if you have in every line id of "parent line" you can use
anonymous block of code by something like this.
DO $$
DECLARE line record;
BEGIN
FOR line in SELECT lines from lines ORDER BY id LOOP
        IF NOT ST_Equal(ST_startpoint(line.geom) , (SELECT
        ST_EndPoint(geom) FROM lines WHERE id = line.parent_line_id))
        THEN
                UPDATE lines SET geom = ST_Reverse(geom) WHERE id =
                line.id;

END LOOP;

END

$$;


>
> On Tue, Sep 13, 2016 at 11:12 AM James Keener <[hidden email]> wrote:
>
>     Depends on what you mean by direction. If you want to grab the start and
>     end points (st_startpoint and st_endpoint) and check their x and y (st_x
>     and st_y) for some condition (both less at the end?) Then update the record
>     with the value of st_reverse.
>
>     I guess my other question is why it matters.
>
>     Jim
>
>     On September 13, 2016 8:31:07 AM EDT, Jonatan Malaver <
>     [hidden email]> wrote:
>
>         Hello,
>
>            I'm trying to come up with a query that would check the direction of
>         a line. If the end point is not the start point of the next line to
>         update the line by reversing that line. Can anyone give me pointers on
>         how to do it?
>
>         Thanks,
>         Jon
>
>
>         postgis-users mailing list
>         [hidden email]
>         http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>     --
>     Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
> --
>
> Thanks,
>
>
> Jonatan Malaver
>
> Assistant Engineer of Electrical and Cable Operations
>
> Shrewsbury Electric & Cable Operations
>
> 100 Maple Avenue
>
> Shrewsbury, MA 01545
>
> Office: (508) 841-8610
>
> Fax: (508) 842-9267
>
> [hidden email]
>

> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users