[postgis-users] help with a query
Birgit Laggner
birgit.laggner at thuenen.de
Tue Sep 27 01:54:09 PDT 2016
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
> <birgit.laggner at thuenen.de <mailto: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 <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 <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/20160927/eb79112a/attachment.html>
More information about the postgis-users
mailing list