Search code examples
sqlpostgresqlpostgissql-except

Difference of two buffers

I want to create a buffer of 100m around a line. But I don't want to select the first 20m buffer. I tried something like this:

SELECT st_buffer(l.geom, 100) FROM line l
EXCEPT
SELECT st_buffer(l.geom, 20) FROM line l

But after checking it in QGIS it has not worked. How do I solve this?

Solution

  • EXCEPT isn't going to do it here. That's going to return any rows in the first query that aren't in the second. It won't modify the columns of those rows. The result of st_buffer is a single geometry which is a single row.

    Instead you build a torus, a donut, by carving the smaller circle out of the bigger one. I believe (I don't have PostGIS handy) you can do that by making two buffers and then subtracting the smaller from the larger with st_difference.

    select
    st_difference(
        st_buffer(l.geom, 100), 
        st_buffer(l.geom, 20)
    ) as torus
from line l