Skip to content

Instantly share code, notes, and snippets.

@Hugheym

Hugheym/ave_angle_postgis.sql

Created May 17, 2019 08:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Hugheym/c4c46a6b7fa8dcda2abbfe45622a2d36 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Hugheym/c4c46a6b7fa8dcda2abbfe45622a2d36 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ave_angle_postgis.sql
SELECT objectid, COALESCE(AVG(angle), 0) tortuosity FROM
(
SELECT objectid,
ST_ANGLE(ST_MAKELINE(lag(point,2) over w, lag(point,1) over w), ST_MAKELINE((lag(point, 1) over w), point)) angle
FROM
( SELECT objectid, (points).geom point, (points).path[2] path FROM
(SELECT
objectid, ST_DumpPoints(shape) points FROM (SELECT * FROM map.road_link) t1
) t2
) t3
WINDOW w AS (PARTITION BY objectid ORDER BY path)
) t4 GROUP BY objectid
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment