Finding duplicate records by matching name and nearby distance#

I wanted to find potentially duplicate records in my data, based on having the exact same name and being geographically located within 500 meters of each other.

This worked:

1
with potential_duplicates as (
2
  select
3
    a.id as one,
4
    b.id as two,
5
    ST_Distance(a.point, b.point) as distance_m
6
  from location a, location b
7
    where a.name = b.name
8
    and a.id > b.id
9
    and ST_Distance(a.point, b.point) < 500
10
)
11
select * from potential_duplicates

I’m using a CTE here because it makes it easy to further customize the output with an additional query.

A few tricks in here:

• Alias the location twice as a and b in order to join against itself to find duplicates
• The ST_Distance(a.point, b.point) < 500 clause returns locations within 500m of each other
• The a.id > b.id clause solves a problem I had with the first version of this query where each pairing was returned twice, with one and two swapped. By requiring a to have a higher id than b I avoid this problem entirely - and also prevent rows from matching themselves (where a.id = b.id).