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

Author: Pouya Panahy

Microsoft certified DevOps engineer with passion in analysing, designing and implementing solutions for Azure Cloud with hands-on experience in security and quality assurence.

Leave a Reply