How to identify the closest point in a single layer?

Question

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?

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),
    case
        when st_distance(t1.geometry, t2.geometry)=0 then 'Duplicate'
        when st_distance(t1.geometry, t2.geometry)<10 then 'Nearby'
    end as "class",
    t1.geometry
        
        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: