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.