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;
|
