Select points within a buffer / radius of a point [duplicate]

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

Answer 1

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:

• PostGIS, closest point to another point
• Efficient way to find nearest feature between huge postgres tables
• Finding nearest neighbor for each point in the same table