Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers 3

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks very much for all the responses. Very helpful. –  JamesC Jun 17 '12 at 12:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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