How can I find the bearing between two points in PostGIS?
Please specify in your answer whether or not the method producing a bearing on the spheroid, or a planar bearing.
Planar bearing can be calculated using
SELECT ST_Azimuth(ST_MakePoint(1,2), ST_MakePoint(3,4))/(2*pi())*360 as degAz, ST_Azimuth(ST_MakePoint(3,4), ST_MakePoint(1,2))/(2*pi())*360 As degAzrev degaz degazrev ------ --------- 45 225
For spherical azimuth (Quoting potgis-users group):
The PostGIS azimuth function seems to use a simple arctan function to determine azimuth. If you convert your coordinates to a projected coordinate system and then run the query, your results will be much closer to the FCC site's results.
Here is a quick conversion to UTM Zone 31:
select degrees(azimuth( 'POINT(634714.442133176 5802006.052402816)', 'POINT(634731.2410598891 5801981.648284801)' ));
which yields an azimuth of 145.457858825445. Points in the center of the UTM zone, or a more suitable projection would give better results.
This is the solution I've chosen when I had to deal with this problems, mainly for legacy reasons (I had a Python function which calculates the azimuth). First, we need to find a function that would tell us the exact distance between two points. Quoting the postgis manual:
ST_distance_sphere(point, point) Returns linear distance in meters between two lat/lon points. Uses a spherical earth and radius of 6370986 meters. Faster than distance_spheroid(), but less accurate. Only implemented for points.
Measure the Longitude and Latitude distance between the points, and use the
arctan function to retrieve the angle.
For anyone coming across this question now - PostGIS supports ST_Azimuth on geography (spheroid) as of 2.0.0.