Calculate length of route along the predefined line-string from beginning of line-string to points within a buffered region around that line-string

Viewed 807

Thus far I have filtered my results to see if they fall within a buffered linestring. Now I would like to calculate the length of the line segment, along the linestring, from the beginning of the route to each latitude and longitude listed in columns 3 and 4.

The distance I want is pictured in the photo below:

Distance explanation

declare @TripRecordsReportWaypoints table(Column1 int, column3 decimal(25,10), Column4 decimal(25,10), InRouteBuffer bit);
insert into @TripRecordsReportWaypoints values (210,40.1445,-82.9911,1),(211,40.1444,-82.991 ,1),(212,40.1442,-82.9909,1),(213,40.1441,-82.9907,1),(214,40.144 ,-82.9906,1);

declare @Columbus106from71to23 geography;
set @Columbus106from71to23 = Geography ::STLineFromText('LINESTRING(-82.969404 40.142222,-82.972580 40.141951,-82.976319 40.141602, -82.979534 40.141376,-82.981217 40.141339,-82.983593 40.141409)',4326);

declare @Routebuffer geography;
set @Routebuffer = @Columbus106from71to23.STBuffer('20');

select Column1
      ,Column3
      ,Column4
      ,@Routebuffer.STContains(geography::Point(Column3,Column4,4326)) as InRouteBuffer
from @TripRecordsReportWaypoints
where @Routebuffer.STContains(geography::Point(Column3,Column4,4326)) = 1;

What do I need to do to calculate the distance of the line segment from the beginning of my defined linestring to each Lat, Long. I would like to have a column that is DistanceAlongRoute.

1 Answers
Related