[postgis-users] 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 <birgit.laggner at thuenen.de>
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 <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;
>>
>> END LOOP;
>>
>> END
>>
>> $$;
>>
>>
>> >
>> > On Tue, Sep 13, 2016 at 11:12 AM James Keener <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> 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
>> >         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
>> >
>>
>> > _______________________________________________
>> > postgis-users mailing list
>> > 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
>
>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://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/20160926/323f8e36/attachment.html>