How to detect duplicates and keep only one?

Question

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.

Answer 1

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.

Answer 2

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, ...);
SELECT AddGeometryColumn('poi_no_dups', 'geom',<your SRID>,'POINT',2);

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