How to find midpoint of a polyline

Shaun Gibbins 0

How would you calculate the x,y of the midpoint of a polyline with MS SQL Server?

I can get start point and end point by using shape.STStartPoint().ToString() and shape.STEndPoint().ToString()
An arbitrary point using shape.STPointOnSurface().ToString() but can't seem to find a method to get the midpoint
For polygon I can use shape.STCentroid().ToString() but there doesn't seem to be a similar method for lines.

I am basically trying to achieve the same as the postGIS example in the attached screeny.

User's image

Viorel 112.5K

Maybe like this:

declare @g geometry = geometry::Parse('LINESTRING(0 0, 5 5, 10 7, 14 4)')

;
with Q as
(
	select 2 as i, @g.STCurveN(1) as line, @g.STCurveN(1).STLength() as dist
	union all
	select i + 1, @g.STCurveN(i), dist + @g.STCurveN(i).STLength()
	from Q
	where dist < @g.STLength()/2
)
select top(1)
	(case dist - @g.STLength()/2
	 when 0 then line.STEndPoint()
	 else line.STEndPoint().STBuffer(dist - @g.STLength()/2).STExteriorRing().STIntersection(line) end).STAsText() as midpoint
from Q
order by i desc
option (maxrecursion 0)