Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

245
Visualizações
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 Respostas
Responde à pergunta

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda