SQL Server, Given a linestring with M value, what is the location of this other M value

Question

We are investigating migrating a prototype into SQL Server (azure).

We have LineStrings that also have M values. What we would like to do is given another M value find out what its geographical location is.

To aid your visualisation, here is a real-world example:

I have a linestring that represents a flight path. Because the flight goes up and down the distance the plane has actually moved is not the same as the total length of the linestring. We have calibrated M values as a part of the linestring but need to be able to plot on it where a given event occurred. All we know about this event is its M value.

SET @g = geometry::STGeomFromText('LINESTRING(1 0 NULL 0, 2 2 NULL 5, 1 4 NULL 9, 3 6 NULL 15)', 0);

Given something like the above, what is the lat and long of a point with an M value of 8?

*Note I am not sure if I have Nulled the Z or M value. The extra parameter we are considering here is the M only.

Answer 1

To calculate the point along the path, you need bearing and distance. If I understand correctly, you have distance (M=distance along the segment).

This SO post has some SQL Server code to determine bearing based on two points:

https://stackoverflow.com/a/36620613/5153797

This website has hints about the math involved to calculate a point based on bearing and distance.:

http://www.movable-type.co.uk/scripts/latlong.html

See the "Destination point given distance and bearing from start point" section.

I know this doesn't have all the code gift-wrapped, but hopefully it will provide the nudge in the right direction.

PS: a nice SO post on calculating distance from two points:

https://stackoverflow.com/a/13026961/5153797