How to get several geometries with ST_Intersection?

Question

I have a layer of polygons (lulc) and I have a layer of points (sensors). I would like to have all the polygons inside buffer zones around the points as I can do in QGIS

So I have tried this request:

SELECT ST_Intersection(lulc.geom,ST_Buffer(sensors.geom,200,'quad_segs=50'))
    FROM sensors 
        JOIN lulc ON ST_Intersects (sensors.geom,lulc.geom)

But the result was this:

.

Although I am expecting a result like this:

I understand that ST_Intersection returns only one geometry but what should I do for having all the polygons inside the buffer zones? (sub-questions how does ST_Intersection choose the geometry that will be its result?)

Answer 1

ST_Intersection will return every intersections, it doesn't filter out any geometry.

In your query, you are selecting only the polygons that intersect the sensor point, and then, using theses polygons, you compute the intersection with the buffer of the sensor point.

Instead, you will want to join the polygons that are in the vicinity of the sensor points, and then compute the intersections:

SELECT ST_Intersection(lulc.geom,ST_Buffer(sensors.geom,200,'quad_segs=50'))
    FROM sensors 
        JOIN lulc ON ST_DWITHIN(sensors.geom,lulc.geom,200)