精简许多ST_Points

Question

我在postgres-db中有很多(1.000.000) ST_Points，带有postgis扩展。当我在地图上显示它们时，浏览器变得非常忙碌。为此，我想写一个sql语句，它将高密度过滤到只有一个点。

当用户缩小到100 ST_Points时，postgres应该只返回一个。但前提是这些点离得很近。

我用下面的语句尝试了一下：

select a.id, count(*)
from points as a, points as b
where st_dwithin(a.location, b.location, 0.001)
and a.id != b.id
group by a.id

我会称之为“瘦身”，但我什么也没找到--也许是因为我不是英语母语者。有人有什么建议吗？

Answer 1

我同意tcarobruce的观点，集群是你正在寻找的术语。但它在postgis中可用。

基本上，聚类可以通过减少X和Y中的小数位数并对它们进行分组来实现；

select 
  count(*),
  round(cast (ST_X(geom) as numeric),3)
  round(cast (ST_Y(geom) as numeric),3)
from mytable 
group by 
round(cast (ST_X(geom) as numeric),3), 
round(cast (ST_Y(geom) as numeric),3)

这将产生一个表，其中包含坐标和该坐标处的实点的数量。在这个特定的示例中，它让您舍入到3个小数点，就像您最初的语句中的0.001一样。

Answer 2

您可以使用ST_ClusterDBSCAN将附近的点聚类在一起

然后保留所有单点，例如：

1. 为每个群集选择一个随机点

或

1. 选择每个点簇的质心。

我使用eps 300将300米内的点聚集在一起。

create table buildings_grouped as
SELECT geom, ST_ClusterDBSCAN(geom, eps := 300, minpoints := 2) over () AS cid
FROM buildings

​

​

1：

create table buildings_grouped_keep_random as
select geom, cid from buildings_grouped
where cid is null
union
select * from 
(SELECT DISTINCT ON (cid) *
FROM buildings_grouped
ORDER BY cid, random()) sub

​

​

2：

create table buildings_grouped_keep_centroid as
select geom, cid from buildings_grouped
where cid is null
union
select st_centroid(st_union(geom)) geom, cid
from buildings_grouped
where cid is not null
group by cid

​

​

Answer 3

你要找的术语是“集群”。

有client-side库可以做到这一点，还有commercial services可以在服务器端做到这一点。

但这不是PostGIS天生就会做的事情。(它有一个ticket。)

您可能必须编写自己的解决方案，并提前预先计算集群。