Monday 22 January 2018

qgis - Delete duplicate geometry in postgis tables


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




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...