1

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: example

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: enter image description here

2
  • Your question is a bit unclear. Are you trying to clip the buffers so none of them overlap?
    – jbalk
    Commented Jul 11, 2023 at 23:11
  • @jbalk your sql query below causes the same result as my original sql query.
    – MJM
    Commented Jul 12, 2023 at 18:55

1 Answer 1

1

This is one method to make ring buffers. I create the first buffer, then use it to make the second buffer by taking the difference of the buffer around the first buffer and the geometry of the first buffer (subtract the first buffer from the new buffer to create the hole), then repeat for each ring:

with buffer1 as
  (select 0::numeric buffer_from, 50::numeric buffer_to, 
      st_buffer(st_transform(st_union(geom),3005),50) geom 
  from public.streams),
buffer2 as
  (select 50::numeric buffer_from, 100::numeric buffer_to, 
      st_difference(st_buffer(st_transform(st_union(geom),3005),100),st_buffer(st_transform(st_union(geom),3005),50)) geom 
  from public.streams),
buffer3 as 
  (select 100::numeric buffer_from, 200::numeric buffer_to, 
      st_difference(st_buffer(st_transform(st_union(geom),3005),200),
      st_buffer(st_transform(st_union(geom),3005),100)) geom 
  from public.streams),
buffer4 as
  (select 200::numeric buffer_from, 300::numeric buffer_to, 
      st_difference(st_buffer(st_transform(st_union(geom),3005),300),
      st_buffer(st_transform(st_union(geom),3005),200)) geom 
  from public.streams)
select * from buffer1
union
select * from buffer2
union
select * from buffer3
union
select * from buffer4;

This gives ring buffers:

all buffers buffer 2 and buffer 4 buffers dissolved on distance

6
  • This doesn't result in the desired dissolution of the buffered rings when multiple line segments are used.
    – MJM
    Commented Jul 12, 2023 at 18:57
  • I didn't understand the terminology you're using. I got it after looking at your picture again. Anyway, you just need to group by buffer distance. See my edits.
    – jbalk
    Commented Jul 12, 2023 at 20:41
  • 1
    Oops. I had the grouping in the wrong place. Edited again.
    – jbalk
    Commented Jul 12, 2023 at 21:27
  • 1
    You can't assign only 1 stream id to each buffer because you're dissolving the buffers on buffer distance, not stream id. You could aggregate a list of all the stream ids associated with each buffer. It's not a 1:1 relationship. There will be many stream ids for each buffer, excepting buffers for isolated streams outside of twice the buffer distance of any other stream, which would get one id.
    – jbalk
    Commented Jul 12, 2023 at 22:00
  • 1
    In your second screenshot, the buffers would have 14 associated stream ids. One buffer for each id (14 in total), dissolved into one buffer.
    – jbalk
    Commented Jul 12, 2023 at 22:10

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.