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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s