1

I tried it this way but I think using DWithin might be better for this situation. What would be the correct order to find duplicates which exist within 10km from each other?

SELECT n, ST_ClusterDBSCAN(geog::geometry, eps := .08, minpoints := 2) over () AS cid
from (
select *
from cities as t
inner join
    (select n dn
     from cities as t
     group by n
     having count(*) >= 2) dups 
  on dups.dn = t.n
order by t.n
) d

EDIT: I guess I would have to do something like this:

select * from (SELECT *
    from (
    select *, ROW_NUMBER() OVER(order by t.n) as rownum
    from cities as t
    inner join
        (select n dn
         from cities as t
         group by n
         having count(*) = 2
        ) dups 
      on dups.dn = t.n
    order by t.n
    ) d
    where mod(rownum,2) = 0) even, 
    (SELECT *
    from (
    select *, ROW_NUMBER() OVER(order by t.n) as rownum
    from cities as t
    inner join
        (select n dn
         from cities as t
         group by n
         having count(*) = 2
        ) dups 
      on dups.dn = t.n
    order by t.n
    ) d
    where mod(rownum,1) = 0
    ) odd
    where st_dwithin(even.geog,odd.geog,5000)

but this is confusing... maybe it's better to just to do DWithin first but I'm not sure how to do that.

2
  • Please add more context to your question. You have tried ST_ClusterDBSCAN to find duplicates, correct? Show us the select ST_DWITHIN statement you would use. If you find a duplicate, which one do you want to keep, and which one should be deleted, or do you want to delete both of them?
    – Michael
    Mar 12, 2019 at 5:27
  • I'd like to keep the one with the least null values and I'd like to copy the i column's value to the other row before deleting
    – jaksco
    Mar 12, 2019 at 5:48

1 Answer 1

1

If your goal is just to identify duplicate records in your data. Then you can use ST_dwithin function like this;

  SELECT col1
  FROM cities as c1 
      INNER JOIN cities c2
  ON ST_dWithin(geom,10000)
  WHERE c1.gid != c2.gid AND c1.col1 = c2.col2

i assumed your data is in projected coordinate system (Unit: meters) and has a unique gid column. The duplication is based on col1, it may be name or any other value which should be unique in 10km radius.

2
  • This works really well. If you then wanted to remove duplicates what would your next step be? I'm not used to seeing multiple records on a single row
    – jaksco
    Mar 12, 2019 at 6:06
  • oh.. I think I figured it out. I can just reference the other columns like c1 and c2 to do the other things that I need like choosing which one to delete etc
    – jaksco
    Mar 12, 2019 at 6:17

Your Answer

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

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