# How to detect duplicates and keep only one?

I've got a table with lots of points (200.000). Some of them are duplicates (not exactly but e.g. a metro station has eight entrances) and I want to keep only one of these metro entrances.

I am able to identify the duplicates with the following statement (with desc_string = Metro Station Name):

``````--table of just duplicates
create table dups as
select t1.* from ciss_poi as t1 join ciss_poi as t2
on st_dwithin(t1.geom,t2.geom,(0.5/111.111))
where t1.desc_string=t2.desc_string and t1.gid != t2.gid
``````

Then I this:

The picture shows two metro stations with total 16 points. My question now is how can I only keep one point of each metro station and delete the others? Maybe creating bounding boxes around each of the doubles would be a hint, but I do not know how to do it.

EDIT: Maybe this might another way of solution: Maybe one possibility is to draw bounding boxes around points where this point have the same name and that are in a maximum distance of 500 meters to each other. I guess I can do with `st_envelope` but I do not know how to write the SQL query.

• Are the points tagged with the metro station name? What does the `desc_string` attribute contain? Have you thought of using the Select Distinct method? This might work well since you've already set up a list of the duplicates grouped together. Sep 19, 2013 at 8:58
• @Get Spatial: the points are tagged with the station name in the [desc_sting] attribute. But what about points with the same [desc_string] but no duplicates because they are far away. How can I set the condition that it should select distinct [desc_string] from points that are within a certain range? Sep 19, 2013 at 9:19
• Maybe you could search the right distance to depreciate the coordinates in order to mingle thème and make a s'élancer distinct on it. Like "select distinct on ST_SnapToGrid(geom,10)" Sep 19, 2013 at 10:18
• Well, say the distance is [st_dwithin(t1.geom,t2.geom,(0.5/111.111)]. Sep 19, 2013 at 10:23
• The ST_SnapToGrid does not eliminate all of the duplicates. What about drawing a bounding box around all double-pairs and then select only of the points in the polygon? Sep 19, 2013 at 12:27

After pondering this all day, I think you are much closer to the answer than you think.

Your initial query showed all the points matching one station within a certain distance, but excluded the initial point. This also excluded all points with no duplicates in the vicinity. You did this with the `WHERE` clause of `t1.gid != t2.gid`.

Suppose you removed that from the where clause, and added the `SELECT DISTINCT ON` notation, as follows:

``````CREATE TABLE allunique AS
SELECT DISTINCT ON (t1.desc_string) t1.*
FROM ciss_poi AS t1
JOIN ciss_poi AS t2
ON ST_Dwithin(t1.geom,t2.geom,(0.5/111.111))
WHERE t1.desc_string=t2.desc_string
ORDER BY t1.desc_string, t1.gid
``````

Here is the help for the DISTINCT clause: SELECT DISTINCT

When you use this with the `ON` addition, and name the field, it groups by that selected field, and then returns the first record. The record that is returned can be controlled by using the `ORDER BY` parameter.

In this case, if we return the all the joined records within a certain distance, including the initial record, then order by the `gid`, it should return just the single one out of a group, but also the individual records with nothing in the vicinity.

• Thank you very much. This approach works! It would be even better if it would create a centroid of the doubles with the centroid's attributes the same as the double (like desc_string, etc.). But hey, at least I get rid of my duplicates. Thanks Sep 20, 2013 at 11:30
• Many thanks to both you and @hoge6b01 for posting this question and answer. 7 years later I came across this identical problem (duplicate station entrances) and this answer saved me hours of writing a Python script workaround Apr 29, 2020 at 2:36

Here's a possibility, using a slightly different approach. You can create a convex hull around each set of "duplicates" and find it's centroid to enter into a new POI table. Then UNION that query with all those POI that are NOT duplicates (not within 500 m of any other). So:

``````CREATE TABLE poi_no_dups (gid INTEGER, desc_string text, ...);

INSERT INTO poi_no_dups (gid, desc_string, geom)
SELECT t1.gid, t1.desc_string, ST_Centroid(ST_ConvexHull(ST_Collect(t1.geometry)))
FROM ciss_poi AS t1 JOIN ciss_poi AS t2
ON ST_DWithin(t1.geom,t2.geom,(0.5/111.111))
WHERE t1.desc_string=t2.desc_string and t1.gid != t2.gid
LIMIT 1
UNION
SELECT t1.gid, t1.desc_string, t1.geometry
FROM ciss_poi AS t1 JOIN ciss_poi AS t2
ON ST_DWithin(t1.geom,t2.geom,(0.5/111.111))=0
WHERE t1.desc_string != t2.desc_string and t1.gid != t2.gid
;
``````

In this way, when you have i.e. 4 entrances to a metro station, the final table will contain the centroid of the entrances, rather than a random one of the entrances.

Here's a revised answer, without requiring the UNION:

``````SELECT t1.desc_string,
ST_Centroid(ST_ConvexHull(ST_Collect(t1.geom))) as geom2
FROM ciss_poi AS t1
WHERE ST_Dwithin(t1.geom, geom2, (0.5/111.111))
GROUP BY t1.desc_string
``````
• It should rather be `UNION ALL` since you don't expect duplicates anywhere. Sep 19, 2013 at 19:18
• Thanks Micha, a very interesting approach. I especially like the fact of having a centroid as a new POI. But it still gives me back 4 metro stations. Sep 20, 2013 at 11:28
• Cna you test just the first SELECT statement (before the UNION) to be sure that you're getting one centroid for each cluster of near-by points? Sep 20, 2013 at 12:13
• I checked the first `SELECT` (had to add a `group by` on t1.gid) but it returns me the initial eight metro stations without any centroid POIs. Sep 20, 2013 at 13:14
• If you add `GROUP BY t1.gid` then indeed you'll get one row for every metro station entrance. They all should have the same geometry. Try doing the SELECT with AsText() surrounding the St_Centroid function to see the coordinate values. What I forgot in my first query was `LIMIT 1` so that for each cluster of "duplicate' points you're using only one. See the edit in the answer above. Sep 21, 2013 at 9:34