Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

244
Views
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?

enter image description here

over 4 years ago · Santiago Trujillo
1 answers
Answer question

0

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);

enter image description here

  • 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)

enter image description here

Further reading:

  • ST_Union
over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!