CODEWITHSUNDEEP
sql-servergeolocationgreatest-n-per-groupnearest-neighbor
EAguirre
EAguirre

Reputation: 167

Finding the id of the nearest neighbour in SQL

I have a table, #geo, with points in geolocation.

Id        geolocation
9201      0xE6100000010CE33995EB71164CC054791243B87441C0
9202      0xE6100000010C56B77A4E7A1B4CC0D15790662C6E41C0

I calculated the distance to the nearest neighbour for each data point. I have 1000 points for 24 month. Now I replicate my code with the first 19 points in a month

create table #Geo
(
    id               int
    ,geolocation     geography
)

INSERT INTO #geo (id, geolocation)
Select 224,0xE6100000010CE33995EB71164CC054791243B87441C0 UNION ALL
Select 225,0xE6100000010CE7D4BE4EA4184CC0CC947B26A07341C0 UNION ALL
Select 226,0xE6100000010C97A8DE1AD81A4CC0139B8F6B436941C0 UNION ALL
Select 227,0xE6100000010C2EAC1BEF8E164CC0DF80E03B7B7341C0 UNION ALL
Select 228,0xE6100000010CE49BD09887174CC00CADD206F57341C0 UNION ALL
Select 229,0xE6100000010C2B009DB436184CC0FD8E1B5D297441C0 UNION ALL
Select 230,0xE6100000010CFBAC32535A154CC054C72AA5677241C0 UNION ALL
Select 231,0xE6100000010CAE9E93DE37024CC0A167B3EA736141C0 UNION ALL
Select 232,0xE6100000010C70B1A206D3EC4BC0B4024356B76241C0 UNION ALL
Select 233,0xE6100000010CEA78CC40651C4CC097C5C4E6E30A41C0 UNION ALL
Select 234,0xE6100000010CDBFD2AC0770F4CC09E996038D76E41C0 UNION ALL
Select 235,0xE6100000010CA1CB487B8B794BC0C84C9AED277041C0 UNION ALL
Select 236,0xE6100000010CC0076D4108154CC07DD8A069E86E41C0 UNION ALL
Select 237,0xE6100000010C103B53E8BC1E4CC062670A9DD7E03FC0 UNION ALL
Select 238,0xE6100000010CDD94A1130A004CC0ACA6B697DEBB3FC0 UNION ALL
Select 239,0xE6100000010CAB750381252B4BC0F1DDFF2A343D41C0 UNION ALL
Select 240,0xE6100000010CD925AAB706CA4BC045813E91275D40C0 UNION ALL
Select 241,0xE6100000010CD1EB4FE2F3134BC014DA6ABD7C5441C0 UNION ALL
Select 242,0xE6100000010CB32A5F238B144CC0C3E37020037441C0 
--select * from  #Geo


select com.id 
, min( com.GeoLocation.STDistance(com2.GeoLocation)) dist
from #geo com
join    #geo com2   on com.id<>com2.id      
group by com.id 

id  dist
224 608.936575787757
225 454.190509008084
... ...

Now I need to get the nearest neighbor's id:

id  dist               Id_with_minimum_distance
224 608.936575787757     ?
225 454.190509008084     ?

Thank you for your help.

Upvotes: 0

Views: 159

Answers (1)

Andomar
Andomar

Reputation: 238078

You can use a subquery with row_number to filter out all except the nearest com2 rows:

select  *
from    (
        select  row_number() over (
                  partition by id1
                  order by dist) rn
        ,       *
        from    (
                select  com1.id as id1
                ,       com2.id as id2
                ,       com1.GeoLocation.STDistance(com2.GeoLocation) as dist
                from    geo com1
                join    geo com2   
                on      com1.id <> com2.id      
                ) sub1
        ) sub2
where   rn = 1 -- Only nearest com2

Example at SQL Fiddle.

Upvotes: 1

Related Questions