3

I'm trying to make this calculation in SQL with PostGIS:

azimuth_1 + (Sum rel_azimuth_2 to rel_azimuth_7)

rel_azimuth_x is always the angle beetween the actual subsegment_x and one subsegment_x-1 before.

enter image description here

3

As clarification of, and in addition to, my answer to your earlier question:

If you want to add up the relative angles between two segments and the SOURCE (that is what your image suggests), you can do so by using ST_Angle (note that there is a 3 point function signature not explicitly explained in the docs!) and a similar construct to my other answer (for completeness sake I will simply add up to that query):

SELECT  id,
        -- aggregates for absolute azimuths
        SUM(seg_azm) AS sum_azm,  -- SUM(seg_azm)::NUMERIC % 360.0 AS sum_azm
        AVG(seg_azm) AS avg_azm,
        MIN(seg_azm) AS min_azm,
        MAX(seg_azm) AS max_azm,
        -- aggregates for relative angles
        SUM(seg_ang) AS sum_ang,  -- SUM(seg_ang)::NUMERIC % 360.0 AS sum_ang
        AVG(seg_ang) AS avg_ang,
        MIN(seg_azm) AS min_ang,
        MAX(seg_azm) AS max_ang,
        -- azimuth between SOURCE & TARGET as reference
        (SELECT DEGREES(ST_Azimuth(ST_StartPoint(geom), ST_EndPoint(geom))) FROM <your_table> WHERE id = q.id) AS st_azm
FROM    (
  SELECT a.id,
         -- absolute azimuths of two-pair segments
         DEGREES(ST_Azimuth((dmp).geom, LEAD((dmp).geom) OVER(PARTITION BY id ORDER BY (dmp).path))) AS seg_azm,
         -- relative angles between vertex - source - next vertex
         DEGREES(ST_Angle((dmp).geom, ST_StartPoint(a.geom), LEAD((dmp).geom) OVER(PARTITION BY id ORDER BY (dmp).path))) AS seg_ang
  FROM   <your_table> AS a,
         LATERAL ST_DumpPoints(a.geom) AS dmp
) q
GROUP BY
        id
;

Notes:

  • the plain SUM of any of those angles are useless; I added the modulo calculation as comments to get the normalized azimuth and angle

  • I added the azimuth between the SOURCE and TARGET in the outer SELECT as reference; if you add up the initial (first segment) azimuth to the normalized angle you will get this value!

  • where is the "segs" table ? or what means the "segs" Relation »segs« doesn't exist Position: 610 --> yoe mean <your table> – Tibor Nov 1 '19 at 16:33
  • @Tibor ah, sorry, that's a leftover from my test; replace that with the same value (e.g. table name) you put as <your_table>. I corrected that in the answer. – ThingumaBob Nov 1 '19 at 16:40
  • should i also add a modolo calculation for the segments from the line in the angle calculation ? – Tibor Nov 1 '19 at 17:40
  • well, those angles will be treated as plain numbers when summed up, so you'll end up with a value far greater than 360. the modulo will give you the normalized angle, zero based (so, if the sum is 1100, you'll get 20; 'made a full turn 3 times, plus 20 degrees'). or do you mean adding another modulo operation?? no. – ThingumaBob Nov 1 '19 at 19:15
  • 1
    i take a mistake - i need the angels not from the start points source to the endpoints of each segments rather between each segment pairs 1-2, 2-3, 3-4, ... – Tibor Nov 1 '19 at 19:28
0

directed angle - Problems with group by....

i changed the code but i have a problem, if i build a group over the directedid i get a much more value for the directed angle.


If the directid is inside the select for directed angle i get this angles...

directedid  |path|angle_directed|angle_clockwise |
------------|----|--------------|----------------|
1000344165_2|{1} |9.845519416014|189.845519416014|
1000344165_2|{2} |              |                |
1000344165_2|{3} |              |                |

is the where outside or i build the group by directed id i get

directedid  |sum               |
------------|------------------|
1000344165_2|350.15448058398595|

here is the code...

select line.directedid , sum(line.angle_directed) , min(line.angle_directed) , max(line.angle_directed) from ( SELECT a.directedid , dmp.path , case when DEGREES(ST_Angle( st_transform((dmp).geom,31468), LEAD(st_transform((dmp).geom,31468)) OVER(PARTITION BY id ORDER BY (dmp).path) , LEAD(st_transform((dmp).geom,31468),2) OVER(PARTITION BY id ORDER BY (dmp).path)))::numeric % 360 <= 180 then abs(180 - DEGREES(ST_Angle( st_transform((dmp).geom,31468), LEAD(st_transform((dmp).geom,31468)) OVER(PARTITION BY id ORDER BY (dmp).path) , LEAD(st_transform((dmp).geom,31468),2) OVER(PARTITION BY id ORDER BY (dmp).path)))::numeric % 360) when DEGREES(ST_Angle( st_transform((dmp).geom,31468), LEAD(st_transform((dmp).geom,31468)) OVER(PARTITION BY id ORDER BY (dmp).path) , LEAD(st_transform((dmp).geom,31468),2) OVER(PARTITION BY id ORDER BY (dmp).path)))::numeric % 360 is null then 0 else DEGREES(ST_Angle( st_transform((dmp).geom,31468), LEAD(st_transform((dmp).geom,31468)) OVER(PARTITION BY id ORDER BY (dmp).path) , LEAD(st_transform((dmp).geom,31468),2) OVER(PARTITION BY id ORDER BY (dmp).path)))::numeric % 360 - 180 end angle_directed , DEGREES(ST_Angle( st_transform((dmp).geom,31468), LEAD(st_transform((dmp).geom,31468)) OVER(PARTITION BY id ORDER BY (dmp).path) , LEAD(st_transform((dmp).geom,31468),2) OVER(PARTITION BY id ORDER BY (dmp).path)))::numeric % 360 angle_clockwise --, st_transform((dmp).geom,31468) --, LEAD(st_transform((dmp).geom,31468)) OVER(PARTITION BY id ORDER BY (dmp).path) --, LEAD(st_transform((dmp).geom,31468),2) OVER(PARTITION BY id ORDER BY (dmp).path) FROM edge_table AS a, Lateral ST_DumpPoints(st_transform(a.geom,31468)) AS dmp - -where directedid = '1_1' --inside ) as line -- where directedid = '1_1' -- outside group by directedid

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.