Suppose I have 3 geographic points, A, B and C.

I can calculate the bearing of B from A (in degrees) using this SQL snippet:

degrees(ST_Azimuth(geogA,geogB)) as brg_a_b

...and the same of C from B:

degrees(ST_Azimuth(geogB,geogC)) as brg_b_c

Now, I'm wanting to check if A, B and C are close to being in a straight line, let's assume I want the B -> C bearing to be within say, 2 degrees different to the A -> B bearing to consider them in a straight line.

Azimuth is measured clockwise from north, so North = 0º, East = 90º, South = 180º, West is 270º.

Say the A-B bearing is 1º, how do I calculate if B-C is within, say 2º (either way) - IE between the values of 359º and 3º?

I need to construct a SQL "WHERE" clause which effectively does this: WHERE brg_b_c within 2º of brg_a_b (but copes with the 360-0 crossover)

I'm sure this is really simple but am having a mental block working it out.

Thanks for any pointers (or suggestions of achieving the same thing in a different way ?)

1 Answer 1

WHERE abs(brg_a_b-brg_b_c) <2 OR abs(brg_a_b-brg_b_c) > 358
  • 1
    Great, thanks. So if I take this a step further: WHERE abs(brg_a_b-brg_b_c) < (max_deviation) OR abs(brg_a_b-brg_b_c) > (360 - max_deviation) That should do the trick I think.
    – fatbadger
    Commented Jan 30, 2014 at 13:43

Your Answer

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

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