[postgis-users] segmentize lines with order
Rémi Cura
remi.cura at gmail.com
Wed Feb 26 05:13:06 PST 2014
Cool,
glad it works :-)
Cheers,
Rémi-C
2014-02-26 13:08 GMT+01:00 Pedro Costa <pedrocostaarma at sapo.pt>:
> 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>:
>
>> 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>:
>>
>>> 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>:
>>>
>>>> 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>:
>>>>
>>>>> 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
>>>>> >:
>>>>>
>>>>>> 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
>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://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
>>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://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
>>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://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
>>>
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://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
>>
>
>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://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/227b98d6/attachment.html>
More information about the postgis-users
mailing list