3

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) 

SELECT 
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

4
  • I would recommend to use valid geometries as test geometries (ST_IsValid, ST_IsValidReason) at least when developing the method.
    – user30184
    Commented Oct 8, 2020 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
    Commented Oct 8, 2020 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
    Commented Oct 8, 2020 at 19:59
  • Changed examples, as it was invalid Commented Oct 9, 2020 at 9:15

2 Answers 2

3

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
GROUP BY OSM_ID, geom
HAVING COUNT(*) > 1; --keep only duplicates
3
  • This assumes the duplicate points are completely identical, correct? that is, if the distance between the points is > 0, it wont find them. Commented Oct 9, 2020 at 8:57
  • @JonasOlsen yes, exact duplicates (as in the question title...).
    – JGH
    Commented Oct 9, 2020 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. Commented Oct 9, 2020 at 9:21
0

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 and acknowledge you have read our privacy policy.

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