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).
--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, CASE 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 FROM (SELECT 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 SELECT CASE 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?