6

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: Metro duplicates

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.

  • 1
    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. – Get Spatial Sep 19 '13 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? – hoge6b01 Sep 19 '13 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)" – aurel_nc Sep 19 '13 at 10:18
  • Well, say the distance is [st_dwithin(t1.geom,t2.geom,(0.5/111.111)]. – hoge6b01 Sep 19 '13 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? – hoge6b01 Sep 19 '13 at 12:27
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.

  • 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 – hoge6b01 Sep 20 '13 at 11:30
1

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
  • It should rather be UNION ALL since you don't expect duplicates anywhere. – Jakub Kania Sep 19 '13 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. – hoge6b01 Sep 20 '13 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? – Micha Sep 20 '13 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. – hoge6b01 Sep 20 '13 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. – Micha Sep 21 '13 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.