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?

Have a look at http://gis.stackexchange.com/questions/136403/postgis-nearest-point-with-st-distance/136416#136416 for some ideas. Also, can you please edit your post so the code block is clearly outlined, it makes it much easier to read. — John Powell, Dec 22 '15 at 21:37

score 2 · Accepted Answer · answered Dec 23 '15 at 08:45

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.

Nearest Neighbour function

1 Answers1