2

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

3

1 Answer 1

5

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:

1
  • @AsandaLamba did this solve your question/problem? If so, consider accepting the answer. If not, what issues are you facing?
    – geozelot
    May 9, 2023 at 5:38

Not the answer you're looking for? Browse other questions tagged or ask your own question.