Tolerance argument for ST_RemoveRepeatedPoints in PostGIS

Question

I have a multipoint PostGIS table with 8,000 or so points that represent intersections in a city street grid. I'd like to clean up this table to remove any points that are within 100 meters of another point.

Looking at the docs, ST_RemoveRepeatedPoints appears to be the best way to go about this. However when I try the example, I can't seem to get the tolerance parameter to work in the way I expect.

For example:

 SELECT ST_AsText(ST_RemoveRepeatedPoints(
            'MULTIPOINT((4 4), (1 1), (1 0), (0 3), (4 4))', 100.0));

Returns:

MULTIPOINT((4 4), (1 1), (1 0), (0 3))

It does remove the duplicate point (4 4), but given that I set the tolerance parameter to 100, I would expect it to remove all the points (except one) given that they are all within 100 units of each other.

What am I missing? And what's the best way to run this operation using PostGIS or ogr2ogr?

Answer 1

There is an open issue with ST_RemoveRepeatedPoints where the tolerance parameter does not work when the function is called with a MultiPoint.

If you need to do a one-off cleanup operation, you could run something like this:

DELETE FROM my_table a
WHERE EXISTS (SELECT 1 FROM my_table b 
              WHERE a.id < b.id 
              AND ST_DWithin(a.geom, b.geom, 100))