[postgis-users] segmentize lines with order
Pedro Costa
pedrocostaarma at sapo.pt
Tue Feb 25 06:02:27 PST 2014
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
> 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/4a20dc33/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: ss_reverse.png
Type: image/png
Size: 10136 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140225/4a20dc33/attachment.png>
More information about the postgis-users
mailing list