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

Answer 1

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