I have a Spatialite database with points. From time to time now points are added. What would be the easiest way to remove duplicates based on the coordinates?

5 Answers 5


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

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

if points :

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

(not tested .....)

  • 2
    Thanks a that got me into the right direction, I solved it it with: DELETE FROM mytable2 WHERE geom IN (SELECT geom FROM mytable1);
    – johannes
    Mar 27, 2011 at 15:29

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 Spatialite but in PostGIS the constraint 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).


  • Thanks for your answer, I like the idea with constraints.
    – johannes
    Mar 27, 2011 at 15:30
   WHERE pkuid NOT IN (SELECT min(pkuid) --or max(pkuid)
                    FROM foo
                    GROUP BY geometry)

(taken from the answer of Denis Valeev here: https://stackoverflow.com/questions/3777633/delete-duplicate-rows-dont-delete-all-duplicate)


In my case the most efficient way is to use spatial index from the layer. With this query, I keep only 1 geometry for each overlapping feature. I've done the test with a TIN converted into Linestring.

   delete from tin_line_sp where ogc_fid not in ( 

   select min(s1.rowid) as id_to_keep from
   idx_tin_line_sp_geometry as s1,
   idx_tin_line_sp_geometry as s2

       s1.xmin = s2.xmin and 
       s1.xmax = s2.xmax and 
       s1.ymin = s2.ymin and 
       s1.ymax = s2.ymax

   group by s1.xmin,s1.xmax,s1.ymin,s1.ymax)

To understand correctly spatial indexes, here two query to convert spatial index into polygons.

    create table tin_line_sp_representation as 
    select PolygonFromText('POLYGON(('||
    xmin || ' ' || ymin || ',' ||
    xmax || ' ' || ymin || ',' || 
    xmax || ' ' || ymax || ',' || 
    xmin || ' ' || ymax || ',' || 
    xmin || ' ' || ymin || '))',25832) as geometry
    from idx_tin_line_sp_geometry;

On success, recover geometry column to be able to visualise into your favorite viewer:

    select RecoverGeometryColumn( 'tin_line_sp_representation','geometry', 25832 , 'POLYGON', 2 )

If you want to delete objects with identical geometry try something like:

    delete from layer as s1 
     where s1.geometry in 
      (select geometry from layer where column=attribute_you_want)
     and s1.column=attribute_you_dont_want

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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