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