5

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?

1 Answer 1

7

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))
1
  • Awesome! I never would have found that bug report on my own-- much appreciated. Tried your solution below and it works perfectly. Thank you! Sep 4, 2017 at 3:40

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.