I am new to GIS.

I have multiple coordinates of places from Safegraph. I believe there are some duplicates in my file (they have different IDs, but they seem to be identical places based on their names and location), and want to find them.

I thought I can do it by finding the distance to the closest point. For example, if the distance between two points is less than 5 meters, I can suspect they are identical.

I tried using the distance matrix using same input and target layer, trying to find "2" nearest places. I assumed I have one point with distance zero (the identical coordinate) and the other nearest one (what I really want).

But I am getting some weird results--the minimum distance is not zero.

Is there a better way to do this?

  • Points "often" dont overlap exactly, that is why distance is used when finding almost duplicates instead of intersect. What is the unit of your coordinate system?
    – BERA
    Jul 29, 2022 at 7:50

1 Answer 1


You can use a Virtual Layer and join the table to itself where the distance between two points are less than some value (I use 10 m), and the point id is not the same. Export the result to a file or it is going to be slow.

Replace "naturereserve" with the name of your layer, and "id" with the name of your unique id column.

select row_number() over() as newid, t1.id, t2.id,
    st_distance(t1.geometry, t2.geometry),
        when st_distance(t1.geometry, t2.geometry)=0 then 'Duplicate'
        when st_distance(t1.geometry, t2.geometry)<10 then 'Nearby'
    end as "class",
        from "naturereserve" t1
left join "naturereserve" t2
on st_distance(t1.geometry, t2.geometry)<10
where t1.ogc_fid>t2.ogc_fid

I've got 2 points at the same location, distance is 0, and two pairs of points ~5 m apart:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.