7

I want to get the stars within the black region as shown above. To find this I have the centre of circle (lat, long), radius and the angle of region (45 degree).

I am new to postgres. I worked on code to check if points exist within the circle of a given radius.

How I can find points within this black region?

enter image description here

  • 1
    Thanks AndyM. I think you want ST_Within; the trick will be creating that projected wedge. – Craig Ringer Jan 24 '14 at 9:43
  • 3
    Can you post your code. It might be helpful to see how far you got. – tobias47n9e Jan 24 '14 at 9:43
  • 1
    Related mailing list post: comments.gmane.org/gmane.comp.gis.postgis/24626 . Looks like you can ST_rotate a line around an origin point, then create an arc to close the poly. Seems roundabout; I'd expect it to be possible to start with a circle and intersect it with a poly. – Craig Ringer Jan 24 '14 at 9:47
  • If you're able to provide a dump of a subset of the data to work with, that makes testing much easier. – Craig Ringer Jan 24 '14 at 10:01
9

Use ST_Azimuth to get the angle from the origin point to the point of interest. I've used simple geometry points here since you didn't have any sample data, but you're probably working with geography. The principle is the same:

WITH points(star) AS (VALUES
  (point(0.5, 1)),
  (point(0.2,0.2)),
  (point(0.2, 1)),
  (point(0.8, 0.2))
)
SELECT star 
FROM points 
WHERE degrees(ST_Azimuth( point(0,0)::geometry, star::geometry )) 
      BETWEEN 0 AND 40;
  star   
---------
 (0.5,1)
 (0.2,1)
(2 rows)

You can also limit distance. Here's how to get distance and azimuth from table "points" containing a point-typed column "star" using ST_Distance too:

SELECT
  star, 
  degrees(ST_Azimuth( point(0,0)::geometry, star::geometry )) AS azimuth_deg,
  ST_Distance( point(0,0)::geometry, star::geometry ) AS distance
FROM points;

You can move those into the WHERE clause and filter on them however you like.

If possible work directly in radians, rather than converting to degrees.

If you're working with points on the earth, consider using the geography type instead of geometry. Both functions mentioned here support geography.

1

A circular wedge in a spherical space is just a triangle. You might look at using the geography type for this. However, defining the "radius" in that type might be hard since you're probably wanting to work in angular units, not metric units. I'd suggest that if you're doing astronomy you'll find that http://pgsphere.projects.pgfoundry.org fits your use cases more closely since that is what it was built for.

  • Your initial statement is incorrect except when the circle's radius is an integral multiple of one-quarter of the sphere's circumference. – whuber Jan 24 '14 at 20:22
  • 1
    ah, right, the two edges are great circles, but the "crust" of the pie piece is not. – Paul Ramsey Jan 24 '14 at 22:40

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.