5

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.

1 Answer 1

4

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;
1
  • thanks! To make it work I needed to add an extra parentheses. CREATE VIEW attempt2 AS SELECT line_id, gid, geom, DEGREES(ST_Azimuth(st_startpoint(geom), st_endpoint(geom))) FROM roads_exp; The start point and end point makes sense! Commented Apr 4, 2022 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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