CODEWITHSUNDEEP
sqlsql-servert-sqlgeometrybearing
CuriousDeveloper
CuriousDeveloper

Reputation: 899

Average bearing of a linestring

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

enter image description here

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)

Upvotes: 4

Views: 1649

Answers (2)

ParoX
ParoX

Reputation: 5933

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):

enter image description here

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

Upvotes: 0

Ali Al-Mosawi
Ali Al-Mosawi

Reputation: 778

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

Upvotes: 2

Related Questions