How to get distance & bearing between a point and the nearest part of a polygon?

Question

I am fairly new to writing SQL and using PostGIS so apologies if this seems a bit simple or obvious. I have a table of polygon features held in a postGIS database and I need to write an SQL query to select all the attributes, the distance and the direction of the polygon features within a certain distance of a predefined point. I have managed to select the features and calculate the distance (using ST_Distance) but cannot work out how to obtain the bearing.

This is the script I am using so far:

SELECT *,
  ST_Distance (Geometry, ST_GeomFromText ('POINT(292596 90785)', 27700))
FROM mytable
WHERE
  ST_DWithin(
    Geometry,
    ST_GeomFromText('POINT(292596 90785)', 27700),
    250) 

How do I work out the bearing of the features as well as the distance? Any help is very much appreciated.

Thanks in advance

James

Answer 1

You can use ST_Azimuth to calculate the bearing, but it takes only point geometries as parameters, so you have to decide which bearing you really want.

You could pick the shortest line as @Nicklas has suggested (it's the one with the distance you already calculate), which makes sense if you need the fastest "direction" to reach the polygon. For anything else, I suggest you calculate it against the polygon centroid (ST_Centroid), which should give better results for arbitrary shapes.

Answer 2

ST_Azimuth is the function you want. It returns the angle in radians clockwise from north. As the sample on the ST_Azimuth() page shows, simply wrapping it in the degrees() function will give you the angle in degrees if that's what you need.

Answer 3

Use

ST_Azimuth(ST_Shortestline(geom1,geom2))

edit

I just realized that ST_Azimuth don't take a linestring as argumetn so you will need to give it two points instead.

If one of the geometries is a point then you can use that directly and use ST_ClosestPoint to get the point on the polygon.