在下面的数据中,我正在寻找一个查询,以便可以按最近的邻居加入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));

最佳答案

蛮力方法将是计算所有明细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是您追求的目标比赛数

关于sql - SQL Spatial JOIN最近邻居,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/5054547/

10-13 07:25