If you did not find a function or a good approach, then check some acrobatics:
-- the path
declare @g1 geography = geography::STGeomFromText('LINESTRING(7.068419558716859 80.140939950943,7.070868430303301 80.14106869697571,7.072976578922316 80.14136910438538,7.074339417352194 80.14203429222107,7.07631978477513 80.14278531074524,7.078491790888004 80.14435172080994,7.078300143701023 80.14649748802185,7.078449202631107 80.14877200126648,7.076617903866453 80.15068173408508,7.074978246481405 80.15248417854309,7.074105179783337 80.15490889549255,7.0723803358444135 80.15643239021301)', 4326)
-- sample distance
declare @distance float = 500.0
declare @i int
declare @r float
;
with Q as
(
select 0 as i, @distance as remaining, cast(0.0 as float) seglen
union all
select i + 1, remaining - @g1.STCurveN(i + 1).STLength(), @g1.STCurveN(i + 1).STLength()
from Q
where remaining >= 0
)
select top(1) @i = i, @r = remaining
from Q
order by i desc
option (maxrecursion 0)
declare @lat float
declare @lon float
if @i is not null
begin
declare @seg geography = @g1.STCurveN(@i)
declare @d float = @seg.STLength() + @r
declare @p geography
if @d = 0
set @p = @seg.STPointN(1)
else
begin
declare @in geography
set @in = @seg.STPointN(1).BufferWithCurves(@d).STIntersection(@seg)
if @seg.STPointN(1).STDistance(@in.STPointN(2)) > @seg.STPointN(1).STDistance(@in.STPointN(1))
set @p = @in.STPointN(2)
else
set @p = @in.STPointN(1)
end
set @lat = @p.Lat
set @lon = @p.Long
end
-- return results; NULL if the path is too short
select @lat as Lat, @lon as Lon
It assumes that the distance should be measured on your path (lines).
If it works, you can make a function or stored procedure.