I'm trying to identify polygons/linestrings with duplicate points, using PostGIS.

I just want to tell whether the geometry has duplicate points, not remove the extra point.

I know there is ST_RemoveRepeatedpPoints, but as far as I can tell it only does something if the points are right after one another i.e.

ST_RemoveRepeatedPoints(st_geomfromtext('Polygon((0 0, 0 1,
                    0.5 1, --> 3. point
                    0.45 0.45, 0.5 0.5,  
                    0.5000000001 1, --> notice it is basically the same as 3. point
                    1 1,  1 0, 0 0))'));
-- Answer:   'Polygon((0 0, 0 1, 0.5 1, 0.45 0.45, 0.5 0.5,  0.5000000001 1,1 1,  1 0, 0 0))' 
-- nothing changed

-- same before, but with added repeating point
ST_RemoveRepeatedPoints(st_GeomFromText( 'Polygon((0 0, 0 1, 0.5 1, 0.45 0.45, 0.5 0.5,  0.5000000001 1,1 1, 1 1,  1 0, 0 0))')); 

-- Answer:   'Polygon((0 0, 0 1, 0.5 1, 0.45 0.45, 0.5 0.5,  0.5000000001 1,1 1,  1 0, 0 0))' 
-- repeating point (1,1) is removed, but nothing else changed

otherwise the function could be used together with ST_NPoints to see if there's a difference between the original and modified geometry like this:

ST_NPoints(ST_RemoveRepeatedPoints(geom)) <> ST_NPoints(geom)

I have also tried ST_SnapToGrid to identify geometries with duplicates, but it seems to have the same behavior as ST_RemoveRepeatedPoints.

So far the simplest solution I have found is to

  1. dump the geometry to points
  2. collect the points and then
  3. use ST_SnapToGrid to see if any points are removed, i.e.:
WITH dumppts AS (SELECT (ST_DumpPoints(geom)).geom FROM some_table)
, collect_pts AS (SELECT ST_Collect(geom) AS geom FROM dumppts) 

ST_NPoints(st_removerepeatedpoints(geom)) <> ST_NPoints(geom) -- returns TRUE if there are duplicate points
FROM collect_pts 

but this seems rather slow, because you have to dump and then collect the geometry back together.

Is there a quicker/smarter solution?

EDITED: modified the example geometry so its now polygons

  • I would recommend to use valid geometries as test geometries (ST_IsValid, ST_IsValidReason) at least when developing the method.
    – user30184
    Oct 8 '20 at 17:19
  • By definition, a ring isn't a ring if the first and last vertices aren't identical. Your POLYGON WKT doesn't meet topology rules, doubling back on itself as it does.
    – Vince
    Oct 8 '20 at 17:20
  • The problem that I saw is that vertices are on a straight line and polygon does not have an area. ST_IsValid reports an self-intersection.
    – user30184
    Oct 8 '20 at 19:59
  • Changed examples, as it was invalid Oct 9 '20 at 9:15

You can make use of st_dump to get every points of the polygon ring(s), then ignore the starting point as it has to be the same as the last point, and at last to group by geometry (point) and identify when there is more than 1 point

WITH src AS (
  SELECT osm_id, (st_dumppoints(way)).*
  FROM osm_polygon)
SELECT osm_id, geom, count(geom) cnt
FROM src
WHERE src.path[1]=1 --only the outer ring
    AND path[2] > 1 --ignore the 1st point
HAVING COUNT(*) > 1; --keep only duplicates
  • This assumes the duplicate points are completely identical, correct? that is, if the distance between the points is > 0, it wont find them. Oct 9 '20 at 8:57
  • @JonasOlsen yes, exact duplicates (as in the question title...).
    – JGH
    Oct 9 '20 at 9:09
  • Maybe i haven't been very clear - I guess i assumed when people were talking of duplicate points, this also considered the possiblity of some kind of "tolerance". That is, if the distance between the points is less than a specified tolerance, the points are considered the same. Oct 9 '20 at 9:21

If you need to check for duplicates within a tolerance, you could do something like the following - it dumps the polygon to points twice, then checks for points in the copy that are within the tolerance of each other (my example is using degrees):

select distinct a.osm_id, a.geom from 
    (select osm_id, (st_dumppoints(geom)).* from osm_polygon) a,
    (select osm_id, (st_dumppoints(geom)).* from osm_polygon) b
  where a.path[1] != 1 and b.path[1] != 1 -- outer ring only
    and a.path[2] != b.path[2] -- make sure it's not comparing the point with itself
    and st_dwithin(a.geom,b.geom,0.00001); -- 0.00001 is the tolerance

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.