[postgis-users] 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 <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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160914/280ac426/attachment.html>