[postgis-users] segmentize lines with order
Pedro Costa
pedrocostaarma at sapo.pt
Wed Feb 26 04:08:38 PST 2014
Thanks guys,
Remi, I'm already tried the second trick and doesn't work because when I
break the lines I get the original draw order.
But the second trick help me a lot because I do not know 'lag'. After
play with that function I have success:
http://pastebin.com/eC4PGq3Q
I have no sure if can appear another combination/sequence of order draw
that can cause a false reverse but, for now, I have no problems...
Em 25-02-2014 14:46, Rémi Cura escreveu:
> OK,
> first you may try this trick , which will be way faster to write :
>
> _get all start point of lines and all end points (without duplicates)
> _merge all the lines into one (I'm guessing they are all connected,
> and don't form loop)
> _cut the merged line with the points .
>
> The trick is that the merge function will take care of the order.
>
>
> Else, you can do it with another trick : (trick2, probably faster)
> considering you have a table with (id, line) , where id allow to order
> the line like in your drawing.
> create a table
> (id1,line, ST_Reverse(line) ) , (each line, you have original line and
> reversed line).
> Use windows function to get the previoust row (ie the next line)
> (according to id ASC order) (window function "lag")
> and a case statement : if the startpoint of your current row is the
> same as the ed point of the previous row, nothing, else , get
> ST_Reverse of current row
>
>
> --example with trick1
> WITH the_geom AS (
> SELECT *
> FROM ST_GeomFromText('MULTILINESTRING((0 0 ,5 4,
> 10 10),(99 99 , 54 96 ,10 10))') AS geom
> )
> ,f_points AS (
> SELECT ST_StartPoint((ST_Dump(geom)).geom) AS point
> FROM the_geom
> UNION
> SELECT ST_EndPoint((ST_Dump(geom)).geom) AS point
> FROM the_geom
> ),
> u_f_points AS (
> SELECT ST_Multi(point)AS points
> FROM f_points
> )
> ,merged_line AS (
> SELECT geom, st_astext(geom), ST_LineMerge(geom)
> AS merged_l
> FROM the_geom
> )
> ,cut_line AS (
> SELECT rc_Split_multi(merged_l,points, 0)
> FROM u_f_points,merged_line
> )
> SELECT *
> FROM cut_line
>
>
> 2014-02-25 15:02 GMT+01:00 Pedro Costa <pedrocostaarma at sapo.pt
> <mailto:pedrocostaarma at sapo.pt>>:
>
> With next line I mean next ' line geom' . I need to verify if
> endPoint of line 1 is the same as start point of line 2. My
> problem is when I have a lot of lines with 'draw order wrong'. See
> ss atached, i need to reverse line 2, 3 and 4.
>
> Do you undestand me now? Or not :-)?
>
> My query:
>
> SELECT CASE
> WHEN ST_DWithin(ST_EndPoint(r1.geom),ST_EndPoint(r2.geom),1)
> THEN 1
> WHEN
> ST_DWithin(ST_EndPoint(r2.geom),ST_StartPoint(r1.geom),1) THEN 1
> ELSE 0
> END AS reverse,
> r1.seq AS r1,
> r2.seq AS r2,
> r3.seq AS r3
> FROM path r1, path2 r2, path2 r3
> WHERE r1.seq = r2.seq + 1 AND r2.seq = r3.seq + 1 AND r1.seq <> 0
> AND r2.seq <> 0
>
>
> Em 25-02-2014 13:50, Rémi Cura escreveu:
>> I still don't understand what you are trying to do,
>> if you want to access the next row of a querry, you need to order
>> the row .
>> So what does "next line" mean? How is this information stored?
>>
>> It is possible to access other rows using windows function :
>> http://www.postgresql.org/docs/9.3/static/functions-window.html
>>
>> However I'm not certain you really need it.
>>
>> Could you provide a simple test case and an explanation of what
>> you are trying to do?
>>
>> (somehting like
>> create table my_test;
>> insert into my_test some_geometry_you_have_trouble_with;
>>
>> --describe the kind of result you want...
>>
>> --The problem is ..., I want to do ..., I tried ...
>> )
>>
>> Cheers,
>>
>> Rémi-C
>>
>>
>>
>> 2014-02-25 12:59 GMT+01:00 Pedro Costa <pedrocostaarma at sapo.pt
>> <mailto:pedrocostaarma at sapo.pt>>:
>>
>> I was trying CASE before but i doesn't know how to access
>> 'next line start point'.
>> Example:
>>
>>
>> SELECT
>> CASE
>> WHEN (ST_Intersects(ST_EndPoint(geom),(SELECT
>> ST_StartPoint(geom) FROM lines WHERE seq = seq+1))) <--- I
>> need to get next line geom
>> THEN 1
>> ELSE 0
>> END AS reverse
>> FROM lines
>>
>> Do you understand my problem?
>>
>> Em 25-02-2014 11:42, Rémi Cura escreveu:
>>> You don't need a stored procedure,
>>> you can simply use pure sql with the CASE WHEN ... THEN ..
>>> WHEN .. THEN .. .. END.
>>>
>>> doc :
>>> http://www.postgresql.org/docs/9.3/static/functions-conditional.html
>>>
>>> If you still need plpgsql :
>>> http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
>>>
>>> Cheers,
>>> Rémi-C
>>>
>>>
>>> 2014-02-25 12:17 GMT+01:00 Pedro Costa
>>> <pedrocostaarma at sapo.pt <mailto:pedrocostaarma at sapo.pt>>:
>>>
>>> thanks Remi.
>>> I'm tying to make a stored procedure to use st_reverse
>>> when draw order is wrong.
>>> Now, my difficult is in writing the loop.
>>> I can't find something like that to guide me...
>>>
>>>
>>>
>>> Em 25-02-2014 11:11, Rémi Cura escreveu:
>>>> You can use
>>>> http://postgis.refractions.net/docs/ST_Reverse.html
>>>>
>>>> Or simply ORDER BY DESC for your path
>>>>
>>>> Cheers,
>>>>
>>>> Rémi-C
>>>>
>>>>
>>>> 2014-02-25 11:49 GMT+01:00 Pedro Costa
>>>> <pedrocostaarma at sapo.pt <mailto:pedrocostaarma at sapo.pt>>:
>>>>
>>>> Hi Rémi,
>>>>
>>>> Thanks for your awnser.
>>>> My problem is that the draw order isn't equal in
>>>> all lines so the path of st_dumppoints sometimes
>>>> return ascend order and another times descend. I
>>>> need to make a loop to make a reverse...
>>>>
>>>> Em 25-02-2014 08:33, Rémi Cura escreveu:
>>>>> Hey,
>>>>> I don't understand your problem.
>>>>>
>>>>> If you have multilinestring, you need to break it
>>>>> to linestring with an id per multilinestring.
>>>>> If you have linestring, you just need to keep an
>>>>> id for each line and an id per point (given in path).
>>>>>
>>>>> Then you have several option to generate a ordered
>>>>> set of point (use the ORDER BY and the
>>>>> row_number() postgres function).
>>>>>
>>>>> If you give some detailled example maybe we could
>>>>> help better.
>>>>>
>>>>> Cheers,
>>>>> Rémi-C
>>>>>
>>>>>
>>>>> 2014-02-24 19:27 GMT+01:00 Stephen Woodbridge
>>>>> <woodbri at swoodbridge.com
>>>>> <mailto:woodbri at swoodbridge.com>>:
>>>>>
>>>>> On 2/24/2014 11:33 AM, Pedro Costa wrote:
>>>>>
>>>>> Hi guys,
>>>>>
>>>>> I'm trying to use lines from postgis to
>>>>> google maps android.
>>>>> To do that, I'm converting the lines to
>>>>> points with St_DumpPoints and,
>>>>> in gmaps, I make the lines. My problem is
>>>>> that I cannot create a correct
>>>>> sequence to order the points in android
>>>>> and so i get wrong lines (see ss
>>>>> atached). I'm already try to use
>>>>> st_dumpPoints path column and generate
>>>>> a serial but doesn't result.
>>>>>
>>>>> Anybody knows a solution to resolve that?
>>>>>
>>>>>
>>>>> You probably need to write function that
>>>>> re-orients you lines before you dump them to
>>>>> points. The algorithm is like this:
>>>>>
>>>>> 1. for the first edge, if the start point
>>>>> match the the start or end of the 2nd edge, if
>>>>> it does then st_reverse() the first edge.
>>>>>
>>>>> 2. for the rest of the edges, if the edge
>>>>> point of the current edge matches the end
>>>>> point of the last edge, then st_reverse() the
>>>>> current edge.
>>>>>
>>>>> You might be able to st_union all the edges
>>>>> into one large edge and that should create a
>>>>> new reorder edge.
>>>>>
>>>>> No you can dump them and the order will be
>>>>> correct.
>>>>>
>>>>> -Steve
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at lists.osgeo.org
>>>>> <mailto:postgis-users at lists.osgeo.org>
>>>>> http://lists.osgeo.org/cgi-bin/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/cgi-bin/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/cgi-bin/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/cgi-bin/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/cgi-bin/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/cgi-bin/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/cgi-bin/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/cgi-bin/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/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140226/10b70ecf/attachment.html>
More information about the postgis-users
mailing list