[postgis-users] help with a query

[Leknín Řepánek]

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