2

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?

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
iamcool
  • 21
  • 1
  • 1
  • 3

2 Answers2

5

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);
Ian Turton
  • 81,417
  • 6
  • 84
  • 185
3

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);
Mike T
  • 42,095
  • 10
  • 126
  • 187
nickves
  • 11,519
  • 3
  • 42
  • 75
  • 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 05 '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 05 '15 at 11:19