1

I have set of points in a PostGIS Table (Type Geometry).

Now, I need to filter points that lie in a circle with a center (C) and radius (R)!

I expect C to be of type Point or Coordinates.. R to be in Meters!

SELECT * FROM my_points
    WHERE ST_Within(point, ...)

However, I don't know how to complete it..

How can I exactly measure C and R?

3

You actually want ST_DWITHIN - take care R is assumed to be in SRID units unless you are using geographies instead of geometries.

select * from my_points where ST_DWITHIN(my_points.point,c,r);
  • what about C.. how should I mention it? – iamcool Aug 5 '13 at 10:13
  • usually ST_GeomFromText() – Ian Turton Aug 5 '13 at 10:17
  • use ST_MakePoint(x, y) for c – Mike T Aug 5 '13 at 11:26
2

You can use ST_Point_Inside_Circle function. The function is self explainained :

boolean ST_Point_Inside_Circle(geometry a_point, float center_x, float center_y, float radius);

The syntax for this functions is point_inside_circle(geometry,circle_center_x,circle_center_y,radius). Returns the true if the geometry is a point and is inside the circle.

Returns false otherwise.

Here's an example:

 select * from TABLE as A where ST_Point_Inside_Circle(a.geom,x,y,R); 
  -- where the x,y is the center of your circle thus C(x,y). R is the Radius. 
  --The SRID must be in something that uses meters.

I haven't seen the actual function yet, but I have a hunch that it creates a buffer zone for each point and checks if it's inside or not, and returns T/F accordingly.

Another way is with a subquery: You create in your SQ your circle, and then check each point of yours if it is inside or not.

with MyCircle as (select ST_Buffer(ST_SetSRID(ST_MakePoint(x, y),SRID, R) as geom) 
-- Circle centered at C(x,y), Radius R. 
-- Use the same srid as your points in your table. 
select * from TABLE as A where st_intersects(MyCircle.geom,a.geom);
  • 3
    Doing an actual buffer is generally always discouraged, since it is actually more complicated and expensive than one would think. It is always much cheaper to compare distances between points. ST_Point_Inside_Circle uses a C function to determine the distance from a point to a circle with a centre and radius, as shown here. – Mike T Aug 5 '13 at 21:49
  • 1
    There is a missing ) in the second example just before "as geom", I couldn't add it because edits must be 6 characters at least! – hammady May 5 '15 at 11:19

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.