title: How to calculate the "Nearest Neighbour" for multiple sources in SQL Server?
body: <p>The "Nearest Neighbour" problem is very common when working with spatial data.</p>
  
  <p>There's even some <a href="https://docs.microsoft.com/en-us/sql/relational-databases/spatial/query-spatial-data-for-nearest-neighbor" rel="nofollow noreferrer">nice, simple documentation</a> about how to do it with MS Sql Server in their docs!</p>
  
  <p>I'm usually seeing examples where it's using 1x source Lat/Long and it returns the 'x' number of nearest neighbour Lat/Longs. Fine...</p>
  
  <p>e.g.</p>
  
  <pre><code>USE AdventureWorks2012  
  GO  
  DECLARE @g geography = 'POINT(-121.626 47.8315)';  
  SELECT TOP(7) SpatialLocation.ToString(), City FROM Person.Address  
  WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
  ORDER BY SpatialLocation.STDistance(@g);
  </code></pre>
  
  <p>In my case, I have <em>multiple</em> Lat/Long sources ... and for each source, need to return the 'x' number of nearest neighbours.</p>
  
  <p>Here's my schema</p>
  
  <pre><code>Table: SomeGeogBoundaries
  LocationId INTEGER PRIMARY KEY (it's not an identity, but a PK &amp; FK)
  CentrePoint GEOGRAPHY
  
  Index:
  Spatial Index on CentrePoint column. [Geography || MEDIUM, MEDIUM, HIGH, HIGH]
  </code></pre>
  
  <p>Sample data:</p>
  
  <pre><code>LocationId | CP Lat/Long
  1          | 10,10
  2          | 11,11
  3          | 20,20
  
  ..
  </code></pre>
  
  <p>So for each location in this table, I need to find the closest.. say 5 other locations.</p>
  
  <h2>Update</h2>
  
  <p>So far, it looks like using a <code>CURSOR</code> is the only way .. but I'm open to more <em>set</em> based solutions.</p>