Exclude the geometry based on buffer from the bigger geometry

I have a multipolygon geometry in which polygon geometry is scattered. I want to exclude the geometry which is not part of the big geometry. For e.g. The below image is one region with one of the polygons is too far. I want to exclude that polygon based on buffer. What function could be used to do so?

Solution

You have to ST_Dump the MultiPolygon and iterate over the result set to filter out the polygons that do not interesect with a given geometry, e.g.

SELECT * FROM (SELECT (ST_Dump(geom)).* FROM mytable) t
WHERE ST_Contains('YOUR BUFFER GOES HERE',t.geom);

Demo: db<>fiddle

CREATE TABLE t (gid int, geom geometry(multipolygon,4326));
INSERT INTO t VALUES (1,'SRID=4326;MULTIPOLYGON(((30 20,45 40,10 40,30 20)),((15 5,40 10,10 20,5 10,15 5)),((-24.78 25.47,-19.14 22.38,-26.35 19.86,-24.78 25.47)))'::geometry);

The large BBOX around the two largest polygons depicts your buffer and is not a part of the MultiPolygon. I put it in the image for illustration purposes only.

The following query dumps the MultiPolygon, checks if each Polygon intersects with the given polygon/buffer and creates a new MultiPolygon or a Polygon, in case there is only one geometry left:

SELECT gid, ST_AsText(ST_Union(geom)) 
FROM (SELECT gid,(ST_Dump(geom)).* FROM t) j
WHERE ST_Contains('SRID=4326;POLYGON((0 44.58,52.38 45.02,52.99 2.46,1.23 0,0 44.58))',j.geom)
GROUP BY gid;

 gid |                                st_astext                                 
-----+--------------------------------------------------------------------------
   1 | MULTIPOLYGON(((15 5,5 10,10 20,40 10,15 5)),((30 20,10 40,45 40,30 20)))
(1 Zeile)