sql - SQL Spatial JOIN最近邻居

Question

在下面的数据中，我正在寻找一个查询，以便可以按最近的邻居加入2个表的结果。

dbo.Interests表中的某些结果将不在dbo.Details表中，

这个问题找到单个点的k个最近点，我需要此查询来进一步协调2个表之间的数据

How can I extend this SQL query to find the k nearest neighbors?

如果OBJECT_ID('dbo.Interests'，'U')不是空DROP TABLE dbo.Interests;
如果OBJECT_ID('dbo.Details'，'U')不是空DROP TABLE dbo.Details;

创建表[dbo]。[兴趣](
[ID] [int] IDENTITY(1,1)NOT NULL，
[NAME] [nvarchar](255)NULL，
[geo] [geography] NULL，
)

创建表[dbo]。[详细信息](
[ID] [int] IDENTITY(1,1)NOT NULL，
[NAME] [nvarchar](255)NULL，
[geo] [geography] NULL，
)

/*** 样本数据 ***/

/ *兴趣* /
插入dbo.Interests(Name，geo)VALUES('Balto Sled Dog'，geography::STGeomFromText('POINT(-73.97101284538104 40.769975451779729)'，4326));
插入dbo.Interests(Name，geo)VALUES('Albert Bertel Thorvaldsen'，geography::STGeomFromText('POINT(-73.955996808113582 40.788611756916609)'，4326));
插入dbo.Interests(Name，geo)VALUES('Alice in Wonderland'，geography::STGeomFromText('POINT(-73.966714294355356 40.7748020248959)'，4326));
插入dbo.Interests(Name，geo)VALUES('Hans Christian Andersen'，geography::STGeomFromText('POINT(-73.96756141015176 40.774416211045626)'，4326));

/* 细节 */
插入dbo.Details(Name，geo)值('Alexander Hamilton'，geography::STGeomFromText('POINT(-73.9645616688172 40.7810234271951)'，4326));
插入dbo.Details(Name，geo)值('Arthur Brisbane'，geography::STGeomFromText('POINT(-73.953249720745731 40.791599412827864)'，4326));
插入dbo.Details(Name，geo)值('Hans Christian Andersen'，geography::STGeomFromText('POINT(-73.9675614098224 40.7744162102582)'，4326));
插入dbo.Details(Name，geo)值('Balto'，geography::STGeomFromText('POINT(-73.9710128455336 40.7699754516397)'，4326));

Answer 1

蛮力方法将是计算所有明细X兴趣记录之间的距离:

SELECT *
FROM
(
SELECT *, rank=dense_rank() over (partition by IName, IGeo order by dist asc)
FROM
(
     SELECT D.NAME as DName, D.geo as DGeo,
        I.NAME as IName, I.geo as IGeo,
        I.geo.STDistance(D.geo) AS dist
     FROM dbo.Details D CROSS JOIN dbo.Interests I
) X
) Y
WHERE rank <= @k

注意:@k是您追求的目标比赛数