<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, 17 Feb 2022 at 18:10, Marcin Mionskowski <<a href="mailto:mionskowskimarcin@gmail.com">mionskowskimarcin@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="auto"><div dir="ltr"><div style="font-family:monospace">"The terms clockwise and counterclockwise can only be applied to a
rotational motion once a side of the rotational plane is specified, from
which the rotation is observed."</div><div style="font-family:monospace"><a href="https://en.wikipedia.org/wiki/Clockwise" rel="noreferrer" target="_blank">https://en.wikipedia.org/wiki/Clockwise</a></div><div style="font-family:monospace" dir="auto">So, you need some kind of rotation (direction change) and some reference. In case of a polygon (or a closed line representing boundary of a polygon) both are obvious -> boundary direction and interior of a polygon.</div><div style="font-family:monospace" dir="auto">In case of a line the reference part is missing.</div><div style="font-family:monospace"></div><div style="font-family:monospace">What seams reasonable to me is to:</div><div style="font-family:monospace">- divide LINESTRING into segments,</div><div style="font-family:monospace">- combine consecutive segments into pairs,<br></div><div style="font-family:monospace">- chose 1st segment as a reference, <br></div><div style="font-family:monospace">- calculate the angle between segments (with respect to its direction OFC) using ST_Angle.</div><div style="font-family:monospace">The problem is, that one LINESTRING can have different "orientations". The CW/CCW is a property of {segment pair|the middle vertex of a pair of segments}.</div><div style="font-family:monospace">You can also use some direction as a reference (north in your example), but it<span lang="en"><span><span> has the same disadvantages and <span lang="en"><span><span>gives no additional information other than the azimuth/azimuth class.</span></span></span></span></span></span> </div><div style="font-family:monospace"></div><div style="font-family:monospace"><br></div><div style="font-family:monospace">Anyway, the SQL could look like:</div><div style="font-family:monospace"><br></div><div style="font-family:monospace">with<br>a_line as (<br> select 1 gid,ST_geomfromtext('LINESTRING(0 0,1 1,2 0,3 1)') a_line<br> union all<br> select 2 gid,ST_geomfromtext('LINESTRING(0 0,0.5 1,2 0.5,3.5 1)') a_line<br> )<br>, points as (<br> select gid,(ST_DumpPoints(a_line)).* <br> from a_line<br> )<br>, segments as (<br> select p1.gid,p1.path||p2.path start_end_points,ST_MakeLine(p1.geom,p2.geom) seg_geom <br> from points p1, points p2 <br> where p1.gid=p2.gid and p1.path[1]+1=p2.path[1]<br> ) <br>select s1.gid<br> , s1.start_end_points||s2.start_end_points[2] points<br> , case floor(st_angle(s1.seg_geom,s2.seg_geom)/pi()) when 0 then 'CW' else 'CCW' end<br>from segments s1,segments s2<br>where s1.gid=s2.gid and s1.start_end_points[2]=s2.start_end_points[1];</div><div style="font-family:monospace"><br></div><div style="font-family:monospace"><span lang="en"><span><span>If you insist on using the azimuth, replace the part from ", segments as (" with:<br></span></span></span></div><div style="font-family:monospace"><br></div><div style="font-family:monospace"> select p1.gid,p1.path||p2.path start_end_points,ST_Azimuth(p1.geom,p2.geom)<br> from points p1, points p2 <br> where p1.gid=p2.gid and p1.path[1]+1=p2.path[1]</div><div style="font-family:monospace"><br></div><div style="font-family:monospace">You can use LEAD() to optimize performance, but the SQL above is more readable in my opinion (it explains the logic better, I think).</div><div style="font-family:monospace"><br></div></div></div></div><br></blockquote><div>This looks very interesting. Perhaps, if using ST_Azimuth, it can be used to do the following:</div><div><br></div><div>divide linestring into segments,</div><div>combine consecutive segments into pairs</div><div>chose previous segment as a reference</div><div>calculate the azimuth change (positive or negative)</div><div>calculate the accumulative change</div><div>If the accumulative change is greater than 180, the line is doing a U-turn</div><div>If the accumulative change is greater than 180, the line is doing a circular motion</div><div><br></div><div>Potentially, it can also be adapted for determine whether a line is doing zig-zagging.</div><div><br></div><div>Am I right?</div><div><br></div><div>Regards,</div><div><br></div><div>David</div></div></div>