MSsql. How to find position

Lior Atzmon 0 Reputation points
2023-01-30T13:03:24.1+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,328 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 110.1K Reputation points
    2023-01-30T18:40:16.06+00:00

    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.

    0 comments No comments

  2. CosmogHong-MSFT 21,051 Reputation points Microsoft Vendor
    2023-01-31T07:08:31.5033333+00:00

    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.

    0 comments No comments