4

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.

iamdave
  • 12,023
  • 3
  • 24
  • 53
Jon
  • 41
  • 2

1 Answers1

0

I am not entirely sure what is going on with both your data and your requirements as the data in your question doesn't really make sense, but....

To find the distance between two geography points you can use the GeogPoint.STDistance(OtherGeogPoint) function, which will return the distance in metres.

To find the start of a geography linestring you can use either GeogLineString.STStartPoint() or GeogLineString.STPointN(PointNumberYouWantToReturn) with the number 1, which are functionally equivalent, though STPointN does give you a little more flexibility should you want to change your points later on.

Put together, this look like the following:

select Column1
      ,Column3
      ,Column4
      ,geography::Point(Column3,Column4,4326).STDistance(@Routebuffer.STPointN(1)) as StartPointToPointMetres
from @TripRecordsReportWaypoints;

Following from your comments below, here is another function that may help you get to where you need to go:

.ShortestLineTo() will find the shortest line between one geography type and another, which in your case looks like this:

@Columbus106from71to23.ShortestLineTo(geography::Point(Column3,Column4,4326)).STStartPoint() as LineFromRouteToPoint
iamdave
  • 12,023
  • 3
  • 24
  • 53
  • Dave, I would like to know how far along the route a particular point is. Is there a simple way to calculate this? I don't want a strait line distance, I want th distance from the start of the line string, along the line to the point in my data set. Does that make more sense. – Jon Jun 01 '17 at 15:28
  • In that case you may want to adjust the data/query in your question, as none of the points in your data are actually in the buffer from your question. Secondly, what do you mean by *how far along the route*? If the point is within the buffer, do you want to travel along the `linestring` until directly North or South of the point and then straight line to it or take the shortest route from the nearest part of the line? – iamdave Jun 01 '17 at 15:31
  • If the point is within the buffer, I want to travel along the line-string until it is at the point with the shortest route from the nearest part of the line. So the distance from the beginning of the line string all the way along the line-string until it is at the nearest point on the line-string to the point given in Columns 3 and 4. – Jon Jun 01 '17 at 15:50
  • I added a photo to my original post explaining the distance that I seek to calculate. Does your edit calculate what I have pictured? – Jon Jun 01 '17 at 16:11
  • Any idea of a program or service that could calculate that? – Jon Jun 01 '17 at 17:00
  • @Jon No idea on a program, though I may have been a bit hasty beforehand, have a read through here: https://ask.sqlservercentral.com/questions/113547/split-polyline-feature-in-sql-server-2012.html – iamdave Jun 01 '17 at 17:03