Detecting duplicated geometries in a PostGIS table

Hi there!

Today I want to make a small post and talk about another annoying problem: Duplicated Geometries!

How can you identify which geometries are duplicated in your table? In some cases we have duplicated geometries with different attributes, which one is the correct one?

To deal with this, a simple query can help you in this matter.

Concepts to keep:

  • PARTITION BY: Is a window function that receives a column, this means it will partition the column in groups with equal values (this is what we want for our “geom” column: duplicates!)
  • ROW_NUMBER(): gives a row number, inside a partition, counting from 1. This means that any number greater than 1 is a duplicate.

Here it is:

select * from (
SELECT id, ROW_NUMBER() OVER(PARTITION BY geom ORDER BY id asc) AS Row,
geom FROM ONLY your_schema.your_table
) dups where dups.Row > 1

Now you can check the duplicates and choose which ones you want to keep.

That’s it!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s