I initially drafted a workflow that breaks down a road network and categorizes each line segment as by compass direction such as NS (north-south), EW (east-west), NW (north-west), NE(north-east) The initial process was done in QGIS as with this previous question. Then to scale it and run the process on a larger data set I needed to use PostGIS. Using PostGIS I ran a work flow that
creates a data base -> enables PostGIS extension -> line explode equivalent from QGIS
--line explode CREATE TABLE public.roads_exp AS WITH segments AS ( SELECT gid, ST_MakeLine(lag((pt).geom, 1, NULL) OVER (PARTITION BY gid ORDER BY gid, (pt).path), (pt).geom) AS geom FROM (SELECT gid, ST_DumpPoints(geom) AS pt FROM public.roads) as dumps ) SELECT * FROM segments WHERE geom IS NOT NULL;
Then I added a unique row
ALTER TABLE public.roads_exp ADD COLUMN line_id SERIAL;
Then I tried to calculate the azimuth using guidence from this question:
CREATE VIEW attempt1 AS SELECT line_id, gid, roads_exp.geom, DEGREES(ST_Azimuth((dmp).geom, LEAD((dmp).geom) OVER(PARTITION BY gid ORDER BY (dmp).path))) AS az FROM roads_exp, LATERAL ST_DumpPoints(geom) AS dmp ;
Many of the azimuth values from both methods match, but some do not. I think the discrepancy must be with how the azimuth is being calculated on the points derived from the lines. in QGIS the azimuth is being calculated directly on each line segment. I do not fully understand the postGIS process described in the relevant question so I am not sure how to fix the code.