CODEWITHSUNDEEP
postgresqlduplicatespostgis
Stine Lynge Mathisen
Stine Lynge Mathisen

Reputation: 21

How do I remove several duplicate geometries?

Several of the same geometries appears in the database. I an output with only one object_id for every distinct geometry.

I have managed to get a result with two overlapping geometries. 

SELECT
  a.objekt_id,
  b.objekt_id,
  a.geometri
  b.geometri
from
  plandk.theme_pdk_tilslutningspligtomraade_vedtaget_v a,
  plandk.theme_pdk_tilslutningspligtomraade_vedtaget_v b
WHERE 
  ST_EQUALS(a.geometri, b.geometri)
AND
  a.objekt_id != b.objekt_id;

The result is a table showing a row for two overlapping geometries. Though sometimes there are three rows with six overlapping geometries. I want the result to have all these in one row.

Upvotes: 2

Views: 2473

Answers (1)

Grzegorz Grabek
Grzegorz Grabek

Reputation: 980

DELETE FROM plandk.theme_pdk_tilslutningspligtomraade_vedtaget_v t
 WHERE EXISTS (SELECT FROM plandk.theme_pdk_tilslutningspligtomraade_vedtaget_v t2
                WHERE t.objekt_id > t2.objekt_id
                  AND ST_EQUALS(a.geometri, b.geometri))

It will delete all doubled geometries except this with lowest id for each equal geometry group.

Upvotes: 1

Related Questions