开发者

SQL Spatial JOIN By Nearest Neighbor

In the data below I am looking for a query so I can join the results of the 2 tables by nearest neighbor.

Some of the results in dbo.Interests Table will not be in the dbo.Details Table,

This Question finds the k nearest poins for a single point, I need this query to further reconcile data between the 2 tables

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

IF OBJECT_ID('dbo.Interests', 'U') IS NOT NULL DROP TABLE dbo.Interests;
IF OBJECT_ID('dbo.Details', 'U') IS NOT NULL DROP TABLE dbo.Details;

CREATE TABLE [dbo].[Interests](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [NAME] [nvarchar](255) NULL,
    [geo] [geography] NULL,
)

CREATE TABLE [dbo].[Details](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [NAME] [nvarchar](255) NULL,
    [geo] [geography] NULL,
)

/*** Sample Data ***/ 

/* Interests */
INSERT INTO dbo.Interests (Name,geo) VALUES ('Balto the Sled Dog',geography::STGeomFromText('POINT(-73.97101284538104 40.769975451779729)', 4326));
INSERT INTO dbo.Interests (Name,geo) VALUES ('Albert Bertel Thorvaldsen',geography::STGeomFromText('POINT(-73.955996808113582 40.788611756916609)', 4326));
INSERT INTO dbo.Interests (Name,geo) VALUES ('Alice in Wonderland',geography::STGeomFromText('POINT(-73.966714294355356 40.7748020248959)', 4326));
INSERT INTO dbo.Interests (Name,geo) VALUES ('Hans Christian开发者_如何学Go Andersen',geography::STGeomFromText('POINT(-73.96756141015176 40.774416211045626)', 4326));

/* Details */
INSERT INTO dbo.Details(Name,geo) VALUES ('Alexander Hamilton',geography::STGeomFromText('POINT(-73.9645616688172 40.7810234271951)', 4326));
INSERT INTO dbo.Details(Name,geo) VALUES ('Arthur Brisbane',geography::STGeomFromText('POINT(-73.953249720745731 40.791599412827864)', 4326));
INSERT INTO dbo.Details(Name,geo) VALUES ('Hans Christian Andersen',geography::STGeomFromText('POINT(-73.9675614098224  40.7744162102582)', 4326));
INSERT INTO dbo.Details(Name,geo) VALUES ('Balto',geography::STGeomFromText('POINT(-73.9710128455336 40.7699754516397)', 4326));


A brute force approach will be to compute the distance between all details X interests records:

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

NotE: @k is the target number of matches you are after

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜