MSsql. How to find position

Question

Hi,

I have geography object which contains many LINESTRING

I want to give distance from this geography object

and to find the position on the geography

For example:

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)

I want to find function which receive the geography and distance from start

and return the lat/lon

Answer 1

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.

Answer 2

Hi @Lior Atzmon

Not an expert on this issue, but you might find something help on this: Geography functions.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.