Remove duplicates from Spatialite

I think the easiest is to never let the duplicate in. add a unique constraint on the geometry field. I don't know how that will work in spatiallite but in postgis the constrint would compare the bounding boxes which will dive the wanted effect in the case of points.

if it doesn't matter which one of the duplicates to remove you could build a query that deletes all rows with id that is not found in a subquery which selects the distinct geometries. same here, safe with points but not other types since only the bbox will be compared not the actual geometry (if working the same way as postgis).

/Nicklas

Auto-joining the table would allow you to find duplicates rows. Something like that should work :

DELETE t1
FROM mytable t1, mytable t2
WHERE t1.the_geom = t2.the_geom

if points :

DELETE t1
FROM mytable t1, mytable t2
WHERE t1.x = t2.x
AND t1.y > t2.y

(not tested .....)