Select points within a buffer / radius of a point

Question

When a user click on a map, I first want to get closest point (placename), then use that point to get number of points (schools) within 10km buffer / radius

... 
$str = pg_query($conn, "SELECT ST_Buffer(a.geom, 1000) as buffer, a.*, s.*, ST_Distance(a.geom, r.ref_geom) AS distance FROM placenames a CROSS JOIN (SELECT ST_MakePoint($coordinate)::geography AS ref_geom) AS r INNER JOIN local s ON ST_Intersects(a.geom, s.geom) WHERE ST_DWithin(a.geom, r.ref_geom, '$radius') ORDER BY ST_Distance(a.geom, r.ref_geom)"
if(pg_num_rows($str) > 0){
    $area = $fetch['placename'];
    $buffer = $fetch['buffer'];
    //How do I use this buffer and get all schools within that buffer
}

3 dots means there is a code on top

Never use ST_Distance or ST_Buffer in a distance search query. Instead use ST_DWithin, which can use an index. — Vince, Mar 27 '23 at 12:20
Does this answer your question? What is difference between ST_DWithin and ST_Distance for proximity search in PostGIS? — Vince, Mar 27 '23 at 12:21

geozelot · Answer 1 · 2023-03-27T12:58:35.973

Get the closest geometry to a given input geometry - referred to as (spatial) (K)NN search - over GEOGRAPHYs casted on-the-fly:

SELECT
  t.*
FROM
  <table> AS t
ORDER BY
  t.geom::GEOGRAPHY <-> <input_geom>::GEOGRAPHY
LIMIT
  1
;

Get all geometries in proximity to an input geometry:

SELECT
  t.*
FROM
  <table> AS t
WHERE
  ST_DWithin(t.geom::GEOGRAPHY, <input_geom>::GEOGRAPHY, <radius_in_meter>)
;

As combo, using an inline sub-query:

SELECT
  t.*
FROM
  <table> AS t
WHERE
  ST_DWithin(
    t.geom::GEOGRAPHY,
    (
      SELECT
        _t.geom::GEOGRAPHY
      FROM
        <table> AS _t
      ORDER BY
        _t.geom::GEOGRAPHY <-> <input_geom>::GEOGRAPHY
      LIMIT
        1
    ),
    <radius_in_meter>
  )
;

I strongly recommend to get familiar with distance units and index usage; related posts with further links and info:

@AsandaLamba did this solve your question/problem? If so, consider accepting the answer. If not, what issues are you facing? — geozelot, May 09 '23 at 05:38

Select points within a buffer / radius of a point

1 Answers1