[postgis-users] segmentize lines with order
Rémi Cura
remi.cura at gmail.com
Tue Feb 25 06:46:41 PST 2014
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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140225/8ad03a33/attachment.html>
More information about the postgis-users
mailing list