3

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: enter image description here 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?

4

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;
  • Thanks, exactly what i needed. I changed the order by to p.geom <-> q.geom because the second buffer is not needed anymore. – bennos Nov 20 '14 at 15:17
  • I was about to add that :D. Also I think it would be more efficient to use not ST_DWithin than buffer, in terms of index use. I will write up when I get home – John Powell Nov 20 '14 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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