How to use PostGIS to buffer a point layer and find if points fall within these buffers, ignoring each buffer's own central point

Question

I have a table containing point geometries and a buffer_distance column. I want to create a buffer around each point using the buffer_distance column and check if any point is within each buffer but making sure to ignore the point at the center of each buffer polygon (the point from which I created the buffer).

I have the following query but it returns True for all values in the point_within column as it is not ignoring the point at the center of the buffered geometry.

SELECT p.id, ST_Buffer(p.geom, p.buffer_distance), ST_Within(p.geom, ST_Buffer(p.geom, p.buffer_distance)) AS point_within
FROM point_table as p

welcome to SO! can you provide a data sample and the create table statement? — Jim Jones, Sep 27 '21 at 14:40
I added the example query I have been working on. Apologies if the formatting is off, this is the first time I use SO ;) — duck_goes_quack, Sep 27 '21 at 15:07

Jim Jones · Accepted Answer · 2021-09-27T21:35:29.427

3

What you're looking for is a spatial join. Join the table with itself and check which records lie inside each individual buffer, and then exclude the duplicates:

SELECT * FROM point_table p
JOIN point_table p2 ON 
  ST_Contains(ST_Buffer(p.geom, p.buffer_distance), p2.geom) AND
  p2.id <> p.id;

Demo: db<>fiddle

edited Sep 27 '21 at 21:35

answered Sep 27 '21 at 15:54

Jim Jones

18,404
3
35
44

I think this is a good step in the right direction! How would you use the spatial join to create a new boolean attribute "point_within" to indicate if a point is within a buffer or not? – duck_goes_quack Sep 27 '21 at 18:19

How to use PostGIS to buffer a point layer and find if points fall within these buffers, ignoring each buffer's own central point

1 Answers1