8

After - I don't know what happend - all my entries in my PostGIS tables are doubled! I tried this to delete them but it does not delete any/all duplicates:

DELETE FROM planet_osm_point
       WHERE osm_id NOT IN (SELECT min(osm_id)
                        FROM planet_osm_point
                        GROUP BY osm_id)

or this:

DELETE FROM planet_osm_point
WHERE osm_id NOT IN (
    select max(dup.osm_id)
    from planet_osm_point as dup
    group by way);

EDIT:

I finally found an easy way, which is working in my case:

DELETE FROM planet_osm_point WHERE ctid NOT IN
(SELECT max(ctid) FROM planet_osm_point GROUP BY osm_id);

found on this page: http://technobytz.com/most-useful-postgresql-commands.html

  • 1
    Could you please provide the current planet_osm_point table structure? means type of columns. You can write a basic Python code to collect the selected columns, if having difficulty with the SQL functions. – Zia Dec 5 '14 at 13:41
  • Yes, that will work, if you have another id (ctid) that is not duplicated. I was assuming that everything was identical and duplicated twice. – John Powell Dec 5 '14 at 14:32
  • Sorry, but I didn't get this ctid approach. This column has been added manually after the duplication event? – Zia Dec 5 '14 at 14:41
  • 1
    "The column ‘ctid’ is a special column available for every tables but not visible unless specifically mentioned. The ctid column value is considered unique for every rows in a table. - "technobytz.com/most-useful-postgresql-commands.html – MAP Dec 7 '14 at 7:29
17

One way of doing this, is to use a window function and partition by geometry, so that each repeated geometry gets an id: 1, 2, 3, etc (or 1, 2) in your case, and then you just select from the table where the id = 1, to get a unique set of values (attributes and geometry) back, eg,

WITH unique_geoms (id, geom) as 
 (SELECT row_number() OVER (PARTITION BY geom) as id, geom FROM some_table)
SELECT geom 
FROM unique_geoms 
WHERE id=1;

Obviously, you would need to add the other osm columns in the select too, this is just for illustration, but this is basically like grouping by geometry and just selecting the first instance of each one.

As all the other attributes are presumably the same for each geometry pair, you would so something like this for all the other fields, including osm_id, and to actually create a new, unique table:

CREATE TABLE osm_unique AS
 WITH unique_geoms (id, osm_id, attr1, attr2,... attrn, geom) as 
  (SELECT row_number() OVER (PARTITION BY geom) as id, osm_id, attr1, attr2,... attrn, geom 
    FROM osm_planet_point)
 SELECT osm_id, attr1, attr2,... attrn, geom 
 FROM unique_geoms 
 WHERE id=1;

This might be quicker than deleting from an existing table, especially if there are lots of indexes in place.

EDIT. As @dbaston has pointed out, PARTITION BY geom uses the = operator, based on bounding boxes, so will eliminate geometries with shared borders. The above would be better written using PARTITION BY ST_AsBinary(geom) instead, ie,

(SELECT row_number() OVER (PARTITION BY ST_AsBinary(geom)) as id, osm_id, attr1, attr2,... attrn, geom 
    FROM osm_planet_point)....
  • Thanks. I have made a note. But for example let's consider this scenario where there is a point geom which is both a bus stop and a crossing (don't consider the OSM data). Then we will have two identical geom representing these two features. Using your approach will remove one of the feature. What I am saying is that how to solve this problem when there is no specific column to Partition By? – Zia Dec 5 '14 at 14:48
  • 1
    Hi Zia, then you partition by (geom, attribute), so that they both have to be the same in order to get the same id. In your example, the geom would be the same, the attribute not, so row_number() would return 1 for both. – John Powell Dec 5 '14 at 14:58
  • 1
    This currently identifies distinct geometries with a shared bounding box as duplicates (since PARTITION BY uses the = operator, which works on bounding-box equality). I'd suggest changing the above to PARTITION BY ST_AsBinary(geom) as a fix. – dbaston Apr 13 '16 at 19:50
  • I think you should accept this answer, or state how it doesn't answer the question. – John Powell Nov 8 '17 at 8:31
2

Here is another method I used to remove duplicates from a SSURGO soil data download. The downloaded shapefiles did not have a unique key, so a serial pk column was generated when I imported to PostGIS. There were a few overlaps in the data sets, and I inadvertently imported some records more than once while developing the import script.

The group by statement includes all columns in the table, excluding the primary key.

It will only delete one set of duplicate rows each time it's run, so if a row is repeated 4 times you will need to run this a minimum of 3 times. This is likely not as fast as John's solution, but works within an existing table. It also works when you don't have a unique id for each unique geometry (such as the osm_id in the original question).

I used a python script to repeat until duplicates were gone, and then ran a full vacuum. I think the script and vacuum each took about 30 minutes for a few hundred thousand duplicates from around 1.5 million records in 6 tables. Plenty good for a one-off. It went through the small tables very quickly.

DELETE FROM schema.table 
  WHERE primary_key IN
    (SELECT MAX(primary_key)
     FROM schema.table 
     GROUP BY ST_AsBinary(geom), col_1, col_2, col_etc
     HAVING COUNT(primary_key) > 1);

EDIT: modified SQL to avoid running multiple times based on @dbaston suggestion (below). I tried this query method on a large table (~1.5 million records, ~25,000 duplicate point rows), and after it ran for 45 minutes I canceled its execution. Running with the SQL above (using a smaller subquery from the HAVING COUNT) reduced each run to less than 30 seconds. After running 3 times, it was done with all duplicates. The SQL below should be OK for small tables.

DELETE FROM schema.table 
  WHERE primary_key NOT IN
    (SELECT MAX(primary_key)
     FROM schema.table 
     GROUP BY ST_AsBinary(geom), col_1, col_2, col_etc);
  • 1
    If you don't have a primary key, you can use the always available ctid column (see docs). – dbaston Nov 1 '16 at 10:39
  • 1
    You can avoid running this multiple times by checking for primary_key NOT IN (SELECT max(primary_key) .... – dbaston Nov 1 '16 at 10:41
  • @dbaston I made notes in the answer above. Removing the HAVING COUNT greatly increases the size of the subquery results, and therefore the number of comparisons the delete statement needs to make. I was surprised at how much longer execution was on a large table. – Nate Wanner Nov 1 '16 at 20:33
  • @NateWanner NOT EXISTS might give you some additional speed in this case. – Michal Zimmermann Nov 1 '16 at 20:51
  • @MichalZimmermann I'm not sure I follow you - both versions expect the subquery to return a result. – Nate Wanner Nov 1 '16 at 21:01

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.