Spatial SQL -> MS-SQL 2008. Procedure to find N nearest neighbors

In this example we will create stored procedure that will help us to select nearest neighbors from milano spatial layers. Procedure recives parameter @maxdist that restricts search range from the given point and another parameter @N that restricts number of records in result dataset.

USE milano
GO

/* ===================================================================
  This procedure finds N nearst spatial obgects to the given point
==================================================================== */

-- drop prev version
IF EXISTS (SELECT name FROM sysobjects
  WHERE name = N'stp_find_N_nearest' AND type = 'P')
  DROP PROCEDURE stp_find_N_nearest
GO
-- create new

CREATE PROCEDURE stp_find_N_nearest @lat FLOAT, @lon FLOAT, @maxdist FLOAT, @N INT
AS
BEGIN


DECLARE @pt GEOGRAPHY;
-- create point
SET @pt = 'POINT(' + CAST(@lat AS Varchar(40)) +' '+ CAST(@lon AS Varchar(40)) + ')' ;

-- return dataset
SELECT TOP (@N) a.name, a.g, a.dist
FROM
(
  SELECT name, g, g.STDistance(@pt) AS dist
  FROM dbo.milano_reg WITH (INDEX(geo_sidx))
  WHERE g.STDistance(@pt) < @maxdist
   UNION ALL
  SELECT name, g, g.STDistance(@pt) AS dist
  FROM dbo.milano_str WITH (INDEX(geo_sidx))
  WHERE g.STDistance(@pt) < @maxdist
   UNION ALL
  SELECT name, g.STBuffer(12), g.STDistance(@pt) AS dist
  FROM dbo.milano_pnt WITH (INDEX(geo_sidx))
  WHERE g.STDistance(@pt) < @maxdist
   UNION ALL
  SELECT '' AS name, @pt.STBuffer(50), 0 AS dist

) AS a
ORDER BY a.dist;

END;

-- find points nearest to Giuseppe Garibaldi Monument
EXEC stp_find_N_nearest 9.18222293, 45.46821404, 5000, 25;


Spatial results will look like this:




sqlexamples.info