I have millions of rows of geometry data. Some of them are close to each other (tens of meters apart), and some of them can be very far from each other. You can think of gas stations in real world where some gas stations are close to each other across the street, but some are not. The next closest one can be few hundreds meters or tens of kilometers apart.
I was curious if it is possible to write a query that can select one record per area. (either square area or in circle is fine)
For instance, a query that can do something like
"I want to select all gas stations in Germany but only interested in seeing at most one per 500m radius area. If there are multiple stations that are close to each other (fewer than 500m apart), only one of them must be selected."
=> In the result set of the query, I can safely assume that most of Germany's gas stations will be shown and none of them will be fewer than 500 meters apart.
One way that I thought of doing this is to calculate geohash for each geometry, and select one per certain geohash prefix. However, this approach is not very flexible because the query will be limited to geohash characters. I cannot supply arbitrary distance threshold in meters.
Would this be possible as PostGIS query? (e.g. using clustering approach perhaps?)
Below is an example incomplete query that I have tried using geohash. Results are sorted by geohash, so it is just matter of selecting one from each set with the same geohash value. However, I can only use area box determined by geohash prefix, and cannot provide custom radius (e.g. 5km, 2km, 1km, etc) value, which makes this query not so flexible.
select id, ST_GeoHash(way, 5), st_y(way) as lat, st_x(way) as lng from points order by st_geohash(way, 5) limit 1000; id geohash lat lng 115319221 "4uusx" -41.5740949 -74.0718146 679361789 "4uusx" -41.5759059 -74.0728299 591844292 "4uusz" -41.5711773 -74.0703186 796302025 "4uutp" -41.5086211 -74.1087041 672159089 "4uuu8" -41.5721128 -74.0665036 687629681 "4uuub" -41.57108 -74.069856 591843790 "4uuub" -41.5696367 -74.064995 596950189 "4uuub" -41.5700602 -74.0660787