3

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?

1

1 Answer 1

3

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

2
  • Thanks for the idea. This is much simpler than the ArcGIS solution linked above. However, wouldn't taking the average azimuth assume that the points are equally spaced? Would we need to weight by the distance between each pair of points?
    – amball
    Oct 8, 2019 at 21:55
  • Mmmno, this should have nothing to do with distance if you follow the point pairs in order of the sequence. I added the regression line approximation of that post you linked to the answer so you can compare. And thx for correcting!
    – geozelot
    Oct 9, 2019 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.