Obtain best-fit bearing for sequence of points

Question

I'd like to calculate the overall bearing (direction) of a sequence of points in PostGIS.

It's clear how to do this for two points, using ST_Azimuth. How can I do this for the best-fit line through a sequence of points?

There is an example here on how to do it in ArcGIS, but is there a clean PostGIS solution?

Answer 1

The best fit bearing should be sufficiently approximated as the average azimuth over the sequence:

SELECT AVG(azm) AS bearing
FROM   (
  SELECT degrees(ST_Azimuth(geom, LEAD(geom) OVER(ORDER BY <sequence_id>))) AS azm
  FROM   <your_table>
) q;

Alternatively, you can easily reproduce the approximation as described in the linked answer with PostgreSQL's REGR_SLOPE() function:

SELECT ATAN2D(1, (REGR_SLOPE(ST_Y(geom), ST_X(geom)) + REGR_SLOPE(ST_X(geom), ST_Y(geom)) / 2) AS bearing
FROM   <your_table>
;

(Switched ordinates in ATAN2D to get angle relative to true north)

---

The regression analysis is stronger influenced by outliers, the averaging tends to have steeper angles.

From a strict statistical POV none of these are the best-fit line as per Deming; the regression line average is closer, but completely unaware of the spheroidal character of the 'measures' (thus more precise on a 2D cartesian plane).