Select duplicate geometries from a layer in QGIS

09 Dec 2020 ᛫ 3 min read

SHARE:

Removing duplicate geometries and features is fairly straightforward in QGIS but what if you want to select and extract the features with duplicates or the features and their duplicates? In this post, we use a query in the DB Manager and the ‘Select by location’ algorithm to do just that. You can use this method with a point, line, or polygon layer.

In this example, we will select and extract duplicate geometries/features from a polygon layer.

Check if there are duplicates using the Topology Checker

Here we have a polygon layer aptly named “With duplicates”. We know that it has duplicates because if we run the Topology checker on the layer with a Rule “must not have duplicates”, it returns 250 errors.

Topology checker must not have duplicates rule
Topology checker "must not have duplicates" rule
Topology checker showing 250 errors (with duplicates)
Topology checker showing 250 errors (with duplicates)

Use the DB Manager to run a query that lists all geometries with duplicates

Now that we know that we have duplicates, how can we select the features and their duplicates. Well, since we know that duplicate geometries technically have the same geometry, we can utilize a simple SQL query to list down the geometries with duplicates in our table.

Open the DB Manager via Database -> DB Manager.

Opening the QGIS DB Manager
Opening the QGIS DB Manager

Layers don’t need to be inside databses like PostGIS or a geopackage for it to be used in the DB Manager. Layers loaded in your current project can also be used by the DB Manager. You can see the fields and features of layers in the DB Manager. You can also create a query to run on your layer.

QGIS DB Manager
QGIS DB Manager

After opening a SQL Window, type the query below. This query selects the geometries that have duplicates in the layer. Click Execute to run the query.

select * from
(select *, count(geometry) as num_geom from "With duplicates" group by geometry) as a
where a.num_geom > 1

Note that the outputted table is not the table of duplicates but the table of geometries with duplicates. Each one of these geometries can have multiple duplicates. To load this table as a layer in QGIS, check Load as new layer and provide a Layer name (prefix). Since this layer has a geometry field, it can be loaded with geometry.

Running query on QGIS DB Manager
Running the query on QGIS DB Manager

The output should be a layer of the geometries with duplicates.

Layer of geometries with duplicates
Layer of geometries with duplicates

Use the Select by location algorithm to select all features in the original layer that are equal to the features with duplicates

Now that we have a layer of the geometries/features with duplicates, we can use the Select by location algorithm to find geometries/features in our original layer that are equal to the geometries/features with duplicates.

Select attribute by location
Select attribute by location
Using the Select by location to select the geometries/features with duplicates
Using the Select by location to select the geometries/features with duplicates

Now you have the geometries/features with duplicates selected. You can extract or save this selection by Right-cliking on the layer -> Export -> Save selected features as…. Your output/saved layer will be the duplicate features from the original layer.

Layer of duplicate geometries
Layer of duplicate geometries

You can watch the video below if you want to follow along.

SHARE:
comments powered by Disqus

You may also like:

Towards a spatial analysis of shooting in Philippine basketball (FOSS4G2021)

01 Oct 2021 ᛫ 1 min read

The opposite of free/libre and open source isn't commercial, it's proprietary

27 May 2021 ᛫ 2 min read

Towards a Spatial Analysis of Philippine Basketball: Applications in the UAAP MBT (Season 81) [Part 1]

02 May 2021 ᛫ 4 min read

Win and let win: On being unconventional, openness, and building communities

30 Apr 2021 ᛫ 1 min read

QGIS Styles based on HLURB Land Use Categories and Color Coding (CLUP Guidebook Vol 3, 2014)

15 Dec 2020 ᛫ 2 min read

Support BNHR

If you find my website or any of the materials I share useful, you can consider donating to the cause below.

Donate and support BNHR

BNHR

[email protected]

Creative Commons License
Except when explicitly stated otherwise, this work and its contents by Ben Hur S. Pintor is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
Other works (software, source code, etc.) referenced in this website are under their own respective licenses.
This site is powered by Jekyll and hosted on Github (view source)