Find Segments lying inside another using Geography points SQL Server

Question

Let me explain the attached picture Yellow line termed as Construction Planning identified by Planning Id and all other colors termed as Construction Segment identified by SegmentId. I am trying to use STDistance function of Geography Points to find the SegmentId=12689 and SegmentId=12687 by passing the PlanningID=25801. My program should return the values 12689 and 12687 since these two wholly lie within the PlanningID=25801. However it should exclude the value SegmentId=12688 since that segment doesn't lie fully or most part of the segment is not inside the Construction Planning. I have created the following script in SQL server

declare    @Planningid INT
declare    @agencyID int
set @Planningid=25801
set @agencyID=79
DECLARE @finalresult int
DECLARE @result TABLE (id INT, starting sys.geography, ending sys.geography,
                          chachedroute sys.geography)
DECLARE @tablevar TABLE(id INT)
insert into @result select id,
starting,Ending,CachedRoute from Route R where AgencyId=@agencyID and LayerId=1 
                  and CachedRoute.STDistance((select CachedRoute from route 
                                               where id=@Planningid))<0.5
select * from @result
/* the above part of the code is working fine as it should */

/* the next part of the logic is not working and it is excluding all the Segment Id when it 
                                should not and should only exclude SegmentId=12688 */
insert into @tablevar
select id from @result where 
            chachedroute.STDistance((select starting from route where id=@Planningid))<700 
            and 
            chachedroute.STDistance((select ending from route where id=@Planningid))<700
intersect
select id from @result

select * from @tablevar

I guess the problem is because of the and logic in the second part of the code where it calculates if the values are within 700 meters. This code was first created with the concept that each PlanningId should have only one SegmentID which is why I coded it by matching both the starting and ending of the segments. But now the modification is that a single PlanningId can have multiple SegmentID where I guess lies the problem. Any help how to modify the code will be really appreciated. The table Route contains the following attributes:

ID (int)
Starting (Geography Points)
Ending (Geography Point)
Cached Route (Geography Point)
LayerId (int)
AgencyId(int)

The value LayerId=1 refers to Construction Segment and LayerId=4 refers to Construction Planning

score 0 · Answer 1 · answered Aug 12 '20 at 15:23

I have changed the above Function as the following to make it work:

CREATE FUNCTION [dbo].[GetSegmentId]
(   
    @Planningid INT,
    @agencyID int
)

RETURNS @tablevar TABLE 
(
    id INT
)

AS

BEGIN
        declare    @CachedRoute geography
        DECLARE @finalresult int
        DECLARE @result TABLE (id INT, starting sys.geography, ending sys.geography,chachedroute sys.geography)
        

        set @CachedRoute = (select CachedRoute from route where id = @Planningid)

        insert into @result select id,
        starting,Ending,CachedRoute from Route R where AgencyId=@agencyID and LayerId=1 and CachedRoute.STDistance(@CachedRoute)<0.5 

        insert into @tablevar
        select id from @result where @CachedRoute.STDistance(starting)<700 and @CachedRoute.STDistance(ending)<700
        intersect
        select id from @result


    RETURN;
END;

Find Segments lying inside another using Geography points SQL Server

1 Answers1