Average bearing of a linestring

Question

How can I compute a bearing that represent a non-straight linestring geometry?

For example, Given this linestring:

LINESTRING(-100.06372406847015 25.4583895045113,-95.14184906847015 30.197590679284158,-100.67895844347015 33.335334871825495,-95.40552094347015 36.576044042090466,-100.59106781847015 39.14304403636714,-95.40552094347015 41.61974326920709,-100.15161469347015 43.56067943577098,-95.66919281847015 45.87106856382069,-110.08325531847015 48.44030667059785,-85.38598969347015 48.73100364391479)

Which you can visualize here: http://arthur-e.github.io/Wicket/sandbox-gmaps3.html

The starting point (the first point in the linestring) is at the bottom of the map (starting in mexico).

I would expect the average/overall bearing of the line to be pointing north (~0 degrees)

Most calculations for the bearing of a line just take the start point and endpoint of the linestring and calculate it that way. Which is correct for a straight line with just 2 points.

I believe the way to do this for a linestring is to calculate the bearing for each POINT pair and the average them?

I am not sure if the methodology is right or if there is a better way (built in function or algorithm)

The expected outcome is a number that is close to 0 (0 degrees). — CuriousDeveloper, Jul 21 '19 at 17:47
I think if you calculate a weighted average of the bearings for each segment (considering the length of each segment) you will get closer to the bearing between the initial point and the final point. — Razvan Socol, Jul 21 '19 at 19:13
The question is not well defined. Imagine a set of lines that form a regular hectagon. Which would the "bearing" be then? — George Menoutis, Jul 25 '19 at 05:33
You need to decide for yourself what kind of algorithm makes sense for your domain. One very simple approach is to take the starting point as a special point and draw a vector from this starting point to the simple average of coordinates of all other points. In other words, don't average angles, but average coordinates. Another possible option is to approximate the given set of points with a straight line using the [Least squares method](https://en.wikipedia.org/wiki/Least_squares). — Vladimir Baranov, Jul 26 '19 at 01:43
The first thought is to calculate the average for each line between two points so you will get the point in the middle, then to average all the results calculated in the first step. This will give a good result supposing that the lines are not going in a circle, because in such case the direction will be toward the center. — J.K, Jul 26 '19 at 02:30

Ali Al-Mosawi · Answer 1 · 2019-07-31T22:46:02.403

The angle that I got is going north, but my north is 90 degrees, and the angle I got from your points is around 84 degree, so going north with a bit of east. Note: This may not be correct but only a try.

I first used split to get separate points, then xml to get each east and north point, then rearranged the data so I can see on the same row the each point with the next point. then calculated the delta and then the bearing between each point, at the end was the average. hope it helps.

declare @data nvarchar(max)='-100.06372406847015 25.4583895045113,-95.14184906847015 30.197590679284158,-100.67895844347015 33.335334871825495,-95.40552094347015 36.576044042090466,-100.59106781847015 39.14304403636714,-95.40552094347015 41.61974326920709,-100.15161469347015 43.56067943577098,-95.66919281847015 45.87106856382069,-110.08325531847015 48.44030667059785,-85.38598969347015 48.73100364391479'
declare @points table (seq int,x float,y float)

    ;with points as (select ROW_NUMBER() over (order by (select 1) desc) seq,* from string_split(@data,','))
    ,xmldata as (select points.seq,CONVERT(XML,'<Points><Point>'+ REPLACE(points.value,' ', '</Point><Point>') + '</Point></Points>') AS xmldataPoints
    from points),pointXY as (
    SELECT seq,
        xmldata.xmldataPoints.value('/Points[1]/Point[1]','float') AS [x],
        xmldata.xmldataPoints.value('/Points[1]/Point[2]','float') AS [y]
    FROM xmldata
    ),nextPoint as (
    select *,LEAD(x,1,null) over (order by seq) x2,LEAD(y,1,null)  over (order by seq) y2 from pointXY
    ),delta as (
    select *,(x2-x) dNorth,(y2-y) dEast from nextPoint
    ),bearing as (
    select * ,
    DEGREES(  IIF(dEast=0,
        IIF(dNorth<0,PI(),0),
        IIF(dEast<0,(-aTan(dNorth / dEast) + PI() / 2.0+ PI()),(-aTan(dNorth / dEast) + PI() / 2.0)))) bearing
    from delta
    )
    select AVG(bearing) from bearing

Result

84.5262691250142

score 0 · Accepted Answer · answered Aug 02 '19 at 22:15

When you have segments lined up like you each segment represents a vector the total line is one resultant vector. You can find the angle of the vector finding the angle between the start point and the end point;

The cartographical azimuth (in decimal degrees) can be calculated when the coordinates of 2 points are known in a flat plane (cartographical coordinates):

Source: https://en.wikipedia.org/wiki/Azimuth

Average bearing of a linestring

2 Answers2