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.
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. – Get Spatial Sep 19 '13 at 8:58