PostGIS Nearest Neighbour outside buffer

Question

I am trying to find the 6 nearest neighbours to a point which are outside a defined buffer.

Basically I am comparing points of the same table and working with Mike Gleason's examples.

WITH subq AS (
SELECT p.id, p.name, unnest(ARRAY(SELECT q.name 
  FROM w_point q 
  where p.id != q.id
  ORDER BY ST_Buffer(p.geom, 0.1) <#> q.geom LIMIT 5)) as name
FROM w_point p)
SELECT * FROM subq;

The problem is, that the resulting points are still inside the buffer: Desired points are outside the yellow buffer, the table reflects the result

How can I add the buffer to my query? Am I thinking the wrong way and is there a better one?

Answer 1

I think you want to exclude the intersection of the buffer in the where clause.

WITH subq AS (
  SELECT p.id, p.name, 
    unnest(ARRAY(SELECT q.name 
      FROM w_point q  
      WHERE p.id != q.id AND NOT ST_Intersects(q.geom, ST_Buffer(p.geom, 0.1))
      ORDER BY ST_Buffer(p.geom, 0.1) <#> q.geom LIMIT 5)
    ) as name
   FROM w_point p
 )
 SELECT * FROM subq;

EDIT: It is more efficient to use ST_DWithin than ST_Buffer, so the above can better be written as,

WITH subq AS (
 SELECT p.id, p.name, 
    unnest(ARRAY(SELECT q.name 
      FROM w_point q  
      WHERE p.id != q.id and not ST_DWithin(q.geom, p.geom, 0.1)
      ORDER BY p.geom <#> q.geom LIMIT 5)
     ) as name
  FROM w_point p
 )
 SELECT * FROM subq;