pootzko
5/17/2017 - 1:53 PM

Gets intersecting and closest points (spatialData)

Gets intersecting and closest points (spatialData)

DROP PROC SP_GET_INTERSECTING_BRANCHES
GO
CREATE PROC SP_GET_INTERSECTING_BRANCHES
  @lat FLOAT,
  @lng FLOAT
AS
BEGIN
  DECLARE @point GEOMETRY
  SET @point = GEOMETRY::Point(@lng, @lat, 4326)
  SELECT BranchId, BranchName, BranchNumber FROM [dbo].[Branch]
  WHERE SpatialData IS NOT NULL AND [dbo].[Branch].[IsDeleted] = 0 AND @point.STIntersects(SpatialData) = 1
END
GO

DROP PROC SP_GET_CLOSEST_BRANCHES
GO
CREATE PROC SP_GET_CLOSEST_BRANCHES
  @lat FLOAT,
  @lng FLOAT,
  @amount INTEGER
AS
BEGIN
  DECLARE @point GEOMETRY
  SET @point = GEOMETRY::Point(@lng, @lat, 4326)
  SELECT TOP (@amount) BranchId, BranchName, BranchNumber, @point.STDistance(SpatialData) AS Distance FROM [dbo].[Branch]
  WHERE SpatialData IS NOT NULL AND [dbo].[Branch].[IsDeleted] = 0
  ORDER BY @point.STDistance(SpatialData)
END
GO