TD1995 TD1995 - 1 year ago 84
SQL Question

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
SELECT st_buffer(l.geom, 20) FROM line l

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

Answer Source

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.

        st_buffer(l.geom, 100), 
        st_buffer(l.geom, 20)
    ) as torus
from line l
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download