测量所有距离返回最短值

Question

我有一个带有商店列表的表格，大约100张左右。第二个桌子我有一个客户列表，其中有LAT/LONG，超过500K。

我需要找到最接近每个客户的商店。目前，我正在使用带有stdistance函数的地理数据类型来计算两个点之间的距离。这很好，但是我挂在处理此操作的最有效方法上。

选项＃1-笛卡尔加入customer_table to store_table，处理距离计算，对结果进行排名，并过滤到＃1。令人担忧的是，如果您有100万行客户列表和100家商店，则创建了1亿行桌子，然后等级功能，然后可能会征税。

选项＃2-使用一些动态SQL，创建一个枢纽的表，该表在第一列中都有每个客户，并且每个后续列都具有与每个分支的计算距离。从那里开始，我可以脱离，然后执行第一个中描述的相同等级/超级函数。

示例

CUST_ID     LAT     LONG     STORE1DIST     STORE2DIST     STORE3DIST
1           20.00   30.00    4.5            5.6            7.8
2           20.00   30.00    7.4            8.1            8.5

我不清楚哪个是最有效的，并且会阻止DBA想要找到我。

感谢提前的输入！

Answer 1

您可以在每个商店距离中将数据分为多行，然后使用简单的Pivot（组By）获取存储的最小值。

select CUST_ID, MIN(STOREDIST) StoreDistance, MIN(STORES) StoreName
from 
(select CUST_ID, LAT, LONG, STORE1DIST, STORE2DIST, STORE3DIST from Cus/*Your table*/) p
UNPIVOT
(
STOREDIST FOR STORES IN (STORE1DIST, STORE2DIST, STORE3DIST)
) as unpvt
Group by CUST_ID

这将为您提供：

CUST_ID StoreDistance   StoreName
-----------------------------------
1       4.5             STORE1DIST
2       7.4             STORE1DIST

Answer 2

我的工作情况类似。我使用这样的距离函数（返回kms，使用 3960*返回里程）：

CREATE Function MySTDistance(@lat1 float, @lon1 float, @lat2 float, @lon2 float)
returns smallmoney
as
    return IsNull(6373*acos((sin(radians(@lat1))*sin(radians(@lat2)))
                           +(cos(radians(@lat1))*cos(radians(@lat2))*cos(radians(@lon1-@lon2)))),0)

然后，您通过做类似的事情来寻找最接近的商店

select C.Cust_Id
    ,Store_id=
        (select top (1) Store_id
           from Store_Table S
          order by dbo.MySTDistance(S.lat, S.long, C.lat, C.long)
        )
from Customer_Table C

现在，您拥有每个客户ID的最接近的商店ID。有大量客户（至少在我的情况下）很快。