Nearest Point in line to another point

Topics: User Forum
Feb 4, 2009 at 4:57 AM

I'm new to MsSqlSpatial.  Ive imported my shape file and have played around with all of the functions.... All within about 4 hours, so I'm pretty happy with that.  Could someone please give me an idea of how I can get the closest point in a line from another point.

so far I have got my @line of interest.

select ST.asText(@line) as ThisLine
LINESTRING(174.752811989227 -36.8620953553892,174.753079379821 -36.8619542623121,174.753109719454 -36.8619081937313,174.753095624035 -36.8618493499078,174.752572497954 -36.8611827755082,174.75249911151 -36.8608565346554,174.752466092978 -36.8607823384962)

and I have another point...
set @lon = 174.7532
set @lat = -36.8612
select ST.asText(ST.PointFromText('POINT(' + CAST(@lon AS VARCHAR) + ' ' + CAST(@lat AS VARCHAR) + ')', 32740)) AS ThisPoint
POINT(174.753 -36.8612)

So now, I expect to see a function like...

select ST.asText(ST.ClosestPointInLine(ThisPoint, ThisLine))
POINT(174.749 -36.8603)  ... for example.

Any ideas? I'm not much of a GIS wizard and it's been a while since doing maths at school.


Feb 4, 2009 at 10:20 AM
Hi there.. here is a T-SQL function which will do it.. however it would be much faster if implemented as a Clr function.

CREATE FUNCTION [dbo].[NearestPointInLine](
@line varbinary(max),
@comparePoint varbinary(max)
RETURNS varbinary(max)

declare @numPoints int
set @numPoints = ST.NumPoints(@line)
declare @ndx int
set @ndx = 1
declare @shortestDistance float
declare @nearestPoint varbinary(max)
WHILE @ndx < @numPoints + 1
 declare @currentPoint varbinary(max)
 set @currentPoint = ST.PointN(@line, @ndx)
 declare @currentDistance float
 set @currentDistance = ST.Distance(@currentPoint, @comparePoint)
 if @shortestDistance is null or @currentDistance < @shortestDistance
  set @shortestDistance = @currentDistance
  set @nearestPoint = @currentPoint
 set @ndx = @ndx + 1

RETURN @nearestPoint

Then you can use it like so: 

declare @line varbinary(max)
select top 1 @line =  Wkb_Geometry from Rivers
declare @comparePoint varbinary(max)
select top 1 @comparePoint = Wkb_Geometry  from Capital

declare @result varbinary(max)
set @result = dbo.NearestPointInLine(@line, @comparePoint)

select ST.AsText(@result)


hth jd
Feb 4, 2009 at 7:01 PM

Thanks for that.  I think I misled you in the question.  Your code finds the closest point in the finite list where I need to get the closest point on the line.  I think what I need to do is:
1) Find the closest line segment.
2) Using vectors find the closest point on that segment.

I going to try and create a Clr function to do this.