PostGIS azimuth of line segments

Question

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.

Answer 1

Since you have extracted each individual line segment, you can directly compute their azimuth using their start and end points

CREATE VIEW attempt2 AS
  SELECT line_id, gid, geom,
         DEGREES(ST_Azimuth(st_startpoint(geom), st_endpoint(geom)))
  FROM   roads_exp;