Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

Hints are greatly appreciated.

share|improve this question
add comment

3 Answers

up vote 6 down vote accepted

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 .....)

share|improve this answer
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); –  rengis Mar 27 '11 at 15:29
    
@jmsigner Please consider accepting an answer so this thread can be marked as answered. –  underdark Jun 11 '12 at 7:29
add comment
DELETE FROM foo
   WHERE pkuid NOT IN (SELECT min(pkuid) --or max(pkuid)
                    FROM foo
                    GROUP BY geometry)

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

share|improve this answer
add comment

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

share|improve this answer
    
Thanks for your answer, I like the idea with constraints. –  rengis Mar 27 '11 at 15:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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