Thursday, February 28, 2013

Finds Close Points by Distance

In this example I am trying to find the distance between the locations in a table and a given point. When I found the distance I will return thee result filtered by a allegible distance.
-- =============================================
-- Author:		Asghar Panahy
-- Create date: 28-Feb-2013
-- Description:	Zoekt objecten binnen bereik van gegeven punt
-- =============================================
ALTER PROCEDURE [dbo].[BereikbareObjecten] 
	-- Add the parameters for the stored procedure here
	@orig_lat REAL , 
	@orig_lng REAL,
	@binnenMeter integer
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	---------------------------------------------------------
	-- Select your SRID wisely. Don't follow me.
	-- select * from sys.spatial_reference_systems 
	-- where spatial_reference_id in (4937, 4258, 4326, 4269)
	DECLARE @SRID as int = 4326;
	---------------------------------------------------------
   
	DECLARE @orig geography;
	SET @orig = geography::Point(@orig_lat, @orig_lng, @SRID);
	
	SELECT  *,CONVERT(INT,  @orig.STDistance( geography::Point([object].[Latitude], [object].[Longitude], @SRID))) As [Distance]
	  INTO #MyTempTable
	  FROM [Object]  
	
	SELECT * FROM #MyTempTable 
         WHERE [Distance] <= @binnenMeter 
         ORDER BY [Distance]
	
END