How can I exclude POINTs that are x distance from each other?

Question

I have a FCC database of antennas and a map view having a bounds. I want to display all antennas within the view, but antennas a stones throw away are duplicates or noise. In the example below, rows 2 and 3 are so close its a dupe (Technically it isn't a dupe, but it is noise.). How can I do a SELECT and filter out antennas that are close (x meters) from each other?

SELECT distinct st_astext(geom), ST_Distance(loc.geom::geography, ST_GeomFromText('POINT(-115.62309833 35.97311)', 4326)::geography) / 1609.344 as miles
FROM fcc_loc as loc join fcc_en as en on loc.call_sign = en.call_sign
WHERE en.entity_name LIKE '%bubbah%' AND ST_Within(geom, ST_MakeEnvelope(-115.29356182624997,36.13963970370481,-115.95274151374997,35.80622156767141,4326));
                 st_astext                 |      miles
-------------------------------------------+------------------
 POINT(-115.49475 35.8928055555556)        | 9.08012915615928
 POINT(-115.616388888889 35.8094444444444) | 11.2902759331815
 POINT(-115.616388888889 35.8094722222222) | 11.2883618698737
 POINT(-115.826805555556 36.1227222222222) |  15.378520803433
(4 rows)

Answer 1

One simple solution would be to round the coordinates for your select distinct. Depending on your CRS, the 3rd decimal place would be an accuracy of about 110 meters (see this answer).

In your sample data, if we rounded to the 3rd decimal the second and third point would be identical. A simple way to do that is to typecast to numeric with 3 decimal places.

We can use SELECT DISTINCT ON () to select based on that:

SELECT DISTINCT
    ON (ST_X::numeric(10,3), ST_Y::numeric(10,3)),
    st_astext(geom),
    ST_Distance(loc.geom::geography, ST_GeomFromText('POINT(-115.62309833 35.97311)', 4326)::geography) / 1609.344 as miles
FROM fcc_loc as loc
JOIN fcc_en AS en
ON loc.call_sign = en.call_sign
WHERE en.entity_name LIKE '%bubbah%' AND ST_Within(geom, ST_MakeEnvelope(-115.29356182624997,36.13963970370481,-115.95274151374997,35.80622156767141,4326));