Nearest Neighbour function

Question

I'm trying to create a function which calculates the nearest neighbors under a certain length in a table s_1 an stores them in an output table.

This is what I've got so far:

CREATE OR REPLACE FUNCTION NearestN()   
RETURNS TABLE (     
a integer,
b integer,
ST_Distance integer
)
AS
$$

SELECT ST_Distance(a.the_geom , b.the_geom ) from s_1 a, s_1 b 
 WHERE a.id < b.id  
  AND ST_DWithin(a.the_geom,b.the_geom, 50.0)                   
   ORDER BY ST_Distance(a.the_geom, b.the_geom) ASC LIMIT 25;       
$$
LANGUAGE plpgsql;

Needless to say to function is not working at all (I'm very new to sql) and I think my calculation does not only get me the nearest neighbor but all distances under a certain length.

And is it possible to make the length an argument of the function so I can vary just by calling the function?

Answer 1

Try to use Indexed Nearest Neighbour Search like

SELECT ST_Distance(a.the_geom , b.the_geom ) from s_1 a, s_1 b 
 WHERE a.id < b.id  
  AND ST_DWithin(a.the_geom,b.the_geom, 50.0)                   
   ORDER BY a.the_geom <-> b.the_geom LIMIT 25;

Note: I cannot see why you would need the WHERE a.id < b.id, you will have to try which version gives you the best result.

Another resource which helped me to understand the Indexed Nearest Neighbour Search was this blog post.