I would like to create a multi-ring buffer around a stream network and add a default floating value to each each buffer ring. I currently have the following SQL query:
CREATE TABLE public.stream_buffer
AS
SELECT s.id AS id_streams, ST_Difference(
ST_Union(ST_Buffer(ST_transform(geom, 3005),50)),
ST_Union(ST_Buffer(ST_transform(geom, 3005),0))
)
AS geom
FROM public.streams s
GROUP BY s.id
UNION ALL
SELECT s.id AS id_streams, ST_Difference(
ST_Union(ST_Buffer(ST_transform(geom, 3005),100)),
ST_Union(ST_Buffer(ST_transform(geom, 3005),50))
)
AS geom
FROM public.streams s
GROUP BY s.id
UNION ALL
SELECT s.id AS id_streams, ST_Difference(
ST_Union(ST_Buffer(ST_transform(geom, 3005),200)),
ST_Union(ST_Buffer(ST_transform(geom, 3005),100))
)
AS geom
FROM public.streams s
GROUP BY s.id
UNION ALL
SELECT s.id AS id_streams, ST_Difference(
ST_Union(ST_Buffer(ST_transform(geom, 3005),300)),
ST_Union(ST_Buffer(ST_transform(geom, 3005),200))
)
AS geom
FROM public.streams s
GROUP BY s.id;
Resulting in the following mess of un-dissolved and valued buffers:
I have tried the following for a single buffer distance, but don't know how to connect various buffer distances, substract the difference and place a value in each buffered ring around stream.
CREATE TABLE stream_buffer_100 AS
SELECT
g.path[1] as id,
g.geom::geometry(Polygon, 3005) as geom
FROM
(SELECT
(ST_Dump(ST_UNION(ST_Buffer(st_transform(geom,3005), 100)))).*
FROM public.streams
) as g;
ALTER TABLE public.stream_buffer_100
ADD COLUMN vul float8;
UPDATE stream_buffer_100 set vul = 0.75;
Below is what I would like with a floating point value for each buffer ring: