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
- dump the geometry to points
- collect the points and then
- 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