[postgis-users] help with a query

[Birgit Laggner]

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 
> <godzilalalala at gmail.com <mailto:godzilalalala at gmail.com>> 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 <http://line.id>;
>
>     END LOOP;
>
>     END
>
>     $$;
>
>
>     >
>     > On Tue, Sep 13, 2016 at 11:12 AM James Keener <jim at jimkeener.com
>     <mailto:jim at jimkeener.com>> 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 <
>     > jon.malaver at shrewsburyma.gov
>     <mailto:jon.malaver at shrewsburyma.gov>> 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
>     > postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     > 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
>     >
>     > jon.malaver at shrewsburyma.gov <mailto:jon.malaver at shrewsburyma.gov>
>     >
>
>     > _______________________________________________
>     > postgis-users mailing list
>     > postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     > http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160920/641d0844/attachment.html>