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?


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

| improve this answer | |
  • 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 '19 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 '19 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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