I have a postgis point table with a large list of cities.
I need to find duplicates in it, but only if they are 10km close each other, which means that is the same city.
For example, I have 5 points called 'Eneby', and suppose that 4 of this are 10Km close each others, and the 5th is 300Km away, what will mean that is another Eneby town. The query in this case should only have to show 4 points.
So, I've made this query, but is not showing the expected result:
Select p1.id, p2.id, * from points as p1, points as p2 where p1.name='Eneby' and st_dwithin(p1.geom,p2.geom,0.1, true) and p1.id=p2.id
The result is the same 5 points, no matter what distance (in degrees) I set. I'm trying with different distance values, but is always the same result.
Does anybody know if is there any mistake in this query?
I've been looking through this forum, and found some similar questions, but can't find exactly this problem.