PostGeoSQL/ST_BambooBuffer at main · CM-FOGS/PostGeoSQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
DROP FUNCTION IF EXISTS ST_BambooBuffer(
	geom GEOMETRY,
	sl integer,
	radius_buffer float)

CREATE OR REPLACE FUNCTION ST_BambooBuffer(
	geom GEOMETRY,
	sl integer,
	radius_buffer float)
RETURNS TABLE (geom GEOMETRY) AS
$BODY$
	WITH
		geodata AS (SELECT row_number() over() AS id, geom),
		linedivide AS (SELECT row_number() over() AS id, ST_LineSubstring(d.geom, substart, CASE WHEN subend > 1 THEN 1 ELSE subend END) geom FROM (SELECT id, geom, ST_Length(((geom)::geometry)) len, sl sublen FROM geodata) AS d CROSS JOIN LATERAL (SELECT i, (sublen * i)/len AS substart, (sublen * (i+1))/len AS subend FROM generate_series(0, floor(d.len/sublen)::integer) AS t(i) WHERE (sublen * i)/len <> 1.0) AS d2),
		tblb AS (SELECT id, ST_Buffer((geom), radius_buffer,'endcap=round join=round') as geom FROM linedivide),
		tblc_l AS (SELECT a.id, ST_LineMerge(ST_Difference(ST_ExteriorRing(a.geom), b.geom)) geom FROM tblb a LEFT JOIN tblb b ON (ST_Intersects(a.geom, b.geom) AND a.id < b.id)),
		tblcpoly_l AS (SELECT id, ST_MakePolygon(ST_AddPoint(geom, ST_StartPoint(geom))) geom FROM tblc_l),
		tbld_r AS (SELECT a.id, ST_LineMerge(ST_Difference(ST_ExteriorRing(a.geom), b.geom)) geom FROM tblb a LEFT JOIN tblb b ON (ST_Intersects(a.geom, b.geom) AND a.id > b.id)),
		tbldpoly_r AS (SELECT id, ST_MakePolygon(ST_AddPoint(geom, ST_StartPoint(geom))) geom FROM tbld_r),
		tble AS (SELECT (ST_Dump(ST_Intersection(a.geom, b.geom))).geom geom FROM tblcpoly_l a, tbldpoly_r b WHERE ST_Intersects (a.geom, b.geom) AND a.id=b.id)
		SELECT ST_Collect(geom) geom FROM tble;
$BODY$
LANGUAGE SQL

SELECT ST_BambooBuffer(ST_Collect(geom), 15,5) geom FROM <line_name_table>

WITH
	geodata AS (SELECT row_number() over() AS id, geom FROM <line_name_table>),
	linedivide AS (SELECT row_number() over() AS id, ST_LineSubstring(d.geom, substart, CASE WHEN subend > 1 THEN 1 ELSE subend END) geom
FROM (SELECT id, geom, ST_Length(((geom)::geometry)) len, 15 sublen FROM geodata) AS d
CROSS JOIN LATERAL (SELECT i, (sublen * i)/len AS substart, (sublen * (i+1))/len AS subend
FROM generate_series(0, floor(d.len/sublen)::integer) AS t(i) WHERE (sublen * i)/len <> 1.0) AS d2),
	tblb AS (SELECT id, ST_Buffer((geom), 5,'endcap=round join=round') as geom FROM linedivide),
	tblc_l AS (SELECT a.id, ST_LineMerge(ST_Difference(ST_ExteriorRing(a.geom), b.geom)) geom FROM tblb a LEFT JOIN tblb b ON (ST_Intersects(a.geom, b.geom) AND a.id < b.id)),
	tblcpoly_l AS (SELECT id, ST_MakePolygon(ST_AddPoint(geom, ST_StartPoint(geom))) geom FROM tblc_l),
	tbld_r AS (SELECT a.id, ST_LineMerge(ST_Difference(ST_ExteriorRing(a.geom), b.geom)) geom FROM tblb a LEFT JOIN tblb b ON (ST_Intersects(a.geom, b.geom) AND a.id > b.id)),
	tbldpoly_r AS (SELECT id, ST_MakePolygon(ST_AddPoint(geom, ST_StartPoint(geom))) geom FROM tbld_r)
	SELECT ST_Intersection(a.geom, b.geom) geom FROM tblcpoly_l a, tbldpoly_r b WHERE ST_Intersects (a.geom, b.geom) AND a.id=b.id;

important: experimental, slightly distorts the construction of individual elements in joints!