我有两个表(表1和表2(,其中有三列:id
、value
和geometry
。几何图形是点特征。
我想在两个表之间进行连接,其中生成的表包含表1的每个点、到表2的点的最小距离、表1的值和表2的对应点的值。
我尝试了下面的代码,但从逻辑上讲,这给出了表1的每个点到表2的每个点的距离。但是,我不能将v2从groupby子句中删除。我怎样才能买到我想要的桌子?
SELECT t1.value AS v1,
t2.value AS v2,
MIN(st_distance(t1.eometry, t2.geometry)) AS dis
FROM table1 t1, table2 t2
GROUP BY v1, v2
为了简单起见,我只取了整数值及其差值,而不是点之间的距离(但应该完全相同:只需更改st_distance
函数的减法(:
演示:db<>小提琴
SELECT DISTINCT ON (v1.point)
v1.point,
v2.point,
abs(v1.point - v2.point)
FROM
table1 v1
CROSS JOIN table2 v2
ORDER BY v1.point, abs(v1.point - v2.point)
我的表:
table1.point: 1, 2, 4, 8, 16
table2.point: 2, 3, 5, 7, 11, 13
结果:
| point | point | abs |
|-------|-------|-----|
| 1 | 2 | 1 |
| 2 | 2 | 0 |
| 4 | 3 | 1 |
| 8 | 7 | 1 |
| 16 | 13 | 3 |
解释:
你必须计算所有的差异,才能知道哪一个是最小的。这就是CROSS JOIN
的原因。现在您可以ORDER BY
表1的点和差异(或距离(。注意abs()
函数:这会使所有负值为正值。否则将取差CCD_ 8而不是CCD_。
DISTINCT ON (v1.point)
为每个v1.1点取第一个有序行。
注意:
由于CROSS JOIN
和st_distance
中繁重的数学运算,对于庞大的数据集来说可能会非常缓慢!