Exclude the geometry based on buffer from the bigger geometry-CodePudding

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?

CodePudding user response：

You have to

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)