I try to remove the artifacts which occur when running the ST_Buffer function in PostGIS PostGIS 2.5.1 (See picture). For this case I wrote a function that should loop trough every segment (row) and erase (ST_Difference) the Polygons with the smaller areas (I assume the "normal-shaped") from the bigger ones (With additional artifacts). enter image description here

--CREATE TABLE line(name char(10), geom geometry);
--INSERT INTO line VALUES('line','LINESTRING(0 0,30 10,40 30,70 40)');

WITH firstblocks AS(

SELECT  row_number() Over() AS id, ST_Buffer(ST_Line_Substring(the_geom, 8*n/length,
    WHEN 8*(n+1) < length THEN 8*(n+1)/length
    ELSE 1
  END),4, 'endcap=flat join=mitre mitre_limit=3.0' ) As the_geom

  ST_LineMerge(geom) AS the_geom,
  ST_Length(geom) As length
  FROM line
  ) AS t

CROSS JOIN generate_series(0,CEIL(length/8)::int) AS n
WHERE n*8/length < 1


--Erase the smaller geometry from the bigger one
        WHEN ST_Area(the_geom) <= ST_Area(lead(the_geom) OVER(PARTITION BY id ORDER BY id))
        THEN ST_Difference(the_geom, lead(the_geom) OVER(PARTITION BY id ORDER BY id))
        ELSE ST_Difference(lead(the_geom) OVER(PARTITION BY id ORDER BY id), the_geom)
    END AS new_geom
FROM firstblocks;

The code works but loading the data as a new layer in the QGIS-Database Manager throws out the error: There was an error creating the SQL layer ... Somehow I am struggling with the conditional statements, while using a moving window approach. How can I combine them successfully?

  • 1
    What buffer artifacts? Is there a PostGIS or GEOS ticket for this?
    – dr_jts
    May 26 '20 at 14:33
  • 1
    Ok, I see the artifact now. Here's a reproducer: SELECT ST_Buffer( ST_LineSubstring('LINESTRING(0 0,30 10,40 30,70 40)'::geometry, 0.25, 0.375), 4, 'endcap=flat join=mitre mitre_limit=3.0');
    – dr_jts
    May 26 '20 at 15:02
  • That's caused by the interaction of the flat end cap and the buffer width. I haven't been able to figure out a strategy to get rid of those. Any idea what other systems produce in this situation?
    – dr_jts
    May 26 '20 at 15:04

Each row in firstblock has its own ID.

When you do the lead(..)(PARTITION BY ID ..), it "breaks" the data for each ID, so there is a single row and therefore no previous (lead) row.

In this example, you can simply order the window function to find the previous row:

        WHEN ST_Area(the_geom) <= ST_Area(lead(the_geom) OVER(ORDER BY id))
        THEN ST_Difference(the_geom, lead(the_geom) OVER(ORDER BY id))
        ELSE ST_Difference(lead(the_geom) OVER(ORDER BY id), the_geom)
    END AS new_geom
FROM firstblocks;

eventually you could partition by line_id, and such line_id should be repeated in firstblock for each block of a given line.

  • Thank you for your answer, this is working technically well, but for some reason single segments just dissappear from the geometry.
    – DrSnuggles
    May 27 '20 at 13:31
  • @DrSnuggles the entire st_difference logic is flawed. The 1st block has no previous (so the output of st_difference is null), and a block could return the geometry of the previous block. You can very likely end up with duplicates and holes (but this is a different question than making the lead() work)
    – JGH
    May 27 '20 at 14:54

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.