Search code examples
postgresqlpostgis

Select Intersect Polygon From Single Feature Layer

I have hundreds of polygon (circles) where some of the polygon intersected with each others. This polygon is come from single feature layer. What I am trying to do is to delete the intersected circles.

It is similar to this question: link, but those were using two different layer. In my case the intersection is from single feature layers.

Solution

  • If I understood your question right, you just need to either create a CTE or simple subquery.

    This might give you a good idea of how to solve your issue:

    CREATE TABLE t (id INTEGER, geom GEOMETRY);

INSERT INTO t VALUES
(1,'POLYGON((-4.54 54.30,-4.46 54.30,-4.46 54.29,-4.54 54.29,-4.54 54.30))'),
(2,'POLYGON((-4.66 54.16,-4.56 54.16,-4.56 54.14,-4.66 54.14,-4.66 54.16))'),
(3,'POLYGON((-4.60 54.19,-4.57 54.19,-4.57 54.15,-4.60 54.15,-4.60 54.19))'),
(4,'POLYGON((-4.40 54.40,-4.36 54.40,-4.36 54.38,-4.40 54.38,-4.40 54.40))');

    This data set contains 4 polygons in total and two of them overlap, as seen in the following picture:

    enter image description here

    Applying a CTE with a subquery might give you what you want, which is the non-overlapping polygons from the same table:

    SELECT id, ST_AsText(geom) FROM t
WHERE id NOT IN (
 WITH j AS (SELECT * FROM t)
  SELECT j.id
  FROM j
  JOIN t ON t.id <> j.id
  WHERE ST_Intersects(j.geom,t.geom) 
);

 id |                              st_astext                              
----+---------------------------------------------------------------------
  1 | POLYGON((-4.54 54.3,-4.46 54.3,-4.46 54.29,-4.54 54.29,-4.54 54.3))
  4 | POLYGON((-4.4 54.4,-4.36 54.4,-4.36 54.38,-4.4 54.38,-4.4 54.4))
(2 rows)

    enter image description here