计算每个商店位置的最小距离 SQL 查询

Question

我有一个表格，其中包含每个商店的X，Y坐标。我想找到彼此之间的最小距离。我的意思是最近的商店。

例如：(所需输出(

Shop_ID      Nearest_Shop_ID 
Shop_1       Shop_5
Shop_2       Shop_8
Shop_3       Shop_4

Select SHOP_ID, Longtitude, Latitude From PARTNER_ADDRESSES
group by SHOP_ID, Longtitude, Latitude;

这是我的商店坐标表;

SHOP_ID     LONGTITUDE  LATITUDE
38599       32.815282   39.882793
38613       25.965545   42.166315
38682       31.845157   37.419859
38686       34.027568   38.370871

我有一个公式可以计算两个位置之间的距离(使用纬度和纵向(。这是公式;

(NVL(6387.7,0) * ACOS((sin(NVL(Latitude1,0) / 57.29577951) * SIN(NVL(Latitude2,0) / 57.29577951)) +
(COS(NVL(Latitude1,0) / 57.29577951) * COS(NVL(Latitude2,0) / 57.29577951) *
COS(NVL(Longtitude2,0) / 57.29577951 - NVL(Longtitude1,0)/ 57.29577951))))
Latitude1  : Shop 1's Latitude value
Longtitude1: Shop 1's Longtitude value
Latitude2  : Shop 2's Latitude value
Longtitude2: Shop 2's Longtitude value

是否可以编写这种sql来计算每个商店的距离并列出每个商店的最小距离值？

Answer 1

您可以使用交叉连接来做每个商店与每个商店，例如

select <distance formula> from shops as ashops cross join shops as bshops

从那里，使用嵌套的SQL，您可以从最小的距离为每个商店选择一家商店

Answer 2

我认为您可以使用cross join，然后使用row_number：

with distance1 as (
select
pa1.SHOP_ID as shop_id1, pa1.Longtitude longitude_1, pa1.Latitude latitude_1,
pa2.SHOP_ID as shop_id2, pa2.Longtitude longitude_2, pa2.Latitude latitude_2,
<your_distance_formula> as Distance
from PARTNER_ADDRESSES pa1
cross join PARTNER_ADDRESSES pa2
where pa1.SHOP_ID <> pa2.SHOP_ID -- remove record where shops are the same
)
select *
from (
select *,
row_number() over (partition by shop_id1 order by Distance) rn_distance
from distance1
) 
where rn_distance = 1

Answer 3

您可以使用self join和analytical function。

Select s1, s2 from
(Select a1.shop_id as s1, a2.shop_id as s2,
Row_number() 
over (partition by a1.shop_id
Order by <your_formula>) as rn
From partner_adresses a1
Join partner_adresses a2
On (a1.shop_id <> a2.shop_id))
Where rn = 1;

干杯！！