在同一表GIS Postgsql中搜索最近的邻居

Question

我们有一个单独树木的数据库，数据库中有地理位置，我们似乎有一个由长和纬度组合而成的地理点，名为estimated_geometry_location。我们每个月都会定期更新这些树。我想得到一个有两个属性的树的列表。我想确定特定树最有可能的更新，即当一个跟踪事件中的一组新树出现时，我们需要运行一个例程，表明日期条目x.2是数据点x.1的更新。理想情况下，该例程然后更新新的数据点(子数据点(，添加旧的母亲数据点，然后希望它代表同一棵树。

到目前为止，我有这样的东西，但DB没有响应(或者可能我等待的时间不够长…到目前为止等待了大约10分钟(

SELECT
i.id
,ST_Distance(i.estimated_geometric_location, i.b_estimated_geometric_location) AS dist
FROM(
SELECT
a.id
,b.id AS b_id
,a.estimated_geometric_location
,b.estimated_geometric_location AS b_estimated_geometric_location
,rank() OVER (PARTITION BY a.id ORDER BY ST_Distance(a.estimated_geometric_location, b.estimated_geometric_location)) AS pos
FROM trees a, trees b 
WHERE a.id <> b.id) i
WHERE pos = 1

如果能对此有一些想法就太好了。我从这里的某个地方得到了这个帖子，并对其进行了改编，但到目前为止运气不佳。

Answer 1

有几件事值得一提。如果数据来自跟踪事件，为什么要将现有树相互比较？我希望有类似的东西

SELECT id
FROM trees
ORDER BY st_distance(estimated_geometric_location, st_makepoint(15, 30))
LIMIT 1

其返回最接近具有经度15和纬度30的点的树。看看你是否需要加入。

---

假设您这样做，像这样的查询的问题是复杂性。如果你的数据库中有任何数量(比如1000棵(的树，那么你实际上是在计算1000棵树与所有999棵树之间的距离，计算999.000个距离！只是说，如果A和B之间的距离与B和A之间的距离相同，那么你应该可以通过说a.id < b.id来剃掉一半。

此外，想想你在做什么。你想找到任意两棵树之间的最小距离，以及与该距离对应的树的ID，对吧？一旦你知道它们不是最小的距离，就没有必要计算任何距离。

SELECT a.id, b.id, ST_Distance(a.estimated_geometric_location, b.estimated_geometric_location)) distance
FROM trees a, trees b 
WHERE a.id < b.id
ORDER BY distance
LIMIT 1

是一种简单得多的到达方式，对我来说也快得多。