Find my nearest…

I currently use the following method for calculating the nearest ‘thing’ to a point of interest:  http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx, my thanks to Isaac Kunen!

With some slight modifications…

This stored procedure calculates the distance from the centroid of a parcel to the closest fire plug, an important measurement when subdividing land to ensure all part of a new property can be reached by a fire hose!

Some notes:

  • @XPoint and @YPoint are parcel centroid parameters passed to the stored procedure in the form of UTM coordinates
  • When creating the geometry using the point parameters it is important to get the SRID correct, this caused a minor headache for a while until I figured out that I needed to use 28355 (Zone 55)
  • A full explanation of the SQL used here can be found in the referenced web page as well as the SQL needed to create the ‘numbers’ table that is used to iterate the search area
  • This SQL is used as part of our Online Property Report mapping service: http://maps.alpineshire.vic.gov.au/opr/

CREATE PROCEDURE [dbo].[usp_GetFirePlugDist]

@XPoint VARCHAR(100),
@YPoint VARCHAR(100)

AS

DECLARE @Point VARCHAR(100)
DECLARE @start FLOAT = 1000;
DECLARE @x GEOMETRY;

SET @Point = ‘POINT(‘ + @Xpoint + ‘ ‘ + @Ypoint + ‘)’

SET @x = GEOMETRY::STGeomFromText(@Point, 28355);

WITH NearestPoints AS
(
SELECT TOP(1) WITH TIES *, Round(b.ogr_geometry.STDistance(@x), 0) AS DISTANCE
FROM numbers AS a
JOIN fire_plugs AS b
ON b.ogr_geometry.STDistance(@x) < @start*POWER(2,a.n)
ORDER BY a.n
)
SELECT DISTINCT TOP(1) DISTANCE AS distance_metres
FROM NearestPoints

GO

Advertisements