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.

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.

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.

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.

The output should be a layer of the 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.

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.

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